Syntax Highlighter JS

Wednesday, April 11, 2012

Automatically sending email reminders about pending approvals

06/14/2012 EDIT : Please see the new post for updated SQL to avoid this error: "System.EmailException: SendEmail failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Missing target address (target, to, cc, bcc):"  

The approval process in Salesforce is very handy because you can send e-mail notifications (by template) and allow approvals via email.   However, some users get busy and delete the email without processing it and can't remember to look at a queue to work these items.

So the request often pops up for users to be reminded via email about pending approval requests.  This post is going to show you how to do exactly that.  We are going to create a class to find approvals over a day old and send a reminder email (possibly off of a template) to the user in question.   Then we will create a wrapper class and schedule it to run once every day.

Before we get started, please make sure that you have an active approval process in place for both Opportunities and Accounts.  Failure to have these in place may result in errors and/or lower code coverage.
One of the things we want this class to do is to use email templates to send the reminder.  This allows system administrators to maintain the template without changing the programming.  Since approvals can be submitted on many object types and we only get the ID from the table, we are going to need to discover what type we are dealing with.

You can use a call to DescribeSObjectResult to pull in the type but that is alot of addtional code but the quick and easy way is to just query for the .type.

Here is an example of that and nesting in our default query:

SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, 
(SELECT Id,, Actor.Name FROM WorkItems)  
FROM ProcessInstance  
WHERE Status = 'Pending' AND IsDeleted = False AND SystemModStamp < Today 

Now that we have the object type, we will need to pull in the ID of the email template as needed.   To do this, constants are set at the top of the class (see full class below) and then referenced in the SendReminderEmail method.   You will need to alter this method (around line 66 where the comment is)  to use any new constants that you create.  

So now you have created the templates in the system, created constants for those templates, and referenced the templates in the code just like the OpportunityEmailTemplateName in the example class.  Now you load the class but are getting errors.

Salesforce seems to not allow you to use email templates to send emails to users about other objects but works just fine for contacts.

To get this working, be sure to remove the recipientType from the <messaging:emailTemplate> tag.  If you don't, you will get the error: "INVALID_ID_FIELD, WhatId is not available for sending emails to UserIds"

This step is vital to getting this all to work.   Anyway, here is the full class with test code:

