Recently, a client requested a daily digest email be sent to their support staff with a list of their active tickets, along with a link to the particular record. This blog will take you step-by-step through the usefulness of this Power Automate Flow and how I created it.
What's great about this Flow is, even though in my example I am triggering it as a recurrence with CDS as a data source, you can use any type of trigger you may need or any data source that applies. The important—and more difficult—part is including the links directly to records in an email table.
You might think this would be a simple request, but it turns out that you need to do a little bit of thinking to make this work. My first instinct was to try to store the returned values for each user as an Array Variable, but when you use the "Create HTML Table" action based upon the array, the link reference does not get recognized as HTML and you get an email that looks something like below:
How to Create this Flow (or something similar)
- Create the Flow with a Trigger of Recurrence. Set the recurrence pattern to whatever you would like. We are setting a daily recurrence in this example.
- Initialize an integer variable for the count of records returned. We don't want to send users emails with blank tables.
- Initialize a string variable for storing each new row of the table.
- List All Active Users
- Apply to each user
- List tickets
- Apply to each ticket
- Increment count variable
- Condition: If count is greater than 0
- Get account (to display the client's name)
- Append to string variable for a new table row. This is how we are building the HTML for the email.
- Condition: Count is greater than 0
- Send an email from a Shared Mailbox. Formatting the rest of our HTML will happen in the body of this email action. Insert whatever table formatting you may want into the <style> tag. Keep in mind, in my screenshot the web version of outlook strips some of the formatting.
- Set count to 0
- Set ticket table string to null
With any luck, your results should appear like this: