Headshot of Gorav Seth next to text that says, "Automate This! Consolidate Emails Using Flow."

Automate This! — Consolidate Emails Using Flow

By

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!

Email with list of records generated by Flow.

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.

Overview of the flow with all elements.

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.

Scheduled flow configuration.

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

Get Records element with filters.

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

Assignment element configuration.

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

Outcome 1—checks day of month.

Outcome 2—checks if owner has any overdue opportunities.

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

Collection Sort element configuration.

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.

Overview of the elements of the flow that handle the HTML table generation.

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

Configuration for the text variable that stores the HTML table.

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.

Text template configuration set to View as Plain Text.

 

  • 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

Configuration of number variable used to store the row number.

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

Loop element configuration.

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

Configuration for the Formula field that sets a background color based on vaRowNumber.

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.

Text template with merge fields from the loop variable (Opportunity).

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

 Configuration for Assignment element that adds HTML for table row to text variable.

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!

Configuration for Assignment element that closes out the table.

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

Formula field to set month and year for email subject.

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}

Text template configuration for email subject.

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

Send Email action configuration.

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

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

SUBSCRIBE TODAY
Increase productivity and create starter flows fast with Einstein for Flow

Increase Productivity by Creating Starter Flows Fast Using Einstein for Flow | Automate This!

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 Eduardo Guzman enhanced productivity and […]

READ MORE
How I Created a Solution to Test Scheduled Flows Easier During UAT

How I Created a Solution to Test Scheduled Flows Easier During UAT | Automate This!

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 Nancy Brown created a […]

READ MORE