global class ApprovalReminderEmails implements Database.Batchable<sObject>,Database.Stateful { /********************************************************************* Add/Edit these constants to contain your email template names PLEASE NOTE THAT THESE MUST BE VISUALFORCE TEMPLATES TO WORK! Also, the VF template must NOT have recipientType set in the <messaging:emailTemplate> tag or you will get the error: INVALID_ID_FIELD, WhatId is not available for sending emails to UserIds ***********************************************************************/ static final string OpportunityEmailTemplateName = 'Approval_Opportunity_Reminder'; // this method is used to get all reocrds that will be processed by execute method global Database.QueryLocator start(Database.BatchableContext bc){ // try to use the .type to determine object type in SOQL // instead of DescribeSObjectResult apex code String query = 'SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, (SELECT Id,, Actor.Name FROM WorkItems) FROM ProcessInstance WHERE Status = \'Pending\' AND IsDeleted = False AND SystemModStamp < Today'; if (test.IsRunningTest() == true) { query = 'SELECT Id, TargetObject.Name, TargetObjectID, TargetObject.Type, (SELECT Id,, Actor.Name FROM WorkItems) FROM ProcessInstance WHERE Status = \'Pending\' AND IsDeleted = False'; } // query w/o date when test return Database.getQueryLocator(query); } // querylocator start // find and process all pending approval requests that aren't deleted // and are older than today global void execute(Database.BatchableContext BC, List<sObject> scope) { // loop thru results for (SObject s : scope) { ProcessInstance PI = (ProcessInstance)s; for(ProcessInstanceWorkitem WI : PI.WorkItems) { ApprovalReminderEmails.SendReminderEmail(PI.TargetObject.ID, PI.TargetObject.Type, WI.Actor.Id, WI.Actor.Email, WI.Actor.Name, PI.TargetObject.Name); } // loop thru WorkItems } // loop thru scope which contains the process instance results } // end SendReminders global void finish(Database.BatchableContext info) { } // finish private static void SendReminderEmail(Id TargetID, String TargetObjectType, Id ActorID, string ActorEmailAddress, string ActorName, string TargetDesc) { id EmailTemplateID; // target object type, name and ID will be null in the test methods but work in non test system.debug('TargetObjectType = ' + TargetObjectType); if (TargetObjectType == 'Opportunity') { EmailTemplateID = FindEmailTemplateIDByDeveloperName(OpportunityEmailTemplateName); } /** << Insert code here for other objects & email templates >> **/ // Make sure this transaction won't fail due to lack of daily capacity Messaging.reserveSingleEmailCapacity(1); Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage(); String[] toAddresses; // email will be null during testing ... if (test.IsRunningTest() == true) { toAddresses = new String[] {''}; } else { toAddresses = new String[] {ActorEmailAddress}; } // prevent Email Exception due to null address during testing if (EmailTemplateID != null) { // use our template email.setTemplateId(EmailTemplateID); email.saveAsActivity = false; // populate the template w/ object data email.setTargetObjectId(ActorID); email.setWhatId(TargetID); } else { // use a generic HTML email when no template is setup / found // get the current salesforce instance to build the link with string domain = URL.getSalesforceBaseUrl().toExternalForm(); string TargetLink = domain + '/' + string.valueof(TargetID); email.setToAddresses(toAddresses); // set the subject on the email email.setSubject('Reminder: Item pending approval'); // set the body of the email email.setHTMLBody('Dear ' + ActorName + ', <br/><br/>' + 'The following ' + TargetObjectType + ' is still pending your approval:<br/>' + TargetDesc + '<br/>' + '<br/> Click this link to view the full record:<br/> ' + '<a href=\'' + TargetLink + '\'>' + TargetLink + '</a>' + '<br/>Please DO NOT reply to this email.' ); } // check for template id // send our email by creating an array of emails and calling the send email method. Messaging.SingleEmailMessage[] EmailsToSend = new Messaging.SingleEmailMessage[] { email }; Messaging.sendEmail(EmailsToSend); } // end send reminder email // this method could be moved to a utility class and made public . . . . public static ID FindEmailTemplateIDByDeveloperName(string DeveloperName) { id EmailTemplateID; List<EmailTemplate> EmailTemplateResults = new List<EmailTemplate>(); if (DeveloperName != null) { EmailTemplateResults = [SELECT Id, Name, DeveloperName, IsActive FROM EmailTemplate WHERE DeveloperName = :DeveloperName]; if (EmailTemplateResults.size() > 0) { if (EmailTemplateResults[0].IsActive == true) { EmailTemplateID = EmailTemplateResults[0].id; } // check for active email template } // check for null search results } // check for blank name return EmailTemplateID; } // FindEmailTemplateIDByDeveloperName static testmethod void TestSendReminderEmails() { /** Create test data - edit as needed for custom rules. If you don't have approval processes in place for your test data, you will get lower code coverage in apex testing I have caught system.DMLException of NO_APPLICABLE_PROCESS to prevent total failure of the test unit. **/ // Create 5 test accounts so that only one (5 Accs + 5 Oppty) // executebatch will be invoked during testing. List <Account> AccountsToInsert = new List<Account>(); for(integer i = 0; i<5; i++) { Account a = new Account(Name='Test Account' + i); AccountsToInsert.add(a); } // loop to create 5 accounts insert AccountsToInsert; // no approval process is defined for account in this code, // so we use it to test the generic / HTML template . . . try { for(Account a: AccountsToInsert) { // Create an approval for the account and submit Approval.ProcessSubmitRequest AccountRequest = new Approval.ProcessSubmitRequest(); AccountRequest.setObjectId(a.ID); Approval.ProcessResult AccountRequestResult = Approval.Process(AccountRequest); System.assert(AccountRequestResult.isSuccess()); } // loop thru accounts submitting them for approval } catch (DMLException e) { system.debug('>>>> NO APPROVAL PROCESS FOR ACCOUNTS!'); } // Create opportunities for the accounts date myDate =; List <Opportunity> OpportunitiesToInsert = new List<Opportunity>(); for(Account a: AccountsToInsert) { Opportunity MyOppty = new opportunity(Name='Test Opportunity for ' +,CloseDate=myDate,StageName='Lead'); MyOppty.AccountId = a.Id; OpportunitiesToInsert.add(MyOppty); } // loop thru accounts insert OpportunitiesToInsert; try { for(Opportunity o: OpportunitiesToInsert) { // create an approval for the opportunity Approval.ProcessSubmitRequest OpptyRequest = new Approval.ProcessSubmitRequest(); OpptyRequest.setObjectId(o.ID); Approval.ProcessResult OpptyRequestResult = Approval.Process(OpptyRequest); System.assert(OpptyRequestResult.isSuccess()); } // loop thru opportunities } catch (DMLException e) { system.debug('>>>> NO APPROVAL PROCESS FOR OPPORTUNTIES!'); } /* End create test data */ test.startTest(); ApprovalReminderEmails ARE = new ApprovalReminderEmails(); // the batch size can be no larger than 10 due to current apex email limits integer batchSize = 10; database.executebatch(ARE, batchSize); //increase code coverage by directly calling the method //since it won't get called in testing due to null values returned during testing only //I am using a standard salesforce sample here but need to change this if you removed the sample ApprovalReminderEmails.FindEmailTemplateIDByDeveloperName('ContactFollowUpSAMPLE'); test.stopTest(); } // end test method } // end class
And here is a wrapper class to use for scheduling: global class ScheduleApprovalReminderEmails implements Schedulable { // Run the job every day at 9 am public static String CRON_EXP = '0 0 9 * * ?'; global void execute(SchedulableContext ctx) { ApprovalReminderEmails ARE = new ApprovalReminderEmails(); // the batch size can be no larger than 10 due to current apex email limits integer batchSize = 10; database.executebatch(ARE, batchSize); } // execute static testmethod void TestcheduleApprovalReminderEmails() { Test.startTest(); // Schedule the test job String jobId = System.schedule('TestScheduleApprovalReminderEmails', ScheduleApprovalReminderEmails.CRON_EXP, new ScheduleApprovalReminderEmails()); // Get the information from the CronTrigger API object CronTrigger ct = [SELECT id, CronExpression, TimesTriggered, NextFireTime FROM CronTrigger WHERE id = :jobId]; // Verify the expressions are the same System.assertEquals(ScheduleApprovalReminderEmails.CRON_EXP, ct.CronExpression); // Verify the job has not run System.assertEquals(0, ct.TimesTriggered); Test.stopTest(); } // CRON test method } // end ScheduleApprovalReminderEmails class


  1. Hi coleman,

    I have tried the same way. I am getting following issue

    "System.EmailException: SendEmail failed. First exception on row 0; first error: INVALID_ID_FIELD, Visualforce templates are not currently supported for mass email.: []"

    I did not use mass email messaging at all in my code.
    I just used single email message method and called it many times to send to bunch of users.(ie., it template is the visualforce id)
    This is the same way what you have used. But I am getting issue.
    Please help me out.

  2. Babu,

    I see you have posted this on the developer boards and I will reply to you on that thread. The direct link to the thread is here:

  3. Hi,

    Can you please help me with this:

    I am trying to create mass email notification for got WhatId error for User object.

    Here is my code:

    trigger Case_SendEmail on Case (after insert) {

    Messaging.MassEmailMessage message = new Messaging.MassEmailMessage();
    message.setTemplateId([SELECT Id FROM EmailTemplate WHERE Name = ''Case Created Alert'].Id);

    if (Trigger.isAfter && Trigger.isInsert) {
    list caseIds = new list();
    map caseAddOwners = new map();

    Id[] targetObjectIds = new Id[] {};
    Id[] whatIds = new Id[] {};

    //get Case.Id where OwnerId changed and Case is not Closed.
    for (Case {

    for (Support_Case_Notifications__c scn:[SELECT Id, User__c FROM Support_Case_Notifications__c WHERE Account__c = :c.AccountId]) {

    Messaging.sendEmail(new Messaging.Email[] {message});

    Here is the error message:

    caused by: System.EmailException: SendEmail failed. First exception on row 0; first error: INVALID_ID_FIELD, WhatId is not available for sending emails to UserIds.: []


  4. Anonymous,

    As mentioned in the post above, Salesforce doesn't seem to allow you to use the the WhatID when sending emails to users.

    Have you removed the recipientType from the tag from your email template as suggested in the post above?

    That was the work around for me.


  5. I am getting below exception in my test method
    System.DmlException: Process failed. First exception on row 0; first error: MANAGER_NOT_DEFINED, Manager undefined.: []

    Please suggest


  6. Anonymous,

    Can you tell me on what line number and method you received the DML exception?

    The MANAGER_NOT_DEFINED error basically means that the next approver isn't defined / populated. However, I have never encountered this error when dealing with code posted above.


  7. Great post.

    Just one thing regarding the batches though - it's not necessary to limit the batch to 10 due "to current apex email limits". There is a limit of 10 calls to sendEmail(), that's true, but then you're currently sending each email individually when it's possible to send many SingleEmailMesages together in the list passed to sendEmail(). These messages do not need to be the same, or even based on the same template / whatId.

    If your SendReminderEmail() method was rather called PrepareReminderEmail() and returned the SingleEmailMessage object instead of sending it then you could change your batch execute() method to do the following:

    global void execute(Database.BatchableContext BC, List scope) {

    // loop thru results
    for (SObject s : scope) {

    ProcessInstance PI = (ProcessInstance)s;
    List emailsToSend = new List();

    for(ProcessInstanceWorkitem WI : PI.WorkItems) {
    emailsToSend.add(ApprovalReminderEmails.PrepareReminderEmail(PI.TargetObject.ID, PI.TargetObject.Type, WI.Actor.Id, WI.Actor.Email, WI.Actor.Name, PI.TargetObject.Name));

    } // loop thru WorkItems

    } // loop thru scope which contains the process instance results

    if(emailsToSend.size() > 0){
    Messaging.sendEmail(emailsToSend); // all the sending done in one go. Only one call to sendEmail() regardless of batch size.

    You can then set the batch size much higher (or even do away with batching it at all?) as the only thing you need to be mindful of is the heap size. The overall limit for sending emails is currently 1000 per 24 hours which is the other thing to bear in mind.

    Architect - ForceClarity Ltd.