Have you tried emailing data from SharePoint lists to people? Have you ever found that you end up with one email for each item.
Getting data from SharePoint
I will start with collecting data from a SharePoint list. The data collected will then be used later in all of my emailing data examples.
All of the examples used within this post could of course also simply collect data from a SQL database or any other data source. If it is items or records that you collect you will always run into the similar issues as mentioned in this post.
Now that we have some data to play with it is time to look at the 3 options for emailing the data to people.
- Sending an email for each item
- Sending an HTML table
- Sending the data in any format
1 – Sending an email for each item
Most likely this is not the way you would like to send your data but there may be situations where you do want to send an email for each item in your SharePoint list hence I thought I better include it in this post.
I’ve had so many people asking me about removing that Apply to each action that is added to the flow automatically as soon as you include some dynamic data that is returned by the get items. Power Automate however recognizes that an array of items is returned by the Get Items action and therefore an apply to each will be needed.
2 – Send an email with a table
The second option is to send an email with an HTML table included. The Create HTML table action is configured within minutes and this will very often work very well when you want to email data to people.
This now results in a nice looking table that will probably work for most use cases.
But what if you would like to format the table differently?
3 – Emailing data in any format
In this 3rd approach I’m going to make use of the method that I’ve described in previous posts.
With an apply to each I’m building up each line of data using the following expression in a compose. You can make this expression as complicated as you like. In this example I’ve included some text formatting and some references to my data.
concat('<div><strong>',items('Apply_to_each')?['Title'],'</strong> - Last Updated: ', items('Apply_to_each')?['Connector_x0020_Modified_x0020_D'], '</div>')
Now we can collect all the content using another compose outside the apply to each and join all the lines together using the join function.
The above can then be used in an Send an email action.
And the email generated will now look like this:
Ok, I didn’t make the formatting of the data too complicated. An important note is that formatting in emails is something that you will need to be careful with as many email clients use old versions of HTML and hence the functionality available will be limited.
Even though the HTML might be slightly limited, there is of course more that you can do than just the basic table as shown in option 2 in this post. Just make sure that you test the email formats that you are using.
When would you send emails to people using any of the 3 approaches above? Some examples:
- After the approval of documents you want to send a list of all approval results to the person requesting the approval.
- You have a list tasks and you want to send a daily status update of all tasks to people
- You want to create an overview of all the Teams created by your staff members in the last week.
There are so many use cases where you might have to convert a list of records into a single piece of text.