flow rich text email with list of records

now that we have rich text emails in flow, its pretty simple to create a nice looking email that includes a list of records. throw it in a scheduled flow and you have some serious magic potential.

my use case : send opportunity owner 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

getOpportunities: get all opportunities, where ownerid = $record.id 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 the engineering gods allow editing of currency fields in reports). So instead of hard coding that link, I query for the report, since i built in sandbox, and some say you shouldn’t hard code all the 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 be close date, and then by name.

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 table html required here, but should be able to teach yourself in 15 mins

the table html itself is stored in a text variable, which I have named vaEmailTableBody

an assignment element adds a text template to the text variable. Text templates are nice because you can type straight html without using quotes for strings and easily enter merge fields without joining pieces together with & like you need to in a formula field. ** Make sure to set ‘view as plain text’ as they default to rich text.** the text template has a wrapper div and some simple css to help it look a bit better in the email.


I also initialize a number variable in the assignment, which will be incremented for each row of the table and used to create a stripey row effect without fancy css stuff that prob wont work in emails anyways.


loopOps: loop over each opportunity record, so that we can add the actual data from each opp to the table.

asstBuildTable: uses another text template to add a row of content to the table, and increments the row number.


the text template adds a row, adds each of the columns, and closes the row


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")

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…


the email body itself is another text template, 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.

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…



Now read this

Creating a Global Picklist with custom API values

Global picklists are great. As is the ability to customize the API names separately from the label. But while you can create picklist values via copy/paste, you have to update the API values individually. For small picklists its not that... Continue →