“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‘] as explained here in more detail, 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/
Hi Tom,
I have gotten pretty far along this flow but keep hitting road blocks. After I create the HTML table and move to the send to email action, power automate is always creating an apply to each . Then I get the following error.
Flow save failed with code
‘InvalidTemplate’ and message ‘The template validation failed: ‘The inputs of template action ‘Send_an_email_(V2)’ at line ‘1 and column ‘6496’ is invalid. Action ‘Apply_to_each_3’ must be a parent ‘foreach’ scope of action ‘Send_an_email_(V2)’ to be referenced by ‘repeatItems’ or ‘items’ functions.’.’.
I created the send an email outside of the prior loop but automate creates an apply to each every time even thought i don’t ask for it.
Hello Paolo,
the ‘Apply to each’ is created because of some dynamic content you use in the ‘Send an email’ action. Take a look on the dynamic contents you use in the email, try to remove all of them and add them one by one. Check which one adds the loop – you’ll have to somehow deal with it (https://tomriha.com/stop-power-automate-flow-from-adding-apply-to-each-automatically/).
Dear Tom,
many thanks for your tutorial, unfortunately I struggle on the step with Filter Array, the output is everytime empty…do you have any ideas?
See you
Kind REgards
Valentin
Hello Valentin,
the most common problem I see with this solution is that users don’t switch the ‘Select’ action to ‘text only’ mode, I’d check that.
Hello,
Thank you for this detailed guide. That’s awesome 🤩 I have a problem:
I get the grouping in the html table down, everything else works as it should.
But it sends various emails with the same content. Any idea?
Hello Lars,
if it sends the same content then I’d review the steps where you build the email content, if it’s implemented correctly.
Hello Tom,
I am encountering the same problem as Ela and Jessica. I did switch the ‘Select’ action to the value only mode but it still doesn’t work.
Error message:
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 ‘{“@odata.type”:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,”Id”:2,”Value”:”XXXX@email.com”}’ is not convertible to type/format ‘String/email’.
Link to email ‘To’ input:
https://imgur.com/G29Daib
Link to flow:
https://imgur.com/6VZ4PWa
Kind regards
Anders
Hello Anders,
you must take only the ‘Email’ dynamic content from the user in the ‘Select’ action, e.g. not ‘User’ but ‘User Email’ or ‘User Value’ depending on the column type.
Thanks for this solution.
Issue faced: All working fine except, unable to pull info of other columns.
In the ‘Create HTML action’ after the ‘Filter array’ below expressions are not returning data.
For ex: Title info is not pulled in in the Email body.
item()?[‘Title‘]
Hello Veer,
I published an article on this topic: https://tomriha.com/access-sharepoint-columns-after-filter-array-in-power-automate/
Hi Tom,
Can we add a 90 days rule? Email will only be sent if the last email was send more than 90 days ago. Thanks.
Hello Daniel,
you’d need to store the information somewhere else, e.g. directly in the items or in a separate SharePoint list where you’d store the date of the last email. Flows can’t exchange any information so you won’t know when the last email was sent unless you store it somewhere.
Thank you for this solution. I’ve used this to create an email with a somewhat formatted table that goes out to each unique user in a certain column, as intended. Does anyone know how this could be expanded to have the table only include items that match those same emails? So the table has, say, 200 items assigned to 10 users throughout. Right now each of the 10 users gets an email with the full table. I’d like each user to get an email with the table only showing items assigned to them. I’ve had a few ideas but nothing is working yet.
Hello Jeff,
you must use the output of ‘Filter array’ in the ‘Create HTML table’ action, that’s where only the filtered items are.