I want to make an email reminder if case status != closed to case owner every H+5, H+7, and H+10 ..
H = created date
and I want to make a table (as email body), so the email body = list of cases that the owner has (sending 1 email with the list of cases to 1 owner), instead of sending 1 email for 1 case to 1 owner. .. how do i write the loop? this is what i`ve tried so far..
global class SchReminderActivitytoOwner implements Schedulable {
global void execute(SchedulableContext ctx) {
List<Messaging.SingleEmailMessage> listmail = new List<Messaging.SingleEmailMessage>();
EmailTemplate et = [SELECT Id, Subject, HtmlValue FROM EmailTemplate WHERE developerName = 'ET_Case_Reminder_Testing'];
OrgWideEmailAddress owe = [SELECT ID,IsAllowAllProfiles,DisplayName,Address FROM OrgWideEmailAddress WHERE IsAllowAllProfiles = TRUE LIMIT 1];
Date MyDate = Date.Today();
String subject = 'Testing Reminder';
String htmlBody ='<h1>***JANGAN DIBALAS/DO NOT REPLY***</h1><br>Dear {!Case.OwnerFullName}, <br>Mohon untuk menindaklanjuti pengajuan interaksi berikut ini sebelum melewati waktu SLA :<br><br><table border="1" style="border-collapse: collapse"><tr><th><b>Ticket Number</b></th><th><b>Subject</b></th><th><b>SLA Handling Time Target</b></th><th><b>Created By</b></th><th><b>Detail</b></th></tr>';
//List<Case> listcase = [SELECT Id, ContactEmail, Owner_Name__c, Owner.Email, Status, Ticket_Number__c, CaseNumber, Subject, SLA_Case_Closed_Days__c, CreatedBy.Name, OwnerId
//FROM Case WHERE Email_Target__c = '[email protected]' and Status != 'Closed' Order by Owner.Name LIMIT 50000];
List<User> us = [SELECT Id, Name, ProfileId, Profile.Name, isActive,Email FROM User
WHERE Email = '[email protected]' and IsActive = TRUE]; //this is my email for testing purpose
Integer i = 1;
Id OwnerLast;
List<Case> listcase = new List<Case>();
if(Test.isRunningTest()){
listcase = [SELECT Id, ContactEmail, Owner_Name__c, Owner.Email, Status, Ticket_Number__c, CaseNumber, Subject, SLA_Case_Closed_Days__c, CreatedBy.Name,CreatedDate, OwnerId
FROM Case WHERE Email_Target__c = '[email protected]' LIMIT 1];
}else{
listcase = [SELECT Id, ContactEmail, Owner_Name__c, Owner.Email, Status, Ticket_Number__c, CaseNumber, Subject, SLA_Case_Closed_Days__c, CreatedBy.Name,CreatedDate, OwnerId
FROM Case WHERE Email_Target__c = '[email protected]' and Status != 'Closed' Order by Owner.Name LIMIT 50000];
}
if (listcase.size() > 0){
for (Case cs : listcase){
String TicketNumber = String.Valueof(cs.CaseNumber); if(cs.CaseNumber == null){TicketNumber = '[Not Provided]';}
String Sbj = cs.Subject; if(cs.Subject == null){Sbj = '[Not Provided]';}
String HandlingTimeTarget = String.Valueof(cs.SLA_Case_Closed_Days__c); if(cs.SLA_Case_Closed_Days__c == null){TicketNumber = '[Not Provided]';}
String Link = cs.Id; if(cs.Id == null){Link = '[Not Provided]';}
String PIC = cs.CreatedBy.Name; if(cs.CreatedBy.Name == null){PIC = '[Not Provided]';}
htmlBody = htmlBody.replace('{!Case.OwnerFullName}', cs.Owner_Name__c);
/*htmlBody = htmlBody.replace('{!Case.Ticket_Number__c}', String.Valueof(cs.CaseNumber));
htmlBody = htmlBody.replace('{!Case.Subject}', cs.Subject);
htmlBody = htmlBody.replace('{!Case.SLA_Handling_Time_Target__c}', String.Valueof(cs.SLA_Case_Closed_Days__c)); */
htmlBody += '<tr><td>' + TicketNumber + '</td><td>' + Sbj + '</td><td>' + HandlingTimeTarget + '</td><td>' + PIC + '</td><td><a href="https://cs72.salesforce.com/' + Link + '">Click Here</a></td></tr>';
//i++;
}
//List<User> us = [SELECT Id, Name, ProfileId, Profile.Name, isActive,Email FROM User
//WHERE Email = '[email protected]' and IsActive = TRUE];
for (User us2 : us){
OwnerLast = us2.Id;
htmlBody += '</table><br><br><br><p>Terimakasih,</p><br><p>System Administrator</p>';
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
//Id [] toaddress= New Id[]{};
mail.setOrgWideEmailAddressId(owe.Id);
mail.setSenderDisplayName('Administrator');
mail.setSubject(subject);
mail.setHtmlBody(htmlBody);
mail.setTargetObjectId(OwnerLast);
//mail.setToAddresses(toaddress);
mail.setSaveAsActivity(false);
listmail.add(mail);
Messaging.sendEmail(listmail);
}
}
}
}
and this is the result..
I manually select my own user based on my personal email value ([email protected]).. how do I select every owner of every cases? i tried using OwnerId but nothing happen..
