Flow: Send Email to SharePoint Group Members

I worked on a project recently where the customer wanted to send an email via Flow to all the members of a given SharePoint Security Group.  Normally I would recommend that a distribution list be made and used instead but the client didn’t have the access to create one so I added some logic in Flow to make it happen.

Setting up Variables

To get the users from a SharePoint Group we first need to initialize three different variables.  We need one string variable called “Group” which will hold the email addresses of the Group Members.  We’ll need an integer variable called “i” which will serve as a counter as we For Each through the results of the SharePoint Group Member lookup.  Lastly, we’ll need an array variable called Users which will contain the array of users from our query.  See screenshot below for the configuration:

Getting the SharePoint Group Members

Now that we have our variables configured, we need to call SharePoint and return the members of the Group in question.  To do this, we’ll use the “Send an HTTP Request to SharePoint” Action in Flow.  You will need to pass in the URL of the site which contains the Group.  The Method for this call will be a a GET because we are wanting to call the Rest API and have it get information for us.  The URI is the endpoint for the SharePoint Rest API.  The endpoint we need to return users from a group is as follows:

_api/Web/SiteGroups/GetByID(<Put In ID of SharePoint Group Here>)/users

Finding the Group ID

You can get the ID of your SharePoint Group by going into the Group and looking in the URL for “MembershipGroupId=”.  The number that follows is the Group ID and what you will use in the URI.

Parsing the Results

Now that we have the results of our HTTP request, we need to loop through those results, also called “Parsing” to get them in a format which Flow can use.  To do this, we will insert a Parse JSON Action.  The Content Property of the Parse JSON action will be the Body of the SharePoint HTTP request. 

In the Schema box, insert the following JSON object:

{ "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "d": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "results": { "type": "object", "properties": { "type": { "type": "string" }, "items": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "__metadata": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "id": { "type": "object", "properties": { "type": { "type": "string" } } }, "uri": { "type": "object", "properties": { "type": { "type": "string" } } }, "type": { "type": "object", "properties": { "type": { "type": "string" } } } } } } }, "Alerts": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "__deferred": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "uri": { "type": "object", "properties": { "type": { "type": "string" } } } } } } } } } } }, "Groups": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "__deferred": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "uri": { "type": "object", "properties": { "type": { "type": "string" } } } } } } } } } } }, "Id": { "type": "object", "properties": { "type": { "type": "string" } } }, "IsHiddenInUI": { "type": "object", "properties": { "type": { "type": "string" } } }, "LoginName": { "type": "object", "properties": { "type": { "type": "string" } } }, "Title": { "type": "object", "properties": { "type": { "type": "string" } } }, "PrincipalType": { "type": "object", "properties": { "type": { "type": "string" } } }, "Email": { "type": "object", "properties": { "type": { "type": "string" } } }, "IsEmailAuthenticationGuestUser": { "type": "object", "properties": { "type": { "type": "string" } } }, "IsShareByEmailGuestUser": { "type": "object", "properties": { "type": { "type": "string" } } }, "IsSiteAdmin": { "type": "object", "properties": { "type": { "type": "string" } } }, "UserId": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "__metadata": { "type": "object", "properties": { "type": { "type": "string" }, "properties": { "type": "object", "properties": { "type": { "type": "object", "properties": { "type": { "type": "string" } } } } } } }, "NameId": { "type": "object", "properties": { "type": { "type": "string" } } }, "NameIdIssuer": { "type": "object", "properties": { "type": { "type": "string" } } } } } } } } }, "required": { "type": "array", "items": { "type": "string" } } } } } } } } } } } } }}

Setting the User Object

Now we need to set the Users array variable that will hold the results of our Parse JSON action.  Insert a “Set Variable” Action and select the Users variable we defined earlier.  We will set the value to the results element of our parse JSON via the expression below:

body(“Parse_JSON”)[“d”][“results”]

Looping through the Users

So we have an Array of all the users in the SharePoint group.  Next, we need to add an “Apply to Each” so that we can loop through each result and extract the email address.  The Group membership object returns all kinds of information we don’t care about like Name, Department, etc.  So we need to loop through and just get the email addresses.  

Inside our Apply to each we need to add an “Append to String Variable” Action.  We want to write to our “Group” String Variable we initialized in the previous step.  The value will be the Email from our Users Array.  To get that we need to use the following Expression:

concat(variables(‘Users’)[variables(‘i’)][‘Email’],’;’)

After we set the email, we need to add a “Increment Variable” Action to set our “i” counter in the previous step to 1.  

The final step is to insert the Send an Email Action with the “To” field being the Group Variable.