Automate This! — Consolidate Emails 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

1. Set up a scheduled flow on the User object.
2. Get all the opportunities for the user that are past their close date.
3. Evaluate if the user owns any opportunities.
4. Sort the opportunities by close date and name.
5. Create an HTML table.
6. Loop over the opportunities to add rows to the table with the data from each opportunity.
7. Close the HTML table.
8. 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.

<div style="max-width:800px;">
<table style="border:1px solid black;width:100%;">
<tr>
<th style="border-bottom: 1px solid black; text-align:left;">Opportunity Name</th>
<th style="border-bottom: 1px solid black; text-align:left;">Close Date</th>
<th style="border-bottom: 1px solid black; text-align:left;">Stage</th>
<th style="border-bottom: 1px solid black; text-align:left;">Amount</th>
</tr>

• 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
• Data Type: Text
• Formula: IF(MOD({!vaRowNumber},2) = 0, "#f2f2f2", "#ffffff")

Create a new text template

This is the content for each row (Opportunity):

• Label: ttTableRow
• IMPORTANT: Select View as Plain Text (default is rich text).
• Body

<tr style="background-color: {!ffBackgroundColor}; border: 1px solid {!ffBackgroundColor};">
<td style="padding:2px;"><a href="{!ffBaseURL}{!loopOps.Id}">{!loopOps.Name}</a></td>
<td style="padding:2px; min-width:140px;">{!loopOps.CloseDate}</td>
<td style="padding:2px;">{!loopOps.StageName}</td>
<td style="padding:2px;">{!loopOps.CurrencyIsoCode} {!loopOps.Amount}</td>
</tr>

• 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.

• API Name: ffMonthYear
• Data Type: Text
• Formula: CASE(MONTH(TODAY()),1,"January",2,"February", 3, "March", 4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December","Voldemort") & " "&TEXT(YEAR(TODAY()))

Create a text template for the email subject

Customize the subject by inserting the month and year.

• API Name: ttEmailSubject
• Set to View as Plain Text. (You may be able to have rich text email subjects; I have not tested that.)
• Body (customize as desired):
  • Review your open opportunities : {!ffMonthYear}

Add an action to send the email

This sends the email!

• Type: Send Email
• Label: Send final email
• Set Input Values:
  • Body: ttEmailBody
  • Subject: ttEmailSubject
  • Recipient Email Addresses (comma separated): {!$Record.Email})
    • 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!

Resources

• External Site: Gorav Seth: flow rich text email with list of records
• External Site: W3Schools: HTML Tables

Want to see more good stuff? Subscribe to our channel!