flow rich text email with list of records

Spring21 brought us rich text emails in flow, so now its 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 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.

• i am able to send as an orgWideEmailAddress from a scheduled flow tho 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…