+ Start a Discussion
Russell baker 1Russell baker 1 

send email alert to opportunity owner when opportunities closed date has passed

Hi Experts,
I want to send email alerts to opportunity owners when their opportunity close date passed. Let say if an opportunity owner have 4 opportunities with same close date so only one email alert should trigger with all 4 opportunity details like Opportunity name, close date and salesforce link.
I know it can be achieve by batch apex but my batch is not working. I haven't get any error. schedule job done is showing in Sechdule job view.
but Opportuntiy owner didn't get any email.
Please find below my batch:
global class SendEmailToopsowner implements Database.Batchable<sObject>  {
    map<string,list<opportunity>> userEmailTasklistmap = new map<string,list<opportunity>>();
    
    global Database.QueryLocator start(Database.BatchableContext BC){
        return Database.getQueryLocator([SELECT Id, Name, StageName, CloseDate, Owner.Email FROM Opportunity WHERE CloseDate < TODAY  and StageName != 'closed-Won' or StageName != 'closed-Lost'  ]);
    }
    
    global void execute(Database.BatchableContext BC, List<opportunity> scope){
        for(opportunity opp : scope){
            if(!userEmailTasklistmap.Containskey(opp.owner.email)){
                userEmailTasklistmap.put(opp.owner.email, new list<opportunity>());
            }
            userEmailTasklistmap.get(opp.owner.email).add(opp);
            
          }  
        List<Messaging.SingleEmailMessage> mails = new List<Messaging.SingleEmailMessage>();
    
            for(string email : userEmailTasklistmap.keyset()){
                
                Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
                list<string> toAddresses = new list<string>();
                toAddresses.add(email);
                mail.setToAddresses(toAddresses);
                mail.setSubject('Opportunity close date passed');                
                String username = userEmailTasklistmap.get(email)[0].owner.name;
                String htmlBody = '';
                
                htmlBody = '<table width="100%" border="0" cellspacing="0" cellpadding="8" align="center" bgcolor="#F7F7F7">'+
                            +'<tr>'+
                              +'<td style="font-size: 14px; font-weight: normal; font-family:Calibri;line-height: 18px; color: #333;"><br />'+
                                   +'<br />'+
                                    +'Dear '+username+',</td>'+
                            +'</tr>'+
                            +'<tr>'+
                                +'<td style="font-size: 14px; font-weight: normal; font-family:Calibri; line-height: 18px; color: #333;"> You have Pending Task </td>'+
                            +'</tr>'+
                        +'</table>';
 
                htmlBody +=  '<table border="1" style="border-collapse: collapse"><tr><th>Name</th><th>StageName</th><th>CloseDate</th></tr>';
                for(opportunity opp : userEmailTasklistmap.get(email)){
                    
                    String CloseDate = '';
                    if (opp.CloseDate != null)
                        CloseDate = opp.CloseDate.format();                    
                    else
                        CloseDate = '';
                    String Name = opp.Name;
                    datetime dt = opp.CloseDate;
                    string ClosedDate = dt.format('M/d/yyyy');
                    string StageName = opp.StageName;
                    string View = URL.getSalesforceBaseUrl().toExternalForm()+'/'+ opp.id;
                    string clickhere = view;                    
                    htmlBody += '<tr><td>' + Clickhere +'</td><td>' + Name + '</td><td>' + CloseDate + '</td><td>' + CloseDate + '</td></tr>';                    
                }
                 htmlBody += '</table><br>';
                 mail.sethtmlBody(htmlBody);
                 mails.add(mail);                    
            }
             if(mails.size()>0)
             Messaging.sendEmail(mails);
    }
    global void finish(Database.BatchableContext BC){        
    }
}

Kindly help!
Best Answer chosen by Russell baker 1
ManojSankaranManojSankaran
Hi Russel,


I tried ur code in my developer org and when running the batch class i got error on line # 27

System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: User.Name Class.SendEmailToopsowner.execute: line 27, column 1

This may be the reason for the issue. kindly update the query, below is the updated query

Add this "owner.name" field in the soql query.

After modifiying the query the code worked fine and below is the email that i have received. Mark is as answer if it solves ur problem.


User-added image
 

All Answers

ManojSankaranManojSankaran
Hi Russel,


I tried ur code in my developer org and when running the batch class i got error on line # 27

System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: User.Name Class.SendEmailToopsowner.execute: line 27, column 1

This may be the reason for the issue. kindly update the query, below is the updated query

Add this "owner.name" field in the soql query.

After modifiying the query the code worked fine and below is the email that i have received. Mark is as answer if it solves ur problem.


User-added image
 
This was selected as the best answer
Russell baker 1Russell baker 1
Hi ManojSankaran,
Can you please tell me the updated code. where i have to update.
 
ManojSankaranManojSankaran
Line number 5 I have updated the query

added 

OWNER.name
Russell baker 1Russell baker 1
Hi, I have wrote test class for this but it did not work.
@isTest 

public class SendEmailToopsownerTest { 

static testMethod void testMethod1(){ 

        Profile pro = [SELECT Id FROM Profile WHERE Name='!Sales'];  
        User usr = new User( Alias = 'standt', Email='standarduser@tt.com',  
        EmailEncodingKey='UTF-8', LastName='Testing1', LanguageLocaleKey='en_US',  
        LocaleSidKey='en_US', ProfileId = pro.Id,  
        TimeZoneSidKey='America/Los_Angeles', UserName='standarduser@tt.com'); 

        System.runAs(usr) { 
            Account acc = new Account(); 
                acc.Name = 'Test Account'; 
                insert acc; 
                acc=[SELECT id,Name FROM account WHERE id=:acc.Id]; 
                System.assertEquals(acc.Name,'Test Account'); 
            Opportunity opp = new Opportunity(); 
                opp.AccountId = acc.Id;  
                opp.Name = 'Testing'; 
                opp.StageName = 'Prospecting'; 
                opp.CloseDate = System.Today(); 
                opp.Owner.name = 'Testing1';
                opp.Owner.Email = 'standarduser@tt.com';				
                insert opp ; 
                opp=[SELECT id,Name,StageName,CloseDate,Owner.name,Owner.Email FROM Opportunity WHERE id=:opp.Id]; 
                System.assertEquals(opp.StageName ,'Prospecting'); 
             }
             Test.StartTest(); 
                Database.executeBatch (new SendEmailToopsownerTest (),200); 
            Test.StopTest(); 
    } 
 }

I am getting error. Please help!