Edit properties of a Visualforce Email Template in apex so the email sent through Messaging.sendEmail() will have the most recent data in it

Question

I have a visualforce email template that displays the list of Opportunity records that are populated using a visualforce component. I'm using this email template in a schedulable class. The HTML Preview displays the most recent data but the actual email, that is sent to the users, displays the data that was current at the time when I last edited the properties of the template manually (through the UI). I tried to implement the workaround mentioned in this article to edit the properties programmatically in apex class: https://success.salesforce.com/issues_view?id=a1p300000008YXaAAM Code snippet from the execute() method in the schedulable class: List OpptysReportList = new List([Select Id, Name, Account.Name, StageName, OwnerId, Owner.Email, Owner.Name, CloseDate, Last_Certified_Date__c from Opportunity where (NOT(StageName LIKE 'Closed%'))); EmailTemplate emailTemplateExec = [select Id, Name, Subject, Markup, HtmlValue, Body, ApiVersion,Description,DeveloperName,Encoding, FolderId,IsActive,OwnerId from EmailTemplate where Name = 'OpptyCertExecSummary']; update emailTemplateExec; if(OpptysReportList.size()>0) { sendExecEmail(startDate,endDate); //code related to startDate and endDate are not shown here as they are //irrelevant to the issue } Future Method in the schedulable class: @future private static void sendExecEmail(String startDate, String endDate) { EmailTemplate emailTempExec = [select Id, Name, Subject, Markup, HtmlValue, Body from EmailTemplate where Name = 'OpptyCertExecSummary']; String subject, body; subject = emailTempExec.Subject + ' ' +startDate + ' - ' + endDate; body = 'Opportunity Certification Executive Summary '+startDate + ' - ' + endDate+'
' + '
' + emailTempExec.HtmlValue; List reminderMails = new List(); Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); mail.setSubject(subject); mail.setHtmlBody(body); mail.setSaveAsActivity(false); String[] toEmails = new String[]{}; toEmails.add('myemail@test.com'); if(toEmails.size()>0) mail.setToAddresses(toEmails); reminderMails.add(mail); if(reminderMails.size()>0 && !Test.isRunningTest()) Messaging.sendEmail(reminderMails,false); } I'm sending the email using a future method to make sure that the email template is updated (committed to the database) before it is used by the Messaging.sendEmail() method. This workaround doesn't help. The "Last Modified Date" of the template is getting updated when the code is executed but the actual email still shows the old data unless I edit the properties manually and reschedule the class. I'm not sure where I'm doing wrong in updating the email template using a DML statement. Adding the visualforce template, the visualforce component used in the template, and the controller class as well: Template:

This is a summary of the Salesforce report: Opportunity Pipeline Annual - Team

