System.LimitException: Too many SOQL queries: 101

Question

I am calling sendemail() from visualforce.There are no SOQL Queries inside for loops but am getting error in controller.

If I comment below two lines then am not getting error

   Messaging.SingleEmailMessage email = Messaging.renderStoredEmailTemplate(et.id, c.Id, ServiceId);
  t.Description = 'Email Body:'+'\n'+email.getPlainTextBody();

Too many SOQL queries: 101

There are 120 Contacts in the ContactList.Debug log SOQL Limit @@@@ inside for loop starts from 3.

 <apex:page standardController="Service__c"
extensions="EmailLetterExtension" showHeader="true" Sidebar="true"
action="{!sendEmail}">   Thank you! Email is sent. 
</apex:page>

---

public void sendEmail(){
 List<contact> lstcon=[Select Id,Name,Email,RecordTypeId,RecordType.Name From Contact where RecordType.Name = 'Service'];
 List<Id> lstids= new List<Id>();
 for(Contact c:lstcon)
 {
  lstids.add(c.id);
 }
 EmailTemplate et=[Select id,subject,body from EmailTemplate where name = 'Service Template' limit 1];

 Messaging.MassEmailMessage mail = new Messaging.MassEmailMessage();
 mail.setTargetObjectIds(lstIds);
 mail.setTemplateId(et.id);
 mail.setSaveAsActivity(false);
 Messaging.sendEmail(new Messaging.MassEmailMessage[] { mail });
 list<Task> tList = new list<Task>();
 for(Contact c:lstcon){
     Task t = new Task();
     t.whatId = ChauffeurId;
     t.WhoId = c.id;
     t.Subject = 'Email : Letter sent to '+ c.Name + ' on '+ datetime.now().format('MM/dd/YYYY');
     Messaging.SingleEmailMessage email = Messaging.renderStoredEmailTemplate(et.id, c.Id, ServiceId);
     t.Description = 'Email Body:'+'\n'+email.getPlainTextBody();
     t.status = 'Completed';
     t.Priority = 'Normal';
     t.ActivityDate = system.today();
     tList.add(t);
     system.debug('SOQL Limit @@@@' +Limits.getQueries());
 }
 insert tList; 

 }

Answer 1

Hi You are getting the error because you are using Messaging.renderStoredEmailTemplate() in for loop as this is counted towards the SOQL governor limit as one query. To avoid the exception you have to remove out Messaging.renderStoredEmailTemplate() of for loop. For more reference please have a look on salesforce docs for the Messaging class.

Executing the renderStoredEmailTemplate method counts toward the SOQL governor limit as one query.

Answer 2

Rishi Golyan correctly identified the issue, however, Messaging.renderStoredEmailTemplate() works on single ids rather than a list of ids.

So, simply moving that method call to be outside of your loop isn't going to cut it if you have more than 100 Contacts to mail to. One way to handle that situation would be to put it into a class that implements Queueable.

With queueable apex, you can call Messaging.renderStoredEmailTemplate() up to 200 times (because queueable is asynchronous, and the async query limit is 200).

If you need to handle more than 200 Contacts, you can simply chain your queueable class again (each call to system.enqueueJob() gets you a fresh set of limits).

A basic implementation might look like this:

public class queueTest implements Queueable{
    List<Contact> contactsList;
    Id templateId, whatId;

    public queueTest(List<Contact> inContacts, Id templateId, Id whatId){
        contactsList = inContacts;
        this.templateId = templateId;
        this.whatId = whatId;
    }

    public void execute(QueueableContext qc){
        Contact ct;
        for(Integer i = 0; i < 200; i++){
            // We're not looping over the list itself, so it's ok to modify contactsList
            // remove(0) will always remove the first item in the list (causing
            //   the data previously stored in index 1 to now be stored in index 0).
            // We'll eventually run through the entire list.
            // Removing list entries as we go ensures that we will 1) eventually stop
            //   needing to chain jobs, and 2) not process the same record more than once

            // We do need to do a quick, simple check to see if contactsList is empty
            //   before attempting to remove the first item (because there may be
            //   less than 200 records to process
            if(contactsList == null || contactsList.isEmpty()){
                break;
            }
            ct = contactsList.remove(0);
            Messaging.renderStoredEmailTemplate(templateId, ct.Id, whatId);
        }

        if(contactsList ! = null || !contactsList.isEmpty()){
            // There are still contacts to email, so chain another instance of
            //   this class (passing the remaining Contacts to process).
            // This will count as a separate transaction, so we'll get a fresh set
            //   of limits
            system.enqueueJob(new queueTest(contactsList, templateId, whatId));
        }
    }
}