flow rich text email with list of records
Spring21 brought us rich text emails in flow, which made it fairly simple to create a nice looking email that includes a list of records. throw it in a scheduled flow and you have some serious potential. add a link to a report with inline editing enabled and you have black belt ninja material!
my use case : send opportunity owners a list of their open opportunities that have passed the close date.
here is the full flow
will break down each piece below
Schedule: the flow is scheduled on the user object, and fires for all users where isActive = true (if the owner is inactive, nobody will be notified. I would handle these through a separate process.)
getOpportunities: get all opportunities, where ownerid = $record.id (the user from the scheduled flow) and closeDate is less than $flow.current date
getReport: In the email, I include a link to a report so they can edit it using inline editing for reports (while I dream of the day when engineering allows editing of currency fields in reports). So instead of hard coding that link, I query for the report, since i built in sandbox, and you know you shouldn’t hard code things…
asstOppCount: used to set a variable to count the number of opps found which is used in the decision / decGotOpp to determine whether to proceed. I can never remember what the ‘right’ way is to check if a lookup found any records. And you cant schedule a flow to run monthly, so you run it daily for all users and do nothing on all but one day per month.
sortOpps: get records only let you sort by one parameter, thankfully this lets me sort by close date, and then by name, so the opps are in the right order in the table
asstInitTable: starts the table and inserts the header row. The new flow datatable element is just for screens, but I found this great article on including tables in flow emails. Some very basic html required here, but should be able to teach yourself in 15 mins
the html for the table that is used in the email is stored in a text variable, which I have named vaEmailTableBody
vaEmailTableBody is assembled by using text templates to store pieces of html, and adding the templates to vaEmailTableBody in assignment elements to create the full html table.
Text templates allow you to type straight html and merge fields without using quotes for strings and joining pieces together with & like you need to in a formula field (the forcepanda example uses formula fields, which is why my example looks simpler). ** Make sure to set ‘view as plain text’ on the text template as they default to rich text.**
I use ttTableAndHeader to create the table and to set the headers for the columns. It includes a wrapper div and some very simple css to help format the table to look better in the email. I include opportunity name, close date, stage, and amount. You can tweak and add more columns as you see fit!
if you want to copy/paste the content of the text template, you can do so here
an assignment element asstInitTable adds this text template to the vaEmailTableBody variable.
I also set a number variable in the assignment, which will be incremented for each row of the table to create a stripey row effect without fancy css that probably wont work in emails anyways.
Now that the table is created with 4 columns, we loop over the collection of opportunities owned by this user. Within the loop, for each opportunity record we add a row to the table with the data from the opportunity.
loopOps: loop over each opportunity record, so that we can add the actual data from each opp to the table to a separate row.
asstBuildTable: uses another text template (ttTableRow) to add a row of content to the table, and increments the row number.
ttTableRow starts a table row, adds each of the columns, and closes the table row. the merge fields in ttTableRow bring in opportunity name, close date, stage, and amount. if you want to copy and paste the content of the ttTableRow, you can do so here
Note that I set opportunity name to also link to the record, and avoid hard coding the link by using the ‘partner url’ approach as documented here
{!ffBackgroundColor} is where i get fancy and change the background color of odd vs even rows. the stripey row effect happens via this line
<tr style="background-color: {!ffBackgroundColor}; border: 1px solid #{!ffBackgroundColor};">
ffBackgroundColor calculates if its an odd or even row, and sets the color IF(MOD({!vaRowNumber},2) = 0, "#f2f2f2", "#ffffff")
The loop will add a row to the table for each opportunity. Once we have looped through all opportunities, we close the table, and send the email
asstCloseTable: adds and directly to vaEmailTableBody - no text template required as that is all we need.
the email body itself is constructed using a text template (ttEmailBody), which contains vaEmailTableBody at the spot where I want to insert the list of opportunities
finally the send email action sends the email, with ttEmailBody as the body, and rich text email body set to true.
- i am able to send as an orgWideEmailAddress from a scheduled flow though it was not clear to me from the docs if that was going to work
- i use a formula field for the recipient email with a variable to ensure all my tests are sent to a testing email address, so if i set vaIsDebug to true, it sends to the test address.
.
you can test this by clicking debug, but make sure the recipient email address is hard coded so you wont send to all your users
then set a schedule, activate, and go take a nice long walk…
here is a package you can use to install this into a sandbox and test it out yourself. use this at your own risk.
link to package : https://test.salesforce.com/packaging/installPackage.apexp?p0=04t6S00000160n1