Send one email per user with multiple SharePoint items in Power Automate
“I built a flow in Power Automate to send reminders to users, but it’s sending one email per SharePoint item, how can I group all user’s items into one email?”
Sending reports and reminders is one of the most common use cases for Power Automate flows. Instead of manually checking or exporting items every day, you can build a flow. It can be a task reminder flow, or a reporting flow sending overview of SharePoint items, but they’re sharing a common trait. If there’re multiple items per user, the flow is sending multiple emails. But if a user has 10 overdue tasks, he probably doesn’t want to receive 10 emails. He’d probably prefer to receive only one email with all the tasks in it.
How do you “group” the items together into a single email per user?
Get all the SharePoint items
The first step is to get all the relevant SharePoint items with the ‘Get items’ action. You can use any filter in the action, but you should get all the items for all the emails.
Then, before you start sending the emails, it’s time for the grouping. For this post it’ll be grouping by the email recipient, in the examples below it’ll be a SharePoint single people picker column ‘SendTo’.
Get the unique email addresses
Before you start with any grouping, you must get the values to group by. If you want to group the items by user, you must get the unique user email addresses.
Add the ‘Select’ action to take only the user email addresses from the items (a similar process as when converting them into a string).
Note: the solution above is designed for single people picker column.
The output will be an array with all the emails. But it’s all of them, including duplicates. You don’t want duplicates, duplicate email addresses will lead to duplicate emails. Each email address must be in the array only once.
Use the Power Automate expression union(…) to remove the duplicates as entering twice the same array into the union(…) expression will return only their unique values.
Add a ‘Compose’ action with the union(…) expression and twice the array with email addresses (output from ‘Select’). It’s the same expression already used when creating Planner tasks from a template.
union(body('Select'),body('Select'))
Filter the SharePoint items using the email addresses
Now, when you’ve got the unique email addresses, you can “group” the items together. Loop through all the email addresses from the ‘Compose’ (the union(…)) output, and filter only the items from ‘Get items’ output for the respective users.
Add ‘Apply to each’ with the output from ‘Compose’, and enter ‘Filter array’ inside to filter the items by the email.
The output from ‘Filter array’ will be only the items related to the email address, the user. ‘Create HTML table’ from the items and send it in an email.
Note: the dynamic content won’t be available in the ‘Create HTML action’ after the ‘Filter array’. You can either add the columns manually using the expression item()?[‘column internal name‘], or use ‘Parse JSON’.
Summary
The process to send one email with multiple SharePoint items in Power Automate has three important steps. The first step is to get all the items (a topic of another post). Once you have all the items, you must get the unique values used for grouping. It must be a simple array that will contain only the unique values, otherwise it’d still send multiple emails.
After you’ve create the array, you can loop through it and filter the original items. Instead of looping through the items one by one, you’re looping through the unique values. That way you’ll get the items grouped as needed, and you can send all of them in a single email.