3

I am trying to send an email to every account in my org from a controller, and each email has an attached VisualForce page. The problem is that I am hitting governor limits on SOQL queries. The problem line of code is highlighted below:

public static Boolean sendEmail(){

    // Get template for emails
    Id templateId = [SELECT Id
                        FROM EmailTemplate
                        WHERE DeveloperName = 'Template'].Id;

    // Get the related accounts
    List<Account> accounts = [SELECT Id, Related_Lead__c FROM Account];

    List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage> ();

    // Create the emails
    for (Account acc: accounts) {       
        // Create a new Email
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

        // Set the Email template
        mail.setTemplateId(templateId);

        // Set the target account
        mail.setWhatId(acc.Id);

        // Set the recipient
        mail.setTargetObjectId(acc.Related_Lead__c);

        // Create PDF to attach to email
        PageReference pdf = Page.AccountPDF;
        pdf.getParameters().put('id', acc.Id);
        Blob b = pdf.getContent(); // ********* LIMITS ARE HIT HERE **********

        // Attach the PDF
        Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment();
        efa.setFileName('account.pdf');
        efa.setBody(b);
        mail.setFileAttachments(new Messaging.EmailFileAttachment[] {efa});

        mails.add(mail);
    }

    // Send the emails
    Messaging.sendEmail(mails);
    return true;
}

Instead of supplying the page with the account id as a parameter which results in a new query within the page controller, I would like to supply the page with the account object (variable 'acc') that I've already retrieved from the database. Is there a way to do that?

1

It would have been helpful had you included more of your code (at least an outline of your controller methods) to give it more context. That said having been said,

Blob b = pdf.getContent(); // ********* LIMITS ARE HIT HERE **********

Under today's current limits, this would be counted as a callout from a controller. At the time you asked the question, it would not have been. If this was being done inside of a batch class at that time, I believe it may have been counted as a callout. Again, it definitely would be today and would cause you problems with limits before any kind of soql limits were hit.

Since we don't know how you're querying accounts or the templateId, here's what you'd at a minimum want to do before calling your method.

Getting rid of these two queries in your method by calling them in your main controller once:

Id templateId = [SELECT Id
                    FROM EmailTemplate
                    WHERE DeveloperName = 'Template'].Id;

List<Account> accounts = [SELECT Id, Related_Lead__c FROM Account];

You can make these static variables and create static methods to both call and generate them using a singleton pattern.

Declare them in your controller:

public static Id templateId {private set;}
public static list<account>accounts {private set;}

Create methods:

public static Id gettemplateId () {
    If(isEmpty(templateId)){
       templateId = [SELECT Id
                        FROM EmailTemplate
                        WHERE DeveloperName = 'Template'].Id; 
    }
     return templateId;
} 


 public static list<account> getaccounts() {
     if(accounts.isEmpty()) {
         accounts = [SELECT Id, Related_Lead__c FROM Account];
     }
     return accounts;
 }

In both of the above examples, the query is only run if the variable is empty, otherwise the contents of the existing static variable are returned. This prevents the query from being executed more than once.

To answer your original question, you could reduce the SOQL queries by changing your method definition to look like this:

public static Boolean sendEmail(Account accnt, Id templateId ){

Then add code inside your method to set the incoming account and templateId to the ones you'll be using in your method. That will at least save you from querying for the TemplateId each time.

You could also change it to use the listaccounts so it doesn't happen inside of a for loop as below:

public static Boolean sendEmail(List<account> acctounts, Id templateId) {

which would more closely match your existing code.

Not that unless your previous query in your controller on Account returns Related_Lead__c, you'll need to query Accounts again to get the Related_Lead__c.

This wouldn't solve your issue if your class is failing on the getContentasPDF() line unless your error codes are giving you a red herring. For that reason, I'd recommend you use your controller to kick off a batch class to run this code asynchronously with the scope for each batch class set to run within the getContentasPDF() callout limits. Alternatively, you could also call a queuable class to do this as well.

Be aware that the following limits will apply:

Maximum size of callout request or response (HTTP request or Web services call): 6 MB for synchronous Apex or 12 MB for asynchronous Apex

The maximum number of asynchronous Apex method executions (batch Apex, future methods, Queueable Apex, and scheduled Apex) per a 24-hour period: The maximum number of asynchronous Apex method executions (batch Apex, future methods, Queueable Apex, and scheduled Apex) per a 24-hour period.

Total number of SOQL queries issued (This limit doesn’t apply to custom metadata types. In a single Apex transaction, custom metadata records can have unlimited SOQL queries.): Syncronous Limit - 100, Asynchronous Limit - 200.

Total number of callouts (HTTP requests or Web services calls) in a transaction: 100.

Using the API or Apex, you can send single emails to a maximum of 5,000 external email addresses per day based on Greenwich Mean Time (GMT).

It's possible that your controller class could have been failing because of the 6MB limit on synchrous callouts for a PDF being returned. Keep the last limit in mind should you have more than 5000 accounts with leads. Obviously, you'll want to keep your scope set to below 100.

| improve this answer | |
  • 1
    OP suggests he is launching this from a controller; perhaps the controller should start a batch job with a scope size such that the callout limit is not exceeded – cropredy Jan 21 '17 at 20:33
  • @cropredy Reread the question and agree with you. Is unfortunate the OP didn't include more of his code to give it more context (at least an outline of his controller). Will edit my answer. – crmprogdev Jan 22 '17 at 12:58

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.