Email a SharePoint group from a Flow, Part 1

One of the greatest features of Flow is the ability to send emails. But there isn’t a native way to send emails to SharePoint groups. Anyone who’s done substantial work with workflows knows that emailing individual users is fraught with issues. People leave companies or change roles, and if a workflow explicitly names an individual as an email recipient, any personnel change will break existing processes, necessitating rework. The best practice in this situation is to email a group, and manage group membership as needed.

Flow doesn’t allow an action to do this, and based on this Tech Community conversation lots of people are asking for it. Recently Microsoft provided the ability to issue raw REST requests against SharePoint from a Flow, and indeed we can use this pattern to fetch users from a group. Once we have that list of users we can them email them using Flow.

Creating a reusable Flow

I’m going to do something a little different with this Flow. I have a number of situations where I need to email SharePoint groups, and I don’t want to have to do this work every time the requirement comes up. What I’m going to do instead is create a standalone Flow that only emails a group, that I can call from other Flows.

One way to do this is to author the Flow using an HTTP trigger. That is, the Flow will listen on an HTTP endpoint, and be invoked whenever a request is made to it. The advantage of this trigger is that it can be invoked from pretty much anywhere: another Flow, an Azure Function, a console app, a mobile app, Postman, anything that can issue web requests can take advantage of this service.

Because we want this Flow to be flexible and configurable, able to email any group on any site in our tenant, we’re going to pass in the name of the group and the site URL to the Flow via JSON. Here’s how the Flow trigger looks so far:

alt text

Use this sample payload to generate the JSON schema:

{
"siteUrl": "foo",
"groupName": "foo"
}

Setting up the REST Request

Like I mentioned earlier, Flow gives us the ability to issue REST requests against SharePoint. If you’ve never worked with REST or with web services in general, it might seem a little daunting. But in Flow, the most difficult part of the process, authentication, is already handled for you, so all you have to do is craft the requests and parse the responses. Flows run under the security context of the user who authored the Flow, and the authentication headers will be automatically provided by Flow. (Note – there are some security implications to consider when authoring Flows – I’ll discuss those at the end of this article.)

If you’re not already familiar with the SharePoint REST interface, take a few minutes to read up on the user, group, and role REST documentation from Microsoft: https://msdn.microsoft.com/en-us/library/office/dn531432.aspx.

We have some options around how to specify the group we’re using – either by its name or by its numeric ID. We’ll be using the group name in this example, because it seems like it would be a little more user friendly. Our REST request is going to query for all the users inside the group specified in the request, inside the site specified by the request. It’s going to look like this:

GET /_api/web/sitegroups/getbyname()/users

Now, drop a “Send an HTTP request to SharePoint” action onto the diesign view after the trigger. Set it up like this:

alt text

OK, so now we’ve set up out trigger and used the data sent to it to invoke a call to SharePoint’s REST interface, which will return the serialized user data as a string. Next we need to transform that into structured data the Flow can use.

Parse the JSON

Now we’ve retrieved the data from SharePoint representing the group users. But the Flow only sees this as a string, even though it’s JSON structured data. We need to tell the Flow to treat this as structured JSON, and to do this, we need the Parse JSON Action.

So, after the SharePoint HTTP call, drop a Parse JSON action onto your design surface. Set it up to use the Body from the SharePoint HTTP call as its content. For the schema, click the “use sample payload” link and paste this into it:

{
    "d": {
      "results": [
        {               
          "Email": "AdeleV@mytenant.OnMicrosoft.com"
        }
      ]
    }
}

So now your Parse JSON action looks like this:

alt text

Build the recipients string

A collection of recipients in a Flow Email action is represented by a semicolon delimited list of email addresses. Since we now have an JSON array of objects containing these addresses, we now need to loop through the results and add the delimited email addresses into a string variable.

First, let’s create the variable and initialize it to an empty string:

alt text

Next we need to loop through the results array. Do do this we add an “Apply to Each” action. This action is a little tough to find – you’ll find it in the “more” section when you add an action to the end of your Flow:

alt text

You’ll add as the input to this action the output from the Parse JSON action you set up earlier. It should be called “value”. Inside the loop we’ll put an “Append to String Variable” action, adding the “Email” property from the passed array, and adding a semicolon at the end.

alt text

Set up the email action

Now we’re getting close. We have our delimited recipient string pulled from a live SharePoint group, and we’re ready to wire up the Email action.

Add an “Office 365 Outlook – Send Email” action to the end of your Flow. Add your string variable on the “To” line. Fill out the values for Subject and Body (you can parameterize these as well if you need to. I’ll leave that implementation up to you).

alt text

Test the Flow

Now everything is wired up, and we can test this out. Since we have an HTTP-triggered Flow, we can use Fiddler or Postman to execute requests directly to the Flow. I like to use the VS Code “REST Client” extension (https://marketplace.visualstudio.com/items?itemName=humao.rest-client) since it’s easy to use and I almost always have VS Code up and running anyway. We can grab the URL from the HTTP Trigger definition:

alt text

And here’s how we wire up the request in VS Code (Clicking “send request” will do what it says):

alt text

If your Flow and request were wired up successfully you should get a 202 response back, and we should be able to see our executed Flow in the history section. Here we can see the inputs and outputs of each action and whether it secceeded or failed, and usually if we did something wrong it’ll be obvious here.

alt text

If your Flow succeeded your recipients ought to have the email in their inboxes.

Call from another Flow

OK, now we have a working Flow that emails a SharePoint group. Now we want to reuse this Flow by calling it from other Flows. To do this we use within that Flow a Request action. Set up the URL and JSON in that action, run it, and if everything is done right, you’ve got a solution to email a SharePoint group that you can use in any of your Flows.

alt text

About those security implications

You should be logged in using a “service account” when authoring Flows. If you create the Flow using your normal user account, three things will happen. First, the emails will appear to be coming from yout user account; second, the Flow will assume the security context of your account, which means it’ll break if your account’s permissions con’t perform the actions against the specified site. If you leave the company all your Flows will break. And third, it will be difficult for your colleagues to maintain or even find the Flows you’ve written.

So create a “Flow Author” licensed account for this purpose. You can name it whatever makes sense to your organization.

Thinking ahead

It would be great – and a lot easier to use – if we could wrap this Flow into a Custom Connector rather than manually wiring up the HTTP request. And that’s exactly what we’re going to do in Part 2. Stay tuned!

26 thoughts on “Email a SharePoint group from a Flow, Part 1

  1. Pingback: Emailing a SharePoint Group from Flow, part 2: The custom connector | derekgusoff

  2. Hi, I tried implementing this but I am receiving an error on the “Apply to Each” step. It is telling me the following:

    ExpressionEvaluationFailed. The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@body(‘Parse_JSON’)?[‘d’]?[‘results’]’ is of type ‘Null’. The result must be a valid array.

    I followed it step by step. Please help!

    Like

  3. Look inside the outputs from your HTTP request to SharePoint and your Parse JSON activities. I would guess that your HTTP request was malformed, or you misspelled the group’s name. Your HTTP request should contain a JSON representation of your users expressed as an array (a number or objects wrapped in square brackets).

    Like

    • Hi Derek, I have changed the emails for privacy reasons, but this is my output:

      {
      “value”: [
      {
      “Email”: “abc@test.com”
      },
      {
      “Email”: “abc2@test.com”
      },
      {
      “Email”: “abc3@test.com”
      },
      {
      “Email”: “abc4@test.com”
      },
      {
      “Email”: “abc5@test.com”
      },
      {
      “Email”: “”
      },
      {
      “Email”: “abc6@test.com”
      }
      ]
      }

      Would the empty email line be what is throwing it off? I am not sure why that empty one is even there… I double checked the users in the sharepoint group and there isn’t a blank one. Not sure how that could even be. Any idea?

      Like

  4. Yes the ParseJSON step succeeds. I created a test user group just a few minutes ago, so the user list is shorter this time. This is the output:

    {
    “value”: [
    {
    “Email”: “abc@test.com”
    },
    {
    “Email”: “abc2@test.com”
    }
    ]
    }

    In the Apply to Each step, your screenshot has “value” as an option in ‘Dynamic Content’ to choose from. All I have is “results” – could this be where we are differing? What would I put in the “Select an output from previous steps” field if “value” is not available to pick from dynamic content?

    Like

    • Lauren, you’re really close. Your ParseJSON output has a property inside it called “value”, which contains the array. So in your case it would be “results.value”. I’m not sure why you and I are seeing different things. You might need to drop down into an expression to get what you need. Something like this:

      @body(‘Parse_JSON’)?[‘results’]

      ..in the Apply to Each

      and then the Email field should light up for you inside the loop.

      But you’re very close. Don’t give up on it.

      Liked by 1 person

  5. So I got this working, but I have another problem now… It continuously runs unless I turn it off. I called the “email user group” from another flow, but it doesn’t work unless the “email user group” flow is on. When that’s on, it runs every 5 minutes. Any idea why this may be happening?

    Like

  6. I used the “When an item is created in Sharepoint List” trigger and put it after the “Post” last step in this article … should it be before?

    Like

  7. Hi, i have a scenario a little different, i am using schedule as trigger, and in the same date i have several items, in lookup column i have multiple groups, l tried the above solution bur it concatenates all the emails from different groups and sends email to all, i don’t what that, l need that each group(or several groups) must receive by email only their item.
    it it possible please?

    Like

  8. Lauren Ulibarri – Can you tell me how you got around the null issue?

    ExpressionEvaluationFailed. The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@body(‘Parse_JSON’)?[‘d’]?[‘results’]’ is of type ‘Null’. The result must be a valid array.

    Like

  9. The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘ @{body(‘Parse_JSON’)?[‘d’]?[‘results’]}’ is of type ‘String’. The result must be a valid array.

    Getting this error. Please help

    Like

    • Take a look at your JSON you’re trying to parse. Looks like you’re getting a single object rather than an array. If you’re only getting a single object back maybe you don’t need the foreach.

      Like

      • its failing at the last Email Request saying ‘One or more of the email addresses you entered are invalid”. Thanks for the informative article :)

        Like

  10. So they now have a 5 step, 10 minute process to do something that took less than 1 step in SharePoint Designer 2007. I need to send an email to six groups. In SharePoint Designer 2007, that took less than 1 step. Now it takes a flow with 30 steps. The future is truly progressive.

    Like

  11. Let me start with I cannot use any HTTP triggers as they are Premium.
    The trigger I am using is ‘For a selected property’.
    After that I ‘send an HTTP request to SharePoint’
    Then ‘Parse JSON’
    Results:

    {
    “value”: [
    {
    “Email”: “Adam.Smith@irs.gov”
    }
    ]
    }

    Then Initial variable as string
    Then the ‘Apply to each’.
    I only get ‘Results’ and not value as a selection. I’m not a coder so the expression thing is Greek to me.

    With just ‘Results’ selected from the Dynamic Content it fails with:

    The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@body(‘Parse_JSON’)?[‘d’]?[‘results’]’ is of type ‘Null’. The result must be a valid array.

    With choosing ‘Results’ from the dynamic content and adding a period followed by ‘Value’ it fails with:

    The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@{body(‘Parse_JSON’)?[‘d’]?[‘results’]}.value’ is of type ‘String’. The result must be a valid array.

    There is only one member in the group which I would not think would cause it to fail.

    The goal is the kick of an approval workflow that will check a group membership for emails address based on where the document is in a folder structure.

    Thanks! if this can work this would be hella handy. Just have to build into each workflow as we do not have the premium license that http triggers require.

    Like

    • Sorry about the HTTP triggers, those are really useful!

      I think you’re really close. It seems the thing you’re passing into Parse JSON isn’t what it’s expecting. Did you use the “sample payload” to generate the expected schema? Try doing that with the output from a previous run of the SharePoint call, maybe the design surface will “wake up”. Only one member in the group should not pose a problem, it’s still an array, just with one thing in it.

      Good luck!

      Like

      • Thanks for the assistance!
        Yes, used the sample payload provided.
        The following is the output from the http request to SharePoint:
        OUTPUT: Body
        {
        “value”: [
        {
        “Email”: “Wade.Wilson@irs.gov”
        },
        {
        “Email”: “Bruce.Wayne@irs.gov”
        }
        ]
        }

        The following is the sections from the Parse JSON which clears with green checkmark
        INPUTS: Content
        {
        “value”: [
        {
        “Email”: “Wade.Wilson@irs.gov”
        },
        {
        “Email”: “Bruce.Wayne@irs.gov”
        }
        ]
        }

        INPUTS: Schema

        {
        “type”: “object”,
        “properties”: {
        “d”: {
        “type”: “object”,
        “properties”: {
        “results”: {
        “type”: “array”,
        “items”: {
        “type”: “object”,
        “properties”: {
        “Email”: {
        “type”: “string”
        }
        },
        “required”: [
        “Email”
        ]
        }
        }
        }
        }
        }
        }

        OUTPUTS: Body
        {
        “type”: “object”,
        “properties”: {
        “d”: {
        “type”: “object”,
        “properties”: {
        “results”: {
        “type”: “array”,
        “items”: {
        “type”: “object”,
        “properties”: {
        “Email”: {
        “type”: “string”
        }
        },
        “required”: [
        “Email”
        ]
        }
        }
        }
        }
        }
        }
        It’s almost as if the flow cannot tell what the delimiter is on the array? Or does not recognize the array?

        Like

      • Hey Derek, I got it to work. It’s a little different than the way you did it. Let me know if you want to a blog on it from a strictly SharePoint perspective without having to use the Premium flows. Thanks!

        Respectfully,
        Dotty

        Like

      • Glad you got it working! And it doesn’t really surprise me that your experience was different from mine. These things have a lot of complexity and nuance and cloud services change all the time.

        Like

  12. Hi, Im also stuck with the ” ‘foreach’ expression ‘@body(‘Analisar_JSON’)?[‘d’]?[‘results’]’ is of type ‘Null’. The result must be a valid array.”
    The output of Json seams to be correct. Can anyone help? thks!

    Like

  13. Hi Dotty, can you share it? I’m having exactly the same problem you had, and tryied some other aproaches, witjh no sucess.
    Thks
    Regards,
    João

    Like

  14. Hello Derek,

    I would suggest to change the headers of “Send an HTTP request to SharePoint” to:
    {
    “Accept”: “application/json; odata=verbose”,
    “Content-Type”: “application/json; odata=verbose”
    }
    This could solve the “The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@body(‘Parse_JSON’)?[‘d’]?[‘results’]’ is of type ‘Null’. The result must be a valid array.” in the Apply to each

    Cheers and keep up the good work!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s