Continuation of 1 of 2 : How to send Outlook emails using a template populated with Smartsheet data and triggering from Smartsheet
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 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
——
Apply to each - RowsWithOneEmail
Loops through each set of email data to replace placholders in the email template with the data for that email then sends the email and files the email.
- Select and output from previous steps
Expression | Dynamic Content |
|---|
variables('RowsWithOneEmail') | Variables > RowsWithOneEmail |
SendEmail: Select – Placeholder to Value
Combines the PlaceholderMapArray with the loop email data based on the ColumnID which is in both arrays. The result is an array of the placeholder text and the data for the email.
Array | First value | Second value |
|---|
PlaceholderMap Array | Column ID | Placeholder text |
RowsWithOneEmail | ColumnID | Email data |
Select – Placeholder to Value | Placeholder text | Email data |
Expression | Dynamic Content |
|---|
variables('PlaceholderMapArray') | Variables > PlaceholderMapArray |
Left | Right Expression |
|---|
placeholder | item()?['placeholder'] |
value | items('Apply_to_each_-_RowsWithOneEmail')?[item()?['columnId']] |
SendEmail: Apply to each – Placeholder value pair
Loops through the “placeholder” – “email data” pairs in “Select – Placeholder to Value” and replaces the template HTML placeholder texts with the email data.
- Select and output from previous steps
Expression | Dynamic Content |
|---|
body('Select_-_Placeholder_to_Value') | Variables > Select – Placeholder to Value |
Placeholder: Compose - In email HTML replace placeholders with values
Replaces the placeholder text in the email HTML template with the email data values.
Expression |
|---|
replace(variables('EmailHtml'), item()?['placeholder'], coalesce(item()?['value'], '')) |
Placeholder: Set variable - In email HTML replace placeholders with values
Makes the updated HTML email available outside of this loop inside a variable called “EmailHtml”.
Expression | Dynamic Content |
|---|
outputs('Compose_-_In_email_HTML_replace_placeholders_with_values') | Variables > Compose - In email HTML replace placeholders with values |
SendEmail: Compose - Email recipient
Extracts the email recipient from the email data of this loop using the column ID in the variable “EmailRecipientColumnID”.
Expression |
|---|
items('Apply_to_each_-_RowsWithOneEmail')?[string(variables('EmailRecipientColumnID'))] |
SendEmail: Compose – Email cc
Extracts the email carbon copy (cc) recipient from the email data of this loop using the column ID in the variable “EmailCCcolumnID”.
Expression |
|---|
items('Apply_to_each_-_RowsWithOneEmail')?[string(variables('EmailCCcolumnID'))] |
SendEmail: Compose – Email Subject
Extracts the email subject from the email data of this loop using the column ID in the variable “EmailSubjectColumnID”.
Expression |
|---|
items('Apply_to_each_-_RowsWithOneEmail')?[string(variables('EmailSubjectColumnID'))] |
SendEmail: Compose - Display updated email HTML
Displays the email HTML for this email for easy review. The variable EmailHtml is the output of the loop “Apply to each - Placeholder value pair”.
Expression | Dynamic Content |
|---|
variables('EmailHtml') | Variables > EmailHtml |
SendEmail: Send an email from a shared mailbox (V2)
Sends the custom email using the variables defined earlier. Only fields with special values are in the table below.
Field | Expression | Dynamic Content |
|---|
Original Mailbox Address - From | Mailbox that sends the email. | N/A |
To | outputs('Compose_-_Email_recipient') | Variables > Compose – Email recipient |
Subject | outputs('Compose_-_Email_Subject') | Variables > Compose – Email Subject |
Body | outputs('Compose_-_Display_updated_email_HTML') | Variables > Compose – Display updated email HTML |
CC | outputs('Compose_-_Email_cc') | Variables > Compose – Email cc |
Connected to Office 365 Outlook | Connect to the mailbox that sends the email. | N/A |
SendEmail: Delay - Allow email to send
A delay of 10 seconds to allow the email send to process before filing the sent email.
Field | Value |
|---|
Count | 10 |
Unit | Second |
SendEmail: Get emails (V3) - Get sent email
Gets the email that was just sent so it can be filed in a specific folder in the “Sent” mailbox.
Field | Expression | Dynamic Content |
|---|
To | outputs('Compose_-_Email_recipient') | Variables > Compose – Email recipient |
Subject Filter | outputs('Compose_-_Email_Subject') | Variables > Compose – Email Subject |
Folder | The folder where your sent emails go. | N/A |
Original mailbox address | Mailbox that sent the email. | N/A |
Top Pulls only the first email found. | 1 | N/A |
Connected to Office 365 Outlook | Connect to the mailbox that sent the email. | N/A |
SendEmail: For each - Sent email
Loop that moves the sent email into the Sent mailbox subfolder and records the sent email on a SharePoint list.
- Select an output from previous steps
FileEmail: Move email (V2) - To sub folder in Sent
Moves the email from the loop to the sub folder in the Sent mailbox.
Field | Expression | Dynamic Content |
|---|
Message Id | items('For_each_-_Sent_email')?['id'] | Get emails (V3) – Get sent email > Message ID |
Folder | Mailbox folder where email will be filed. | Variables > Compose – Email Subject |
Original mailbox address | Mailbox that sent the email. | N/A |
Connected to Office 365 Outlook | Connect to the mailbox that sent the email. | N/A |
FileEmail: Create item – Record on Talent cancellation list
Records details from email on a SharePoint list of easy review and tracking. The list needs to be created before this action. The fields from the list are the fields shown after “Limit Columns by View”. Only fields with values are shown.
Field | Expression | Dynamic Content |
|---|
Site Address | SharePoint site where list is located. | N/A |
List Name | Name of list | N/A |
Original mailbox address | Mailbox that sent the email. | N/A |
Sent date and time | outputs('Move_email_(V2)_-_To_sub_folder_in_Sent')?['body/receivedDateTime'] | Move email (V2) – To sub folder in Sent > Received Time |
Recipient email | outputs('Move_email_(V2)_-_To_sub_folder_in_Sent')?['body/toRecipients'] | Move email (V2) – To sub folder in Sent > Received Time |
Subject | outputs('Move_email_(V2)_-_To_sub_folder_in_Sent')?['body/subject'] | Move email (V2) – To sub folder in Sent > To |
Connected to SharePoint | Connect SharePoint account where list is located. | N/A |
Feel free to reach out with any questions.
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
