Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

Send one email per user with multiple SharePoint items in Power Automate

Posted on May 26, 2021June 1, 2022 by Tom

“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.

Power Automate send one email with multiple SharePoint items per user

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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

23 thoughts on “Send one email per user with multiple SharePoint items in Power Automate”

  1. Yemi Adesanya says:
    August 15, 2021 at 7:05 pm

    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!

    Reply
  2. Tyler Perez says:
    August 26, 2021 at 5:55 pm

    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.

    Reply
    1. Tom says:
      September 1, 2021 at 7:35 pm

      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.

      Reply
    2. Tom says:
      September 5, 2021 at 5:24 pm

      Hello Tyler,
      I hope this article explains the solution with multiple people picker columns.

      Reply
  3. Anas elayoubi says:
    February 6, 2022 at 3:38 am

    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)

    Reply
    1. Tom says:
      February 7, 2022 at 10:19 pm

      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.

      Reply
  4. Ela says:
    February 7, 2022 at 8:11 pm

    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.

    Reply
    1. Tom says:
      February 7, 2022 at 10:05 pm

      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.

      Reply
      1. Jessica says:
        May 31, 2022 at 7:24 pm

        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”
        }
        ]

        Reply
        1. Tom says:
          June 1, 2022 at 7:34 pm

          Hello Jessica,
          you probably didn’t switch to the ‘Text input’ mode in the ‘Select’ action.

          Reply
  5. Ela says:
    February 8, 2022 at 4:28 am

    Yes, that was it!!! Thank you!

    Reply
  6. Mike says:
    March 2, 2022 at 11:23 pm

    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!

    Reply
    1. Tom says:
      March 5, 2022 at 7:18 pm

      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.

      Reply
  7. Emily says:
    April 12, 2022 at 12:25 am

    Thank you for this! Exactly what I needed.

    Reply
  8. Wayne says:
    April 19, 2022 at 12:27 am

    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.

    Reply
    1. Tom says:
      April 23, 2022 at 8:26 pm

      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/

      Reply
  9. Josef says:
    May 3, 2022 at 2:16 pm

    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!

    Reply
    1. Josef says:
      May 3, 2022 at 4:42 pm

      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?

      Reply
  10. Alberto says:
    May 12, 2022 at 2:00 am

    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.

    Reply
  11. Alex says:
    May 13, 2022 at 9:56 pm

    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.”.’.’.

    Reply
    1. Tom says:
      May 17, 2022 at 8:32 am

      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’].

      Reply
  12. KUMAR says:
    May 27, 2022 at 4:31 pm

    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.

    Reply
    1. Tom says:
      June 1, 2022 at 7:04 pm

      Hello Kumar,
      I just published a post to answer your question: https://tomriha.com/get-unique-users-from-various-people-picker-columns-power-automate/

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme