Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by #AwesomeAdmin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Gorav Seth reimagined a series of disconnected emails into a single consolidated email with a list of records using Flow.
Use case
Send the opportunity owner a single email that lists all of their open opportunities that have passed the close date, to help ensure accurate forecast data.
With Workflow Rules, we had to send one email per opportunity. Now, with rich text emails in Flow, we can send a single email with a list of records—and even include a link to a report that will allow them to edit the opportunities inline!
Overview of the approach
Set up a scheduled flow on the User object.
Get all the opportunities for the user that are past their close date.
Evaluate if the user owns any opportunities.
Sort the opportunities by close date and name.
Create an HTML table.
Loop over the opportunities to add rows to the table with the data from each opportunity.
Close the HTML table.
Send a rich text email in Flow, with the HTML table in the body.
Step-by-step guide
A brief note about naming conventions used in this flow: The elements in the flow use a naming convention that I find useful: [element type abbreviation]_[descriptionInCamelCase].
For example, vaOppCount is a variable I use to store the total count of opportunities owned by the user.
Using a naming convention makes it easy to find elements and helps clarify what each item does. You’re free to name things as you choose, but finding a naming convention that works for you and sticking with it will pay dividends over time.
Set up a scheduled flow on the User object
Create a scheduled flow that runs for all active users.
Scheduled flows run once for each user that meets the criteria—in our case, for each active user. So, if there are 10 active users, it will run 10 times, and the $record.Id will be the UserId for the current iteration.
Flow Starts: Today’s date
Frequency: One Time (This is for testing and will be updated once you activate the flow.)
Object: User
Conditions: Active = TRUE
Note: If the opportunity owner is inactive, nobody will be notified. Those can be handled separately.
Get all opportunities for the User
Add a Get Records element.
This gets all open opportunities owned by the current user that are past their close date.
Label: getOpportunities
Object: Opportunity
Conditions (All Conditions Are Met):
CloseDate LESS THAN - $Flow > Current Date
IsWon EQUALS False
IsClosed EQUALS False
OwnerId EQUALS $Record > User ID
How Many Records to Store
All Records
Evaluate if the user owns opportunities
Add an Assignment element to set a number variable to the number of records found.
This lets us count the number of open opportunities owned by this user from the Get Records, so that we can skip any user who does not own open opportunities in the next step.
Label: asstOppCount
Variable: New Resource
Resource Type: Variable
API Name: vaOppCount
Data Type: Number
Decimal Places: 0
Operator: Equals Count
Value: getOpportunities
Add a Decision element to evaluate the day of the month and if the owner has any open opportunities.
I only want this to run on the 15th of the month, and I only want to send an email to people who own open opportunities. The decision handles this. The order of the outcomes ensures that this only runs on the right day of the month, and only if the user owns open opportunities.
Label: decGotOpps
Outcomes
Outcome 1:
Label: Not the right day
Condition Requirements: All Conditions Are Met
Resource: New Resource
Type: Formula
API Name: ffDayOfMonth
Data Type: Number
Formula: DAY(TODAY())
Operator: Not Equal to
Value: 1
Outcome 2:
Label: Owner Has Opps
Condition Requirements: All Conditions Are Met
Resource: select vaOppCount
Operator: Greater Than or Equal
Value: 1
Default Outcome
Label: no opps
Sort the opportunities
Add a Collection Sort element.
This ensures that the open opportunities are displayed in the order desired, and allows sorting by multiple fields. I sorted by close date and then by name.
Label: sortOpps
Collection Variable: getOpportunities
Sort by:
CloseDate: Ascending
Name: Ascending
Keep all items
Create an HTML table
To initiate an HTML table with the headers you need, you have to manually craft the HTML for the table, but text templates make it quite easy.
This will require some light HTML. This is all the HTML you need to know for the whole flow. If you want to dig in a bit deeper, check out the documentation at W3C.
Tables are created with the <table> tag and ended with the </table> tag.
Each header column is defined with <th>YOUR COLUMN NAME</th>.
Each row is defined with a <tr> and </tr> tag.
Each column is defined with a <td> and </td> tag.
A hyperlink is created using <a href=“LINK_LOCATION”>LINK TEXT</a>.
The following four Flow elements create the table, add the content, and close the table.
Create a new text variable
This variable holds all the HTML you’ll use in the email. You’ll use Assignment elements to add HTML to this text variable using text templates.
Label: vaEmailTableBody
Data Type: Text
Create a new text template
This initializes a new HTML table and defines the columns you’ll display in the email.
Label: ttTableAndHeader
IMPORTANT: Select View as Plain Text (default is rich text).
Plain text lets you store raw HTML. Rich text will insert its own HTML as you type and will not work.
Body: Use the following or tweak as desired. This contains a bit of CSS to improve the appearance in Outlook.
Line 1 is a container to keep the table from getting too wide on large monitors.
Line 2 creates the table.
Line 3 begins the header row.
Lines 4-7 define the column headers. If you want more or less, you can copy the whole line and just change the field name.
Line 8 closes the header row.
Create a number variable to store the row number
This will be used to format the background of each table row, to make it fancy.
API Name: vaRowNumber
Data Type: Number
Default Value: 1
Description: Track row number to do stripey table magic—starting with row 1 (odd) = no background
Add an Assignment element to create the table and the headers.
This adds the HTML from the text template to the text variable that will be used in the email.
Label: asstInitTable
Set Variable Values:
vaEmailTableBody EQUALS ttTableAndHeader
Loop over the opportunities to add rows to the table
Add a Loop element.
The Loop element iterates over each opportunity in the collection, letting us insert a new row in the table for each opportunity, with the data from that record.
Label: loopOps
Select Collection Variable: getOpportuntiies
Direction: First item to last item
Create a new formula field for the link to the opportunity
This lets you insert a link to the opportunity without hard coding your URL. You can do this using a Flow formula, with the syntax as described in this post. Hard coding URLs sets you up for failure, as the URL schemes can change over time. Future-proof your configuration by doing it properly from the start. See this article for more details on the dangers of hard coding configuration, and how to avoid it.
New Resource
Type = Formula
API Name = ffBaseURL
Type = Text
Formula = Left($Api.Enterprise_Server_URL_190,(find('/services',$Api.Enterprise_Server_URL_190)))
Create a new formula field for the background color of the row
API Name: ffBackgroundColor
Description: Table row background color: odd rows = no color
Line 1 creates the row and sets the background color and border. Lines 2-4 merge in the data from the opportunity. I’m using the opportunity name, close date, stage, and amount.
Note that in line 2, I’m creating a link to the opportunity using a formula field, the opportunity ID, and the <a> tag.
Add an Assignment element in the loop
This adds a row of HTML to the table, with the merge fields from the current opportunity in the loop.
Label: asstBuildTable
Set Variable Values:
vaEmailTableBody ADD ttTableRow
Close out the table
Add an Assignment element (after the loop is complete).
The HTML to close the table is so simple, I don’t use a text template. I just add it directly from the Assignment element.
Label: asstCloseTable
Set Variable Values
vaEmailTableBody ADD </table>
vaEmailTableBody ADD </div>
And with that, the hard work is done. All that remains is to send the email!
Send the email
Create a text template for the email body.
This contains vaEmailTableBody, which is the HTML table of the opportunities, as well as whatever else you want to communicate.
API Name: ttEmailBody
IMPORTANT: Leave View as Rich Text (default). Otherwise, you’ll see raw HTML in your email, not a table.
Body:
Add whatever text you want.
Put {!vaEmailTableBody} wherever you want the table of opportunities to appear in the email.
I recommend including a description at the bottom for why they’re receiving the email.
Extra credit: Link to a report, filtered for ‘my opportunities’, where they can edit the opportunity stage and close date inline in the report (sadly, they can’t edit opportunity amount, as currency fields are not supported for inline editing, so vote for this idea.)
Create a formula field for the month and year
This will be used in the email subject to include the month and year.
NOTE: While you test this, set it to your email—don’t spam your users!
Rich-Text-Formatted Body: GlobalConstant > TRUE
Sender Email Address: Enter an organization-wide email address in your instance.
Sender Type: OrgWideEmailAddress
Now, do some testing—and remember to check that the recipient email in the Send Email action is NOT the user before testing! Once you’re ready, update the recipient email, set the start date and frequency by clicking the Start element, and activate the flow. Lastly, think about where else you might use this approach!
Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by Awesome Admin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Bradley Condon built the […]
Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by Awesome Admin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, let’s see how Ohad Idan vanquishes shared […]
Welcome to another “Automate This!” In this live-streamed video series, we cover all things automation, from use cases and best practices to showcasing solutions built by Awesome Admin Trailblazers like you. With automation, you can remove manual tasks, drive efficiency, and eliminate friction and redundancy. In this episode, learn how Becka Dente leverages a simple […]