How to handle Users and SharePoint Group Members when sending emails with Power Automate

Alright, it wasn’t easy to find a good title for this blog post 😅 BUT, I’ll tell you what we’re going to do today: Sending reminder emails to an Assigned To field in SharePoint which can contain (single select) Users OR SharePoint groups.

Full scenario

In our scenario, we’d like to send a SINGLE email to users or SharePoint Group Members in an Assigned To column. This email will be in the shape an HTML table BUT the Assigned To value should be the user (if assigned directly) or the SharePoint Group name (if the user is part of that group). So let’s go!

Variables

Let’s get the basics out of the way. We’ll need to initialise 2 variables:

• varUserEmail: To store the email for individual users
• varMemberEmail: To store the email for the SharePoint Group Members

Both will be of type Array.

Next, we’ll get our items from SharePoint using the Get Items action (feel free to add a Filter query if you don’t want to be reminded all the time when saving the flow!)

In my SharePoint list, I have 3 items for demo purposes, and it looks like that:

Here, test1 and test3 are assigned to a user directly, while test2 is assigned to a SharePoint Group. Myself and John Smith are members of that group 😉

Let run the flow now, to observe a couple of things. The below is an extract of the outputs of Get Items:

"@odata.etag": "\"1\"",
        "ItemInternalId": "1",
        "ID": 1,
        "Title": "test1",
        "AssignedTo": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "i:0#.f|membership|vero@myDomain.com",
          "DisplayName": "Veronique Lengelle",
          "Email": "vero@myDomain.com",
          "Picture": "",
          "Department": null,
          "JobTitle": null
        }

For ID 1 which a user is assigned directly, we’ve got the email value. However, when it comes to a SharePoint Group being assigned, the members (and therefore their emails) are not provided!

"@odata.etag": "\"1\"",
        "ItemInternalId": "2",
        "ID": 2,
        "Title": "test2",
        "AssignedTo": {
          "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
          "Claims": "Group1Test",
          "DisplayName": "Group1Test",
          "Email": null,
          "Picture": null,
          "Department": null,
          "JobTitle": null
        },

Get the SharePoint Group Members

Right after the Get Items action, we’re going to use an Apply to each to loop through each request. And we’ll add a condition to check if the Assigned To value is a user or a SharePoint Group.

Here are the inputs:

• The input for the Apply to each will be the value of Get Items
• Condition will be if the “Assigned To Email” is not null (aka user assigned directly!)
  • If not null (true branching)–> Append to varUserEmail
  • If null (false branching):
    • Get group details (with HTTP request to SharePoint action)
    • Get the members (with HTTP request to SharePoint action)
    • For each member, append to varMemberEmail variable

The next step will be to make sure we’ve got only unique users to send a single email 🙂

Let’s use a Compose action with the union() function:

union(variables('varUserEmail'),variables('varMemberEmail'))

Compare users and send emails

Looping through each unique users using a Filter array action requires a lengthy formula. It allows to compare if some members are “unique users”, and therefore send a single email whether they’re assigned directly or via membership. This is an important step! 😉

Here’s the formula for the Filter array action (make sure to adapt to your naming):

@or(and(not(empty(item()?['AssignedTo']?['Email'])),equals(item()?['AssignedTo']?['Email'], items('ATE_unique_users'))),and(empty(item()?['AssignedTo']?['Email']),contains(variables('varMemberEmail'), items('ATE_unique_users'))))

The rest will be about including the data you wish to have in the HTML table, formatting your HTML table, and finally send the email!

Converted HTML action (compose) is used to remove any special characters that could cause chaos! The formula is:

replace(replace(replace(replace(replace(body('Create_HTML_table'), '&lt;', '<'), '&gt;', '>'), '&amp;', '&'), '&#39;', '"'),'&quot;', '"')

When sending the email, the first output is from my Style HTML Table action, and the second output is the Converted HTML action.

Results

A successful flow run will give you the below. Each user will receive ONLY the request(s) they’re assigned to directly or via group membership.

Thanks for reading! 🙂