0

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<Opportunity> OpptysReportList = new List<Opportunity>([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+'<br/>' + '<br/>' +
           emailTempExec.HtmlValue;
    List<Messaging.SingleEmailMessage> reminderMails = new List<Messaging.SingleEmailMessage>();  
    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:

<messaging:emailTemplate subject="Oppty Cert  - Exec Summary" recipientType="User" relatedToType="Opportunity">
<messaging:htmlEmailBody >
<html>
<body>
<p>This is a summary of the Salesforce report: Opportunity Pipeline Annual - Team</p>
<c:B2BSalesOppExecSummary />
</body>
</html>
</messaging:htmlEmailBody>
</messaging:emailTemplate>

Component:

<apex:component controller="B2BSalesOppExecSummaryCC" access="global">
<style>
.toplft{
text-align:left;
white-space: nowrap;
padding: 5px 20px 5px 5px;
}
.lft{
text-align:left;
background-color: #b1dcea;
white-space: nowrap;
padding-left: 5px;
padding-right: 20px;
}
</style>
  <apex:dataTable value="{!wrap1}" var="w">   
    <!--"headerclass works only in outlook but not in gmail and inline styling in the facet works only in gmail but not in outlook, so using both of them-->
    <apex:column width="20%" value="{!w.mName}" headerClass="toplft" style="vertical-align:Top;padding-left:5px;">
     <apex:facet name="header"><span style="text-align:left;white-space: nowrap;padding: 0px 20px 0px 5px;">Sales Leader</span></apex:facet>
    </apex:column>
    <apex:column width="80%">
            <apex:dataTable value="{!w.wrap2}" var="o"> 
            <apex:column width="17%" value="{!o.oName}" headerClass="lft" style="background-color: #e5f3f8;padding-left: 5px;">
             <apex:facet name="header"><span style="text-align:left;white-space: nowrap;background-color: #b1dcea;padding: 0px 20px 0px 5px;">Sales Person</span></apex:facet>
            </apex:column> 
            <apex:column width="17%" value="{!o.oRecTypeName}" headerClass="lft" style="background-color: #e5f3f8;padding-left: 5px;">
             <apex:facet name="header"><span style="text-align:left;white-space: nowrap;background-color: #b1dcea;padding: 0px 20px 0px 5px;">Record Type</span></apex:facet>
            </apex:column>          
            <apex:column width="17%" value="{!o.hasCertified}" headerClass="lft" style="color:{!o.color};background-color: #e5f3f8;padding-left: 5px;">
             <apex:facet name="header"><span style="text-align:left;white-space: nowrap;background-color: #b1dcea;padding: 0px 20px 0px 5px;">Certified for this period?</span></apex:facet>
            </apex:column>     
            <apex:column width="17%" value="{!o.lastCertifiedOn}" headerClass="lft" style="background-color: #e5f3f8;padding-left: 5px;">
             <apex:facet name="header"><span style="text-align:left;white-space: nowrap;background-color: #b1dcea;padding: 0px 20px 0px 5px;">Last Certified Date</span></apex:facet>
            </apex:column>       
            <apex:column width="17%" value="{!o.totalcertified}" headerClass="lft" style="background-color: #e5f3f8;padding-left: 5px;">
             <apex:facet name="header"><span style="text-align:left;white-space: nowrap;background-color: #b1dcea;padding: 0px 20px 0px 5px;"># of Opporunities Certified</span></apex:facet>
            </apex:column>       
            <apex:column width="17%" headerClass="lft" style="background-color: #e5f3f8;padding-left: 5px;">
             <apex:facet name="header"><span style="text-align:left;white-space: nowrap;background-color: #b1dcea;padding: 0px 20px 0px 5px;">SUM Total Contract Value</span></apex:facet>
             <apex:outputtext value="{0, number, $#,###}">
                <apex:param value="{!o.optyTCVSum}"></apex:param>
             </apex:outputtext>
            </apex:column>            
            </apex:dataTable>  
    </apex:column>
  </apex:dataTable>  
</apex:component>

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<wrapper2> wrap2{get; set;}
        public wrapper1(String mgr,List<wrapper2> w)
        {
            this.mName = mgr;
            this.wrap2 = w;
        }
    }
    public List<wrapper1> 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<Opportunity> OpptysReportList = new List<Opportunity>([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<String,String[]> wrapperMap = new Map<String,String[]>();
Map<String,Id> ownerNameIdMap = new Map<String,Id>();    
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<Id,User> userManagerMap = new Map<Id,User>([Select Id,Name,Manager.Name From User Where Id IN :ownerNameIdMap.values() ORDER BY Manager.Name ASC NULLS LAST]);
Map<String,List<wrapper2>> managerNameWrapperMap = new Map<String,List<wrapper2>>();
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<wrapper2> w2;
    if(managerNameWrapperMap.containsKey(m))
    {
        w2 = managerNameWrapperMap.get(m);
    }
    else
    {
        w2 = new List<wrapper2>();           
    }
    w2.add(w);
    managerNameWrapperMap.put(m,w2);   
}
wrap1 = new List<wrapper1>();
for(String m:managerNameWrapperMap.keySet())
{
    wrapper1 w1 = new wrapper1(m,managerNameWrapperMap.get(m));
    wrap1.add(w1); 
}
}
}
Improve this question
9
  • 1
    what is the point of line 3 update emailTemplateExec; ? it appears to do nothing. Don't you want to modify the EmailTemplate in the schedulable's execute() and then call the future method? That said, why not use a VF email template so you can use merge fields in the template that are resolved by the VF email template's controller? This avoids having to modify the template at all – cropredy Dec 3 '17 at 3:24
  • The email template isn't populated with the most recent data when the email is sent out. As mentioned in this article: success.salesforce.com/issues_view?id=a1p300000008YXaAAM (which I mentioned in the above post too), the work-around for this issue is to click on "Edit Properties" button on the template and then click on "Save" button. If I do this and then run the schedulable class then only the email is populated with the most recent data. Update statement in Line #3 is to implement this work-around programmatically, so we don't have to edit the properties manually everytime. – ynl124 Dec 4 '17 at 17:56
  • The template I'm using in the schedulable class currently is a visualforce template, as I have already mentioned in the post. The email template displays a list of opportunity records in a table that is populated by a visualforce component and a controller. The data in the table is not current. For example the table has a column "Last Certified Date", this is a custom field on Opportunities and it gets updated every-time an Opportunity is certified. The template shows the most recent Last Certified Date in the html preview but when I run the schedulable class the sent-out email shows old value – ynl124 Dec 4 '17 at 18:08
  • Ah, so you are trying to dynamically change the subject of a VF Email Template? And you are fetching the subject from the EmailTemplate object via SOQL and it is coming back as null. You probably have thought of hacks to store the subject line in a custom setting/custom metadata – cropredy Dec 4 '17 at 18:16
  • what does the VF email template look like? Please use edit to amend the question – cropredy Dec 4 '17 at 19:17
1

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+'<br/>' + '<br/>' +
       emailTempExec.HtmlValue;



List<Messaging.SingleEmailMessage> reminderMails = new List<Messaging.SingleEmailMessage>();  
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<Messaging.SingleEmailMessage> reminderMails = new List<Messaging.SingleEmailMessage>();  
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.

Improve this answer

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.