We just implement Salesforce NFP (not for profit) for our church to store members database and attendances. Thanks to Salesforce for providing this awesome system for free.
We send birthday email to members who birthday on that date manually, this activities is just too troublesome as someone need to check who is birthday today and sent it manually, it time consuming and prone to human error and many members birthday are just skipped.
Since we are using Salesforce, can we just automate it? As info, we are using Person Account to store all members. Because there is no “scheduler” to execute something in Salesforce based on a date. I am thinking 2 solutions below out of my head when I heard about this requirement:
1. Workflow Rule + Field Update
With a simple Google search, I found this article “Creating an Automatic Birthday Email“. Let us digest what is the logic behind using solution:
- Create an email template in Salesforce
- Create a checkbox field to flag this Contact Opt in for birthday email
- Create another checkbox field to flag contact has not been send email
- Create a date field to tell the system when to send birthday email using “Next Birthday” field
- Create a workflow on Contact (or Account for Person Account) for contact with opt-in email birthday and birthday is not blank
- Create time trigger with action to send birthday email after X hours. For Person Account, since need workflow will only trigger for Account, while Email address on Contact, you need to have another workflow to copy Email address to a custom email field in Account
- Create another time trigger after above trigger executed, this trigger to update checkbox field that email has been sent.
- Create 2nd workflow with criteria: opt-in for birthday email and email has been sent (this will be executed after 2nd time trigger in step 5)
- Immediate action to update “Next birthday” date to next year
- Create time trigger with action to update flag that birthday has not send
- Create 3rd workflow to check if Contact is flag for user who opt-out email birthday
- Create 4th workflow to check if Contact birthdate is changed
- Make sure to activate all Workflow rules
- Mass populate all contact “Next Birthday”
- Make sure email Deliverability to All Email
Note: for Person Account, workflow rule is trigger from Account, not from Contact, make sure the workflow rule object is Account.
Here is the steps from the article.
1 – Create an email template for the Birthday Email.
2 – Create a checkbox field:
1 – We’ll call this field “Receive Birthday Emails” for the purposes of this article
2 – Default should be “Checked”
3 – Add it to all applicable page layouts so that all can see it (this will allow contacts to opt-out of the email)
3 – Create another checkbox field:​
1 – We’ll call this field “Reset Birthday Email System”
2 – Default should be “Checked”
3 – Keep out of ALL page layouts; this should only be worked on by our workflow rules we’ll create.
4 – Read only for everyone
4 – Create a Date field:
1 – We’ll call this field “Next Birthday”
2 – Leave “Default Value” blank
3 – Like “Reset Birthday Email System”, we’ll keep this out of ALL page layouts and keep it read only for everyone.
5 — Create a Workflow Rule:
1 – ​We’ll call it “Send Birthday Email”. This will be the rule that will send the actual email, and then triggers (1 day later) the Birthday Reset process.
2 – For the Object to create the Workflow on, select “Contact.” This will be the same for all workflow rules in this article.
3 – The Evaluation Criteria should be “created, and any time it’s edited to subsequently meet criteria.”
4 – In the first line of Rule Criteria, input: “Receive Birthday Emails” equals “True”
5 – In the second line of Rule Criteria, input: “Reset Birthday Email System” equals “False”
6 – In the third line of the Rule Criteria, input: “Birthday” not equal to “” (leave the third column completely blank)
6 – Click Next.
​
6 – Add a Time Trigger and Action:
1 – ​Click Add Time Trigger.
2 – Set the trigger for 6 Hours after Contact: Next Birthday and click Save. (Note: if you need the email before the birthdate, select amount of time before Next Birthday instead.)
3 – Click Add Workflow Action | New Email Alert
4 – Type in a Description. For Unique Name, we’ll call it “Birthday Email”.
5 – Select the Email Template you created in Step 1.
6 – In the Search field within the Recipient Type section, select Email Field
7 – Select “Email Field: Email” and click Add.​
Note: This will not work when the workflow is created on Account object, which is required when Person Account is enabled. Although there is a standard email field on Account when person accounts are enabled, this field cannot be used in email alerts.
As a work around, we can create a custom Email field on Account object and make it hidden for all profiles except system admin. To update the Person Account Email field’s value in it, we can create another workflow with Field update action
7 – Add Another Time Trigger and Action:
​
1 – Click Add Time Trigger.
2 – Set the trigger for 30 Hours after Contact: Next Birthday and click Save
3 – Click Add Workflow Action | New Field Update
4 – Let’s call it “Check Reset Birthday Email System Box”
5 – Be sure to check the box labeled Re-evaluate Workflow Rules after Field Change
6 – The Field to update is what we called “Reset Birthday Email System”
7 – Under “Checkbox Options”, select “True”
8 – Create a Second Workflow Rule:
​1 – We’ll call it “Reset Birthday Email”. This will be the rule that sets “Next Birthday” for the next birthday after the current date, then triggers the “Send Birthday Email” Workflow rule.
2 – The Evaluation Criteria should be “created, and any time it’s edited to subsequently meet criteria.”
3 – In the first line of Rule Criteria, input: “Receive Birthday Emails” equals “True”
4 – In the second line of Rule Criteria, input: “Reset Birthday Email System” equals “True”
5 – Click Next.
9 – Add a Field Update:
1 – Under Immediate Actions, click Add Workflow Action | New Field Update
2 – For the Name, let’s call it “Reset Next Birthday”. This will actually set the Next Birthday field with the new value that represents the next birthday.
3 – For Field, select Next Birthday.
4 – Leave the Re-evaluate Workflow Rules after Field Change checkbox unchecked.
5 – Select Use a formula to set the new value, and Show Formula Editor
For Account/Contact:
IF(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)) <= TODAY(), DATE(YEAR(TODAY())+1,MONTH(Birthdate),DAY(Birthdate)), DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)))
For Person Accounts:
IF(DATE(YEAR(TODAY()),MONTH(PersonBirthdate),DAY(PersonBirthdate)) <= TODAY(), DATE(YEAR(TODAY())+1,MONTH(PersonBirthdate),DAY(PersonBirthdate)), DATE(YEAR(TODAY()),MONTH(PersonBirthdate),DAY(PersonBirthdate)))
10 – Add a Time Trigger:​Click Add Time Trigger.
1 – Set the trigger for 1 hour after Rule Trigger Date. This is to ensure that this action happens after changing the Next Birthday field.
2 – Add a Field Update. Select the Field as Reset Birthday Email System.
3 – Check the Re-evaluate Workflow Rules after Field Change box.
4 – Select Checked is False
5 – Click Save.
11 – Create a Third Workflow Rule
1 – Let’s call this workflow rule “Birthday Email Opt-Out”. This will check the Reset checkbox when a user discontinues the email for a contact, so that if the contact wants the email again (sometime later), the Next Birthday field is properly reset before giving out an email on the wrong date.
2 – This workflow rule will be Evaluated Every time a record is Created or Edited (the 2nd option)
3 – Rule Criteria: “Receive Birthday Emails” equals “False”
4 – Click Add Workflow Action | Select Existing Action
5 – Under “Search:”, select “Field Update”
6 – Select Field Update: Check Reset Birthday Email System Box and click Add, then click Save
​12 – Create a Fourth (and final) Workflow Rule
1 – Let’s call it “Birthday Change”. This rule takes care of the unlikely event that a contact’s Birthdate field changes – we trigger our second Rule, so as to reset Next Birthday to reflect the new date.
2 – This workflow rule will be Evaluated Every time a record is Created or Edited (the 2nd option)
3 – Rule Criteria: Use a formula :
For Account/Contact:
(MONTH(Birthdate) <> MONTH(Next_Birthday__c) || DAY(Birthdate) <> DAY(Next_Birthday__c)) && Receive_Birthday_Emails__c = True
For Person Accounts:
(MONTH( PersonBirthdate ) <> MONTH(Next_Birthday__pc) || DAY(PersonBirthdate) <> DAY(Next_Birthday__pc))
4 – Click Add Workflow Action | Select Existing Action
5 – Under “Search:”, select “Field Update”
6 – Select Field Update: Check Reset Birthday Email System Box and click Add, then click Save
​13 – Activate all Workflow Rules
1 – Go to Setup > Create > Workflow & Approvals > Workflow Rules. Click “Activate” next to each of the four new workflow rules. This allows the workflow rules to actually run.
14 – Verification: before enabling for all contacts​
1 – NOTE:This step is not technically necessary, but recommended to ensure that all emails are scheduled properly.
2 – Create a test contact on an account. Add an email field and keep the “Receive Birthday Emails” box checked. Set the contact’s birthday to Tomorrow.
3 – Monitor the Time Based Queue (see Monitoring the Workflow Queue) You should see 1 Workflow in the queue for your contact, for the workflow “Reset Birthday Email”, set for 1 hour from now.
4 – Wait approx. one hour. After this rule executes, you should see 2 workflow rules in the queue, one scheduled at 6am on the Contact’s birthday, and another scheduled 6am the next day, both for the rule “Send Birthday Email”.
5 – If desired, you can do further verification the next day, that the email get sent, and then the next day at 6am, the Reset workflow should trigger and set Next Birthday to next year, and one hour later (7am), 2 more Workflow rules will be in the queue.
6 – When satisfied with verification, move on to Step 15.
15 – Enable All contacts to Receive Emails
1 – ​In order for any existing contacts to receive birthday emails, run a report that returns all contacts for which you’d like this feature enabled (note you may need to create a custom report type that includes just Contacts), and include these three fields: Contact ID, Receive Birthday Emails, and Reset Birthday Email System.
2 – Export the details of the report and open the document as a spreadsheet, and change ALL 0s to 1s and save as CSV. Then import them back in using the Import Wizard as detailed in this article.
3 – Due to the time-based queue limitations, the time it takes for the initial reset and scheduling of the birthday emails will vary depending on how many contacts are in your system. The limit for time-based workflow action execution is 500 per hour, including any other time-based workflow action that may be in your system.
2. Apex Scheduler
For developer familiar writing apex code, this should be pretty simple to write the apex class. But for those of you not familiar in writing apex code, there is an app in AppExchange develop by Salesforce Labs – Birthday eMailer, and read thecustomization guide here.
This app simply implementing apex scheduler:
- Create an email template in Salesforce with Email Template name match with the Apex class
- Create a checkbox field “Send Birthday Email” to flag Contact opt-in for Birthday email, set this field default to True
- Set the scheduler to be executed daily:
- Query contact object for contact with birthday today
- Query EmailTemplate for the right Email template name
- Send mass email
This solution is much simple than using Workflow rule, as in workflow we need to deal with many workflow rules and field update actions, so it will very confuse.
This solution also work for Person Account, because for each person account, Salesforce will create the Contact as well, so it work well when just query to Contact.
Here is the apex class modify from Birthday eMailer:
1: /**************************************************************************/
2: //The Birthday Emailer
3: //
4: //This class can be scheduled to run nightly to send emails to Contacts
5: //or Person Accounts on their birthday
6: //
7: //Emails will only be sent to contacts that meet the following conditions:
8: // *the Birthday or Anniversary field is filled out
9: // *the Email Opt Out is unchecked
10: // *the "send birthday email" checkbox is checked
11: // *the Email is not blank
12: //
13: //Feel free to edit this code, or change the email templates.
14: //
15: /***************************************************************************/
16:
17:
18: global class BirthdayCronJob implements Schedulable{
19:
20: //mandatory function called by the Apex Scheduler
21: global void execute(SchedulableContext SC) {
22: sendmail(); // our main function that does the email sending
23: }//end execute()
24:
25: //Get email addresses of people with birthdays and who are to receive emails
26: public List<Id> getBirthdayEmailAddresses(Integer Month, Integer Day)
27: {
28: List<Id> mailToIds = new List<Id>();
29:
30: //find a list of contacts with birthdays today
31: Contact[] c = [SELECT Id, email, Birthdate, Send_Birthday_Email__c, HasOptedOutOfEmail
32: FROM Contact
33: WHERE email <> '' AND
34: DAY_IN_MONTH(Birthdate) = : Day
35: AND CALENDAR_MONTH(Birthdate) = : Month
36: ];
37:
38: //add the list of contacts to a list
39: for(Contact recipient : c) {
40:
41: //If( contacts who's birthday is today can receive emails AND has the "Send Birthday Email" checked)
42: //{ add to birthday email recipient list }
43: //
44:
45: System.Debug('\n*******Found Birthday Recipient');
46:
47: if (recipient.Send_Birthday_Email__c == true && recipient.HasOptedOutOfEmail == false)
48: {
49: mailToIds.add(recipient.Id); // add to email contact array
50: System.Debug('\n*******Recipient: '+ recipient.email);
51: //and add to Chatter Array
52:
53: } else {
54: //Just add to chatter array
55: System.Debug('\n*******NO Recipient');
56: }
57:
58: }
59:
60: //return the list
61: return mailToIds;
62: }//end getBirthdayEmailAddresses()
63:
64: public void sendMail()
65: {
66: //define variables
67: String debugAddress = '[email protected]';
68: String BirthdayEmailTemplateName = 'Happy_Birthday';
69: String AnniversaryEmailTemplateName = 'Celebrating_your_Anniversary';
70: String debugMessage;
71: String[] toAddresses;
72:
73: Integer DayOfEvent = date.today().day();
74: Integer MonthOfEvent = date.today().month();
75:
76:
77: // build the Birthday list
78:
79: //get the list of people with birthdays - this can justifiably come back empty, on a day when no contacts were born
80: List<Id> BirthdayIdsList = getBirthdayEmailAddresses(MonthOfEvent,DayOfEvent);
81:
82: //Set the templates
83: EmailTemplate birthdayTemplate = [select Id,Name,Subject,body from EmailTemplate where DeveloperName = :BirthdayEmailTemplateName];
84:
85: //EmailTemplate anniversaryTemplate = [select Id,Name,Subject,body from EmailTemplate where name like :temp+'%'];
86: //If we have a template, and we have a list of email addresses, then send the email
87: if(birthdayTemplate != null && BirthdayIdsList.isEmpty() == false)
88: {
89:
90: Messaging.MassEmailMessage birthdayMail = new Messaging.MassEmailMessage();
91:
92: birthdayMail.setTargetObjectIds(BirthdayIdsList);
93: birthdayMail.setTemplateId(birthdayTemplate.Id);
94: birthdayMail.setUseSignature(false);
95: birthdayMail.setSaveAsActivity(true);
96:
97: // Send the email
98: try {
99: Messaging.sendEmail(new Messaging.MassEmailMessage[] { birthdayMail });
100: }catch(Exception e)
101: {
102: System.Debug(e);
103: }
104:
105: }
106: else
107: {
108: System.Debug('BirthdayCronJob:sendMail(): Either an email template could not be found, or no Contact has a birthday today');
109: }//end if
110:
111: }//end sendMail()
112:
113: }//end class BirthdayCronJob
If you modify the apex class in sandbox as needed, you will need Test Method to deploy the Apex Class:
1: @isTest
2: class BirthdayCronJobTest {
3:
4: static testmethod void test() {
5: //Test.startTest();
6:
7:
8: Integer testMonth = date.today().month();
9: Integer testDay = date.today().day();
10: BirthdayCronJob BCJ = new BirthdayCronJob();
11:
12: //run the test once, without ensuring that a contact with today's birthday exists;
13: BCJ.sendMail();
14:
15: Contact testContact = new Contact();
16: testContact.FirstName = 'Test';
17: testContact.LastName = 'Contact';
18: testContact.Email = '[email protected]';
19: testContact.Birthdate = date.today();
20: testContact.Send_Birthday_Email__c = true;
21: insert testContact;
22:
23: //run the test again, now that a contact with today's birthday exists
24: BCJ.sendMail();
25:
26: // Schedule the test job
27:
28: String CRON_EXP = '0 0 0 3 9 ? 2022';
29: String jobId = System.schedule('testBasicScheduledApex', CRON_EXP, new BirthdayCronJob());
30:
31: // Get the information from the CronTrigger API object
32:
33: CronTrigger ct = [SELECT id, CronExpression, TimesTriggered, NextFireTime
34: FROM CronTrigger
35: WHERE id = :jobId];
36:
37: // Verify the expressions are the same
38: System.assertEquals(CRON_EXP, ct.CronExpression);
39:
40: // Verify the job has not run
41: System.assertEquals(0, ct.TimesTriggered);
42:
43: // Verify the next time the job will run
44:
45: System.assertEquals('2022-09-03 00:00:00', String.valueOf(ct.NextFireTime));
46: //System.assertNotEquals('testScheduledApexFromTestMethodUpdated',
47: // [SELECT id, name FROM account WHERE id = :a.id].name);
48:
49: //Test.stopTest();
50:
51: //System.assertEquals('testScheduledApexFromTestMethodUpdated',
52: // [SELECT id, name FROM account WHERE id = :a.id].name);
53:
54: //Clean up
55: delete testContact;
56:
57: }
58: }
Reference:
- Birthday eMailer
- Creating an Automatic Birthday Email (Knowledge Article Number: 000181218)