Component: Sales Leader Sales Person Record Type Certified for this period? Last Certified Date # of Opporunities Certified SUM Total Contract Value Controller: public class B2BSalesOppExecSummaryCC { public String color{get; set;} public class wrapper2 { public String oName{get; set;} public String oRecTypeName{get; set;} public String hasCertified{get; set;} public String lastCertifiedOn{get; set;} public String totalcertified{get; set;} public Integer optyTCVSum{get; set;} public String color{get; set;} public wrapper2(String owner, String certified, String lastCertDate, String numOfCert, Integer tcv, String clr, String recType) { this.oName = owner; this.oRecTypeName = recType; this.hasCertified = certified; this.lastCertifiedOn = lastCertDate; this.totalcertified = numOfCert; this.optyTCVSum = tcv; this.color = clr; } } public class wrapper1 { public String mName{get; set;} public List wrap2{get; set;} public wrapper1(String mgr,List w) { this.mName = mgr; this.wrap2 = w; } } public List wrap1{get;set;} public B2BSalesOppExecSummaryCC() { Id recTypePlatform = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('Platform').getRecordTypeId(); Id recTypeCollege = Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('College').getRecordTypeId(); Integer orgFiscalMonth = [SELECT FiscalYearStartMonth FROM Organization].FiscalYearStartMonth; Date orgFiscalYear = Date.newinstance(system.today().year(), orgFiscalMonth, 1); Integer thisMonth = System.today().month(); Date fiscQuarterStart,fiscQuarterEnd; if(thisMonth == 1 || thisMonth == 2 || thisMonth == 3) { fiscQuarterStart = orgFiscalYear; } else if(thisMonth == 4 || thisMonth == 5 || thisMonth == 6) { fiscQuarterStart = orgFiscalYear.addMonths(3); } else if(thisMonth == 7 || thisMonth == 8 || thisMonth == 9) { fiscQuarterStart = orgFiscalYear.addMonths(6); } else if(thisMonth == 10 || thisMonth == 11 || thisMonth == 12) { fiscQuarterStart = orgFiscalYear.addMonths(9); } fiscQuarterEnd = fiscQuarterStart.addMonths(12).addDays(-1); List OpptysReportList = new List([Select Id, OwnerId, Owner.Name, Last_Certified_Date__c, Total_Contract_Value_TCV__c, RecordType.Name from Opportunity where (NOT(StageName LIKE 'Closed%')) and (RecordTypeId = :recTypePlatform OR RecordTypeId = :recTypeCollege) and CloseDate >= :fiscQuarterStart and CloseDate <= :fiscQuarterEnd ORDER BY Last_Certified_Date__c DESC NULLS LAST]); String startDate, endDate; date dat, todayDat, endDat; todayDat = System.today(); Integer todayDay = todayDat.day(); endDat = System.today().addMonths(1).toStartOfMonth().addDays(-1); Integer thisMonthEnd = endDat.day(); if(todayDay <= 15) { startDate = todayDat.month()+'/1/'+todayDat.year(); endDate = todayDat.month()+'/15/'+todayDat.year(); dat = System.today().addDays(-(todayDay-1)); } else { endDate = endDat.month()+'/'+endDat.day()+'/'+endDat.year(); startDate = todayDat.month()+'/16/'+todayDat.year(); dat = System.today().addDays(-(todayDay - 16)); } Date dt = date.newInstance(dat.year(), dat.month(),dat.day()); Map wrapperMap = new Map(); Map ownerNameIdMap = new Map(); for(Opportunity o:OpptysReportList) { String[] s; if(!wrapperMap.containsKey(o.Owner.Name)) { s = new String[6]; s[5] = o.RecordType.Name; if(o.Last_Certified_Date__c >= dt) { s[0] = 'Yes'; s[2] = '1'; s[4] = 'Green'; } else { s[0] = 'No'; s[2] = '0'; s[4] = 'Red'; } if(o.Last_Certified_Date__c!=NULL) s[1] = String.valueOf(o.Last_Certified_Date__c); else s[1] = ''; if(o.Total_Contract_Value_TCV__c!=NULL) s[3] = String.valueOf(o.Total_Contract_Value_TCV__c); else s[3] = '0'; } else { s = wrapperMap.get(o.Owner.Name); if(o.Last_Certified_Date__c!=NULL && s[1]!='') { if(o.Last_Certified_Date__c > date.valueOf(s[1])) s[1] = String.valueOf(o.Last_Certified_Date__c.month())+'/'+String.valueOf(o.Last_Certified_Date__c.day())+'/'+String.valueOf(o.Last_Certified_Date__c.year()); } if(o.Last_Certified_Date__c >= dt) s[2] = String.valueOf(Integer.valueOf(s[2])+1); if(o.Total_Contract_Value_TCV__c!=NULL) { Decimal temp = Decimal.valueOf(s[3])+o.Total_Contract_Value_TCV__c; s[3] = String.valueOf(temp.intValue()); } } wrapperMap.put(o.Owner.Name,s); ownerNameIdMap.put(o.Owner.Name,o.OwnerId); } Map userManagerMap = new Map([Select Id,Name,Manager.Name From User Where Id IN :ownerNameIdMap.values() ORDER BY Manager.Name ASC NULLS LAST]); Map> managerNameWrapperMap = new Map>(); for(String o:wrapperMap.keySet()) { String[] s = wrapperMap.get(o); String m = userManagerMap.get(ownerNameIdMap.get(o)).Manager.Name; wrapper2 w = new wrapper2(o,s[0],s[1],s[2],Integer.valueof(s[3].trim()),s[4],s[5]); List w2; if(managerNameWrapperMap.containsKey(m)) { w2 = managerNameWrapperMap.get(m); } else { w2 = new List(); } w2.add(w); managerNameWrapperMap.put(m,w2); } wrap1 = new List(); for(String m:managerNameWrapperMap.keySet()) { wrapper1 w1 = new wrapper1(m,managerNameWrapperMap.get(m)); wrap1.add(w1); } } }

cropredy · Answer

OK, I apologize for the comment thread but this appears to be the essence of your issue You have (and I have annotated) // Fetch EmnailTemplate as it was last defined in the Point-and-click editor EmailTemplate emailTempExec = [select Id, Name, Subject, Markup, HtmlValue, Body from EmailTemplate where Name = 'OpptyCertExecSummary']; String subject, body; // Modify the subject subject = emailTempExec.Subject + ' ' +startDate + ' - ' + endDate; // Modify the Body body = 'Opportunity Certification Executive Summary '+startDate + ' - ' + endDate+'
' + '
' + emailTempExec.HtmlValue; List reminderMails = new List(); Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); // Use the modified subject and body in the outbound email message mail.setSubject(subject); mail.setHtmlBody(body); ... reminderMails.add(mail); if(reminderMails.size()>0 && !Test.isRunningTest()) Messaging.sendEmail(reminderMails,false); // send the email Instead, let SFDC do its work populating the email template at run time. Your email template is (mostly) a custom component with controller. But this will never execute because you are never asking Apex outbound email to assemble the email using the template - so the template is never rendered with its VF controller constructor or methods ever called. A more common pattern would be: // Fetch EmailTemplate as it was last defined in the Point-and-click editor EmailTemplate emailTempExec = [select Id from EmailTemplate where Name = 'OpptyCertExecSummary']; List reminderMails = new List(); Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); // tell SFDC to use your template mail.setTemplateId(emailTemplateExec.Id); ... reminderMails.add(mail); if(reminderMails.size()>0 && !Test.isRunningTest()) Messaging.sendEmail(reminderMails,false); // send the email If you need a dynamic subject, you can use mail.setSubject(...); The body portion you are synthesizing: Opportunity Certification Executive Summary '+startDate + ' - ' + endDate can be part of the template itself along with merge fields that resolve to controller properties.

Edit properties of a Visualforce Email Template in apex so the email sent through Messaging.sendEmail() will have the most recent data in it

One Answer

Add your own answers!

Ask a Question