6

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; 

 }
5
  • The "too many SOQL queries" error is an aggregate condition. It may have been thrown at this code, but likely indicates that you have a SOQL in a loop elsewhere, or you simply have too much logic in your transaction. – sfdcfox Aug 21 '17 at 13:42
  • 1
    Remember that the 101 query limit is cumulative throughout an entire transaction. You haven't provided enough context for me to say for sure that this code doesn't appear in a loop, but I'll take your word for it. In that case, this code is simply the code that caused you to attempt to make the 101st query. To figure out where your problem is, you'll need to capture a debug log and find out what other code is being executed. – Derek F Aug 21 '17 at 13:42
  • SOQL limit inside for loop starts from 3. – sfdc Aug 21 '17 at 13:55
  • The edits to your question help clear things up a little bit. If you're calling sendEmail() via a command button on your visualforce page, then this method cannot be the source of your query limit error. Providing the entire text of the error message verbatim would help. Part of the error message should be a stack trace, which should help you narrow down the issue to a few classes or triggers. Your sendEmail() method is likely part of that stack trace, but it's probably at the very bottom of it. Look more towards the top of the stack trace. – Derek F Aug 21 '17 at 14:02
  • @DerekF updated visualforce page code – sfdc Aug 21 '17 at 14:11
9

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.

9

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));
        }
    }
}
4
  • 1
    Note that you can only chain 5 times, so you'd still be capped at 1000. – Adrian Larson Aug 21 '17 at 15:24
  • @AdrianLarson isn't that only for Dev and Trial edition orgs? – Derek F Aug 21 '17 at 15:32
  • I thought I hit it in a sandbox, but I'm not certain, could have been DE. – Adrian Larson Aug 21 '17 at 15:32
  • 2
    Just ran some example code in a dev sandbox, via anonymous apex, that works on a single record per call to queueable, and passed it 7 records. All 7 System.enqueueJob() calls ran for me without error. – Derek F Aug 22 '17 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.