Details of how to send Outlook emails using a template that is populated with Smartsheet data and triggered in Smartsheet. This solution is complex. Some details have been blocked out for privacy. I had to split this into two posts due to the length.
I hope this is helpful and feel free to reach out with any questions or comments.
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
Benefits
- Triggered from Smartsheet via automation that moves or copies row to a specific sheet. Trigger can be simple or complicated.
- Custom HTML email with placeholders filled in with Smartsheet data.
- Email sent from your organization email address.
- Can send same email to multiple recipients with different recipient names and same content.
- Can send different emails to different groups of recipients.
- Clear audit trail in Outlook.
Requirements
- Smartsheet: Provides data and triggers PowerAutomate.
- PowerAutomate: Automation of email generation.
- SharePoint: Contains email html template and SharePoint list for tracking sent emails.
- Outlook: Sends email.
Data map
Power Automate flow overview
Trigger is the Power Automate trigger. The entire process is triggered with the Smartsheet row move or copy automation that is shown above.
Power Automate flow details
The details for every step above.
When a new row is created
Flow trigger that monitors a Smartsheet sheet for a new row and gets the details from that row.
- “Sheet Id”: Select using dropdown menu of Smartsheet Workspaces and sheets.
- “Columns”: Type in the column delimited exact names of the columns to transfer from Smartsheet to Power Automate.
- “Connected to Smartsheet”: The Smartsheet account used to access the Smartsheet data. Use an administration account not connected to a specific person so the connection is stable.
Initialize variable – Email recipient column ID
Creates variable that stores the Smartsheet column ID that contains the email recipient.
- “Name”: Variable name used in the rest of the flow.
- “Value”: The Smartsheet column ID of the column that contains the email recipient.
Initialize variable – Recipient name column ID
Creates variable that stores the Smartsheet column ID that contains the email recipient.
- “Name”: Variable name used in the rest of the flow.
- “Value”: The Smartsheet column ID of the column that contains the name of the recipient.
Initialize variable – Email cc column ID
Creates variable that stores the Smartsheet column ID that contains any carbon copy (cc) emails.
- “Name”: Variable name used in the rest of the flow.
- “Value”: The Smartsheet column ID of the column that contains the email of any CC recipients
Initialize variable – Date column IDs
Creates variable that stores the Smartsheet column IDs of date columns.
- “Name”: Variable name used in the rest of the flow.
- “Value”: Array containing the Smartsheet column IDs for the dates. Must have this exact format.
[
Column ID(s) for date columns
]
Initialize variable – Email subject column ID
Creates variable that stores the Smartsheet column ID that contains the email subject. It is easier to calculate the “email subject” in Smartsheet.
- “Name”: Variable name used in the rest of the flow.
- “Value”: The Smartsheet column ID of the column that contains the subject of the email.
Initialize variable – Rows with one email
Creates variable for Rows with a single email.
- “Name”: Variable name used in the rest of the flow.
Initialize variable – Placeholder Map Array
Creates variable that maps the Smartsheet column IDs containing specific data to the specific text of the placeholders in the Outlook template.
- “Name”: Variable name used in the rest of the flow.
- Value: Pairing that maps the Smartsheet column ID containing the data with the exact placeholder name from the Outlook template. The value must have this exact format
[
{
"columnId": "your column ID",
"placeholder": "{{Placeholder 1}}"
},
{
"columnId": "your column ID",
"placeholder": "{{Name}}"
},
{
"columnId": "your column ID",
"placeholder": "{{Date}}"
},
{
"columnId": "your column ID",
"placeholder": "{{first name}}"
},
{
"columnId": "your column ID",
"placeholder": "{{email}}"
},
{
"columnId": "your column ID",
"placeholder": "{{Coordinator}}"
},
{
"columnId": "your column ID",
"placeholder": "{{Coordinator name}}"
}
]
Get file content - HTML email template
Retrieves the email template from SharePoint.
- Site Address: SharePoint site containing the template. Select using the drop down.
- File Identifier: The template file. Select using the drop down.
- “Connected to Smartsheet”: The Smartsheet account used to access the Smartsheet data. Use an administration account not connected to a specific person so the connection is stable.
Initialize variable – Email HTML
Create a variable containing the email as HTML.
- “Name”: Variable name used in the rest of the flow.
- Value
Expression | Dynamic Content |
|---|
body('Get_file_content_-_HTML_email_template') | Get file content – HTML email template > File Content |
Select – Trigger ColumnIds
Creates an array of the ColumnIds from the trigger.
Expression | Dynamic Content |
|---|
triggerOutputs()?['body/cells'] | When a new row is created > Cells |
Expression | Dynamic Content |
|---|
item()?['columnId'] | When a new row is created > Cells Column ID |
Select – Prep Columns IDs for copy
Creates an array of JSON texts that combines the the ColumnIds with the word “placeholder”. Later PowerAutomate will replace the word “placeholder” with the correct placeholder text.
Expression | Dynamic Content |
|---|
body('Select_-_Trigger_ColumnIDs') | Select – Trigger ColumnIDs > Output |
Expression | Dynamic Content |
|---|
concat('{"columnId": "', string(item()), '", "placeholder": ""}') | N/A |
Compose – Columns IDs for Placeholder map
Creates an array of JSON objects that combines the the ColumnIds with the word “placeholder”. It converts the array of texts from “Select – Prep Columns IDs for copy” in to and array of objects. Later PowerAutomate will replace the word “placeholder” with the correct placeholder text.
Expression |
|---|
concat( '[', decodeUriComponent('%0D%0A'), ' ', join( body('Select_-_Prep_Columns_IDs_for_copy'), concat(',', decodeUriComponent('%0D%0A'), ' ') ), decodeUriComponent('%0D%0A'), ']' ) |
Select – Trigger Values
Gets a JSON object from the trigger that consists of a single property which is an array of strings. Each string is a corresponding column value from the trigger.
Expression | Dynamic Content |
|---|
triggerOutputs()?['body']?['cells'] | N/A |
Expression | Dynamic Content |
|---|
item()?['value'] | N/A |
Filter array - Isolate email data from trigger
Filters out the emails portion of the trigger array. Output contains “columnID”, “value”, and “displayValue”.
Expression | Dynamic Content |
|---|
triggerOutputs()?['body/cells'] | When a new row is created > Cells |
| Expression | Dynamic Content |
|---|
Left | item()?['columnId'] | When a new row is created > Cells Column Id |
Middle | Is equal to | N/A |
Right | variables('EmailRecipientColumnID') | Variables > EmailRecipientColumnID |
Compose - Convert Email data to object
Creates a single JSON object of the emails from “Filter array - Isolate email data from trigger” step.
Expression |
|---|
first(body('Filter_array_-_Isolate_email_data_from_trigger')) |
Compose - Extract emails as array
Creates an array of the emails “Compose - Convert Email data to object” with one email as each element.
Expression |
|---|
split(outputs('Compose_-_Convert_Email_data_to_object')?['value'],',') |
Filter array - Non empty emails
Filters out non blank emails and emails that contain “@” as an array from the “Compose - Extract emails as array” step.
Expression | Dynamic Content |
|---|
outputs('Compose_-_Extract_emails_as_array') | Compose - Extract emails as array > Outputs |
| Expression | Dynamic Content |
|---|
Left | item() | N/A |
Middle | Contains | N/A |
Right | @ | N/A |
Apply to each - Email
Starts an “apply to each” loop where the item for each loop is the array of emails from “Filter array - Non empty emails”
Expression | Dynamic Content |
|---|
body('Filter_array_-_Non_empty_emails') | Filter array - Non empty emails > Body |
Email: Compose - Instructor Email from Row
Extracts the email as a string from the loop item.
Expression | Dynamic Content |
|---|
items( 'Apply_to_each _-_ Email') | Apply to each - Email > Current item |
Email: Compose - Instructor first name proper
Extracts the name from the first part of the email string and capitalizes the first letters.
Expression | expression continued |
|---|
if( empty(outputs('Compose_-_Instructor_Email_from_Row')), '', concat( toUpper( substring( first( split( replace( replace( replace( first(split(toLower(outputs('Compose_-_Instructor_Email_from_Row')), '@')), '.',' ' ), '_',' ' ), '-',' ' ), ' ' ) ), 0, 1 ) ), toLower( substring( first( split( replace( replace( replace( first(split(toLower(outputs('Compose_-_Instructor_Email_from_Row')), '@')), | '.',' ' ), '_',' ' ), '-',' ' ), ' ' ) ), 1, sub( length( first( split( replace( replace( replace( first(split(toLower(outputs('Compose_-_Instructor_Email_from_Row')), '@')), '.',' ' ), '_',' ' ), '-',' ' ), ' ' ) ) ), 1 ) ) ) ) ) |
Email: Select - Row data with single email
Creates an object that is an array of values from the triggering row and replaces:
- Recipient email with the email from this loop
- Recipient name with the name from “Compose - Instructor first name proper”
- Formats the date as MMM d, yyyy
- Keeps the other values unchanged
Using the size of the array from triggering email row values in “Select – Trigger Values”, it creates a object with a body that is an array of values.
Expression |
|---|
range(0, length(body('Select_-_Trigger_Values'))) |
Expression |
|---|
if( equals(variables('EmailRecipientColumnID'), body('Select_-_Trigger_ColumnIDs')[item()]), items('Apply_to_each_-_Email'), if( equals(variables('RecipientNameColumnID'), body('Select_-_Trigger_ColumnIDs')[item()]), outputs('Compose_-_Instructor_first_name_proper'), if( contains(variables('DateColumnIDs'), body('Select_-_Trigger_ColumnIDs')[item()]), formatDateTime( body('Select_-_Trigger_Values')[item()], 'MMM d, yyyy' ), body('Select_-_Trigger_Values')[item()] ) ) ) |
Email: Select – Pair Column ID and Column Value
Creates an array of objects consisting of the Smartsheet ColumnIDs and the adjusted values from “Select - Row data with single email” step. Needed to have an array of ColumnIDs and values that will be used when replacing placeholders in the template for each email.
Expression |
|---|
range(0, length(body('Select_-_Row_data_with_single_email'))) |
Left | Right Expression |
|---|
ColumnID | body('Select_-_Trigger_ColumnIDs')[item()] |
Value | body('Select_-_Row_data_with_single_email')[item()] |
Email: Select - Strings for Object
Prepares the key value pairs for the future JSON object key value pairs. Needed to have an array of ColumnIDs and values that will be used when replacing placeholders in the template for each email.
Expression | Dynamic Content |
|---|
body('Select_-_Pair_Column_ID_and_Column_Value') | Select - Pair Column ID and Column Value > Output |
Expression |
|---|
concat('"', string(item()?['ColumnID']), '":"', string(item()?['Value']), '"') |
Email: Compose – Object for Array
Creates a JSON object of key value pairs. Needed to have an array of ColumnIDs and values that will be used when replacing placeholders in the template for each email.
Expression |
|---|
json(concat('{', join(body('Select_-_Strings_for_Object'), ','), '}')) |
Email: Append to array variable
Allows “column ID : email data” pairs to be usable outside of this loop. Adds the JSON object of key value pairs to the array of sets of key value pairs. Each element of this array is a set of key value pairs. The final array will be used in a loop to replace placeholders in the template for each email.
Expression | Dynamic Content |
|---|
outputs('Compose_-_Object_for_Array') | Compose – Object for Array > Outputs |
Compose – Display Array RowsWithOneEmail
Shows the email data contained in the “RowsWithOneEmail” array. This is for easy review of the data.
Expression | Dynamic Content |
|---|
variables('RowsWithOneEmail') | Variables > RowsWithOneEmail |
Continues in 2 of 2: How to send Outlook emails using a template populated with Smartsheet data and triggering from Smartsheet
I hope this is helpful and feel free to reach out with any questions or comments.
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
