-3
public class MonPipelineReport{    
    public static void Get_Officer()
    {
        Date startDate = Date.today().addMonths(-1);
        startDate = Date.newInstance(startDate.year(),startDate.month(),1);
        Date endDate = startDate;
        Integer days = Date.daysInMonth(endDate.year(), endDate.month());
        endDate = Date.newInstance(startDate.year(),startDate.month(),days);
        List<Lead> allLeads = [SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, 
                               Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c 
                               FROM Lead WHERE CreatedDate >=: startDate AND CreatedDate <=: endDate AND Loan_Officer_1a__c != null LIMIT 50000];
        EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];
        Map<Id,List<Lead>> leadMap = new Map<Id,List<Lead>>();
        List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMEssage>();
        if(allLeads != null && allLeads.size() > 0){
            for(Lead l: allLeads){
                if(!leadMap.containsKey(l.Loan_Officer_1a__c)){
                    leadMap.put(l.Loan_Officer_1a__c, new List<lead>());
                }
                leadMap.get(l.Loan_Officer_1a__c).add(l);
            }
        }
        if(leadMap.keySet().size() > 0){
            Map<Id,Contact> officers = new Map<Id,Contact>([SELECT Id,Email,Name FROM Contact WHERE Id IN: leadMap.keySet()]);
            for(Id i: leadMap.keySet()){
                Contact con = officers.get(i);
                System.debug(con);
                if(String.isnOtBlank(con.Email)){
                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setToAddresses(new String[]{con.EMail});
                    mail.setSubject(templateId.Subject);
                    String html = templateId.HtmlValue;
                    html = html.replace('||OfficerName||',con.Name);
                    String leadsTable = '<table cellpadding="3" cellspacing="3" width="100%" align="center" border="1" style="border-collapse:collapse;">'+
                        '<tr style="font-weight:bold;"><td>Name</td><td>Phone</td><td>Starting Credit Score</td><td>Status</td><td>Enrolled On</td><td>Est. Re Pull Date</td><td>Realtor Name</td></tr>';
                    for(Lead l: leadMap.get(i)){
                        leadsTable += '<tr><td>'+l.Name+'</td>'+
                            '<td>'+l.Phone+'</td><td>'+l.Starting_Credit_Score__c+'</td><td>'+l.Status+'</td><td>'+l.Enrolled_On__c+'</td>'+
                            '<td>'+l.Est_Re_Pull_Date__c+'</td><td>'+l.Realtor_Name__c+'</td></tr>';
                    }
                    leadsTable += '</table>';
                    html = html.replace('||Leads||',leadsTable);
                    mail.setHTMLBody(html);
                    mails.add(mail);
                }
            }
        }
        if(mails.size() > 0){
            Messaging.sendEmail(mails);
        }
    }

closed as off-topic by David Reed, Pranay Jaiswal, battery.cord, glls, Jayant Das Mar 8 at 17:56

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – David Reed, Pranay Jaiswal, battery.cord, glls, Jayant Das
If this question can be reworded to fit the rules in the help center, please edit the question.

0

try the code below.

global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {
        Date startDate = Date.today().addMonths(-1);
    startDate = Date.newInstance(startDate.year(),startDate.month(),1);
    Datetime realStartDate = Datetime.newInstance(startDate, Time.newInstance(0, 0, 0, 0));

    Date endDate = startDate;
    Integer days = Date.daysInMonth(endDate.year(), endDate.month());
    endDate = Date.newInstance(startDate.year(),startDate.month(),days);
    Datetime realEndDate = Datetime.newInstance(endDate.addDays(1), Time.newInstance(0, 0, 0, 0));

    query += ' WHERE CreatedDate >= ' + realStartDate + ' AND CreatedDate <= ' + realEndDate + ' AND Loan_Officer_1a__c != null';
    return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, list<Lead> allLeads) {
        Map<Id,List<Lead>> leadMap = new Map<Id,List<Lead>>();
        List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMEssage>();
        if(allLeads != null && allLeads.size() > 0){
            for(Lead l: allLeads){
                if(!leadMap.containsKey(l.Loan_Officer_1a__c)){
                    leadMap.put(l.Loan_Officer_1a__c, new List<lead>());
                }
                leadMap.get(l.Loan_Officer_1a__c).add(l);
            }
        }
        if(leadMap.keySet().size() > 0){
            Map<Id,Contact> officers = new Map<Id,Contact>([SELECT Id,Email,Name FROM Contact WHERE Id IN: leadMap.keySet()]);
            for(Id i: leadMap.keySet()){
                Contact con = officers.get(i);
                System.debug(con);
                if(String.isnOtBlank(con.Email)){
                    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                    mail.setToAddresses(new String[]{con.EMail});
                    mail.setSubject(templateId.Subject);
                    String html = templateId.HtmlValue;
                    html = html.replace('||OfficerName||',con.Name);
                    String leadsTable = '<table cellpadding="3" cellspacing="3" width="100%" align="center" border="1" style="border-collapse:collapse;">'+
                        '<tr style="font-weight:bold;"><td>Name</td><td>Phone</td><td>Starting Credit Score</td><td>Status</td><td>Enrolled On</td><td>Est. Re Pull Date</td><td>Realtor Name</td></tr>';
                    for(Lead l: leadMap.get(i)){
                        leadsTable += '<tr><td>'+l.Name+'</td>'+
                            '<td>'+l.Phone+'</td><td>'+l.Starting_Credit_Score__c+'</td><td>'+l.Status+'</td><td>'+l.Enrolled_On__c+'</td>'+
                            '<td>'+l.Est_Re_Pull_Date__c+'</td><td>'+l.Realtor_Name__c+'</td></tr>';
                    }
                    leadsTable += '</table>';
                    html = html.replace('||Leads||',leadsTable);
                    mail.setHTMLBody(html);
                    mails.add(mail);
                }
            }
        }
        if(mails.size() > 0){
            Messaging.sendEmail(mails);
        }
    }

    global void finish(Database.BatchableContext BC) {

    }
   }
  • it works thank you so much ........... – Muhammad Jawwad Mar 8 at 7:50
  • please share the test class also? – Muhammad Jawwad Mar 8 at 7:51
  • 3
    Would it not be better to have execute build the mails and then have them sent in one operation during the finish method? This would require the batchable to be stateful, and you will have to be careful to hold these details in a serializable object of course. You must also consider email limits: developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… . A final point: use of an email template and mailmerge will cause an extra query per contextual object so again consider (query) limits. – Phil W Mar 8 at 8:15
  • 2
    Note that your test should use the Test.startTest() and Test.stopTest() API calls around the Database.executeBatch call used to trigger the test, otherwise the batch will not be executed. – Phil W Mar 8 at 8:19
  • 2
    We tend to discourage "code dump" answers - a large code paste without explanation - for pretty much this reason: they lead to a lot of back and forth in the comments and don't provide as much value to other community members. SFSE's question and answer guidelines discourage asking for code to be written. – David Reed Mar 8 at 13:20

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