“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.
There’s also a blog post on working with multiple people picker column or more than 1 people picker column.
Dear Tom,
Thank you very much for this post. It helped me solve a problem I have struggled with for hours today. Your clear and easy-to-replicate steps helped a great deal. Thanks!
Note: For reasons still unknown to me, I could not get ‘Current Item’ to work, both as an array filter and as the email recipient.
What worked for me was using this expression “items(‘Apply_to_each’)[‘Email’]” to tease out the email address inside the for each loop.
My flow works now. Many thanks to you!
Thank you for the genius method around this. This works so well, however my e-mail column has multiple e-mails in it. Is there a way to split the e-mails based on a delimiter, or some other method in order to have a single e-mail per person for all of the items in which they are included as an owner? I hope that question makes sense.
Hello Tyler,
once you’ve got multiple people per item it gets a lot more complicated, I’ll have to think about that and probably turn it into a blog post, it’s too complex to explain it in the comments.
Hello Tyler,
I hope this article explains the solution with multiple people picker columns.
Thank you.
I have a question please i used this flow but i add the manager of the person who will receive the email in cc . But when i test the flow didnt work correctly the both person receive 3 mail for each for example (3 items shrepoint liste)
Hello Anas,
I don’t know how you get the manager, but you should get him only once, e.g. extract him from the first user’s item using the first(…) expression: https://tomriha.com/how-to-get-a-specific-value-from-a-json-object-in-power-automate/. The manager will be the same for all the user items, so you can just take him from the first one.
Hi,
I built this flow but I receive the following error on the last action “Send an Email”:
The ‘inputs.parameters’ of workflow operation ‘Send_an_email_(V2)’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ’emailMessage/To’ is required to be of type ‘String/email’. The source type/format ‘Object’ of the value ‘{“XXXX@yahoo.com”:””}’ is not convertible to type/format ‘String/email’.
In the ” Compose – get unique users” step, the inputs are:
{
“XXXX@yahoo.com”: “”
},
{
“YYYY@gmail.com”: “”
},
Not sure why the colon is populated after the email address. Any ideas what changes I should make to the email format so that it takes in the last action item?
Email address is being pulled from SP list. I tested on a text field and on an email specific field (Person/group column type) and both give the same error.
Hello Ela,
you probably didn’t switch the ‘Select’ action to the value only mode, as shown on the first screenshots. It should have only 2 fields, the input and the Map field.
Hi! I am having this issue too. I have the Select value and map value entered.
The ‘inputs.parameters’ of workflow operation ‘Send_an_email_(V2)’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ’emailMessage/To’ is required to be of type ‘String/email’. The source type/format ‘Object’ of the value ‘{“”:”xxxxx@gmali.com”}’ is not convertible to type/format ‘String/email’.
The output I am getting from the Select step is :
[
{
“”: “chen_jessica@lilly.com”
},
{
“”: “xxxxxx@gmail.com”
},
{
“”: “xxxxxx@gmail.com”
},
{
“”: “xxxx@gmail.com”
}
]
Hello Jessica,
you probably didn’t switch to the ‘Text input’ mode in the ‘Select’ action.
Yes, that was it!!! Thank you!
Hello!
I followed the instructions step by step to this process and I receive two emails when the flow runs.
In addition, the information from the list is not listed in the email either. Not sure what happened.
Thanks!
Hello Mike,
you’re probably not using the right input in some of the actions, check the flow again if you’re referencing the correct action outputs.
Thank you for this! Exactly what I needed.
I was with you until the very end. I am able to get the flow to only send an email to the unique users but I do not understand how to get all of the data associated with that user in a single table in that email.
Hello Wayne,
you select what data you want to display in the ‘Create HTML table’ action as shown for example in the referenced article: https://tomriha.com/format-email-with-sharepoint-items-in-a-readable-way-in-power-automate/
Thanks for the instructions! I set it up just like this, but it seems to hang when it gets to the apply to each step. Can you suggest any fixes? Thanks!
I figured out what the issue was, but that brings up another question. Is there a way to include a link to each sharepoint list item that is included in the HTML table in the email?
Hey Tom,
Would it be possible to use this approach with a few changes to identify if the email one of the Users within the field (column) is equal to the email of the Last Modified By? If so, do you think I could ask you for some direction on how to proceed?
Thanks in advance.
I tried to recreate this but with an Assigned To field instead of Send To but I guess Im doing something wrong because mine returned the error:
Flow save failed with code ‘InvalidTemplate’ and message ‘The template validation failed: ‘The template action ‘Create_HTML_table’ at line ‘1’ and column ‘3848’ is not valid: “The template language function ‘items’ must have at least one parameter.”.’.’.
Hello Alex,
based on the error message you’re using the items() expression which is used in loop and must reference the loop name, e.g. items(‘Apply_to_each’)?[‘Title’]. In the ‘Create HTML table’ you should use only item() without any reference, e.g. item()?[‘AssignedTo’].
I am able to get unique value for single column, How to approach if we have two people picker columns and we need unique values.
Hello Kumar,
I just published a post to answer your question: https://tomriha.com/get-unique-users-from-various-people-picker-columns-power-automate/