I had a need and all I needed was more Flow!

In a communications site in SharePoint Online, I have a list of contacts for my committee, something like:

This list is great, it lets everyone see who’s who across the committees.

Now I want to email them, sometimes just the Liaisons, sometimes just the Lead/Co-Leads, sometimes everyone. I tried copy/paste from the list but that didn’t work AT ALL. I could connect with Excel then copy/paste, but that was getting annoying.

If only there was a better way…

Enter Microsoft Flow!

This task is right up Flow’s alley. With a little time (~30 minutes) and a little research, I was able to create a mass mailer in SharePoint Online using Flow.

Create a list in SharePoint

First thing, we need a list to collect the email details, like subject, message, to, etc. Here’s the list I made, pretty straight forward:

The To drop down is simply the same options from my Committee Contacts list

I added All and made it multi-select too.

Create the Flow

If you haven’t created a Flow yet, do it. Super simple, super powerful. Here’s the flow I made:

Let’s take it a little slower.

Trigger: SharePoint – When a new item is created

I created the trigger to read from the list I created earlier. Easy enough ;). Quick note, at this writing, Flow will only show you the modern lists in the site in the drop down. Classic lists will need to be manually entered.

Condition: To contains all

This part was a little tricky at first. I needed to check if the To field had All selected. If so, then just grab everyone from the list, if not we wanted to filter it out. Since the To field is a multi-select field, I need to check if All is one of the selected items. A multi-select field comes into Flow as an array.

I worked around this by flattening the array into a single string. You’ll see the Condition has an Expression in it: join(triggerBody()?['To'], ','). This takes all of the values selected in the To field, and makes a single string out of it, comma separated. It mashes the array of options into a single value for easier comparison. Then the rest of the condition checks if this string contains the word All.

If Yes, All is selected

Action: SharePoint – Get Items; All Contacts

First, let’s get All Contacts. That action simply connects to the list in my site and pulls all items. We do some fancy filtering in the No condition below.

Action: Data Operations – Select; Select emails

Then we take the array that gets returned, and just pull out the email from the Person field. We use a Select action for this.

Initially, using a Select action has an option for you to create a new array of objects by mapping values to your custom properties. As seen here:

In the Map field, you can specify any key you want, and then put in a value. You can add multiple rows here too to create your own complex object. This is great but we don’t need this.

Instead, press the little T icon to the right, and this changes the Map to a single field. You’ll see it’s preloaded with some basic JSON. Delete it all and just insert the 1 field we care about.

This makes a single field array, containing just the email addresses. We’ll need this to be able to merge all of the values together for the email!

Action: Office 365 Outlook – Send an Email; Send Email

Now let’s send the email!

In the To field, we are joining the Select emails array into a single string. That Expression is: join(body('Select_emails'),';'). If we didn’t use the Select action previously, we won’t be able to create a single string with all of the email addresses.

In the Subject and Body fields, we include the fields from the original SharePoint list item.

In the CC field, I automatically CC the creator, and anyone they specified in the CC field.

Note on From. When the email sends, it will send as you, since the Office 365 Outlook connector is using your profile. If you have permission to send on behalf a group account, maybe a Group, you can put that email address here. I didn’t bother, just sending from me.

Finally, I make sure Is HTML is enabled. It’s a Rich Text field in SharePoint, and I want to ensure that formatting carries through.

That part is done!

If No, a specific group of users was selected

The No condition is a little bit more complex. We have to know which groups of users the creator specified in the To field, then get only those email addresses and then send the email.

Action: Data Operations – Select, Select Group

The To field is an array value, since it’s multi-select in SharePoint. This select statement grabs the individual values into a single array. More on Select action above under the Yes condition.

Action: Data Operations – Join

Now let’s join the To values from the above Select group action into a single string value, with a little jazz.

The Output in the From field, is the Output from the Select Group action. The Join with will join each value together, with that string between each value. For instance, if my array looked like ['One','Two'], then it’ll join it like ‘One’ or Category eq ‘Two. This is partial value for the odata filtering for the next action.

Action: SharePoint – Get Items

Next we’ll get the people from the selected groups.

This is the same Get Items action from earlier in the Yes condition. Here we select the list containing all of the contacts, but then specify a Filter Query. The filter query has the rest of the odata filter. This value is Category eq ‘[Output from Join]’. Making sure to wrap the output in single quotes.

When more than one item is selected in the array ['One', 'Two'], this Filter Query will be Category eq ‘One’ or Category eq ‘Two’.

Action: Data Operations – Select; Select emails 2

Same deal from earlier under the Yes condition, we want to just pull the email addresses of the people we got from the list.

I had to name is Select emails 2 since there is already an action in this flow Select emails. Fancy right?

Action: Office 365 outlook – Send an Email

Now let’s send the email! Nothing too different than what we did under the Yes condition

Again, the To field has the value Expression: join(body('Select_emails_2'),';'). Joining the output from the Select Emails 2 action. Make sure Is HTML is set to yes! (I frequently forget this)

Finally, mark the SharePoint Item as Sent

One last action, sitting outside of the condition, to update the SharePoint item as sent.

Action: SharePoint – Update Item

We now update the originally created item, and set the Sent field to Yes. You do need to carry over the original field values, otherwise they’ll be blank after it updates.

 

Done!!

Feels like a lot but it wasn’t. Let me know how it goes! ‘Til next time, Happy Flowin’!