Create your personal list mailer with Microsoft Flow

I've got a fever for Flow!

Sorry, I had to.

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:

SharePoint list showing contacts

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:

New send to form in SharePoint

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

Who to send the email to

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:

The entire Microsoft Flow for getting and sending emails

Let’s take it a little slower.

Kick it off with a 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 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.



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






Slides and Scripts for my session: Why care about Office Dev PnP?

I had the wonderful opportunity to speak at Boston Code Camp 28! My goal was to showcase the awesome work done over at the Office 365 Developer Patterns and Practices initiative. This project provides real value to developers aiming to expand into the Office 365 space. We covered PowerShell and JavaScript libraries, reducing our development efforts from hundreds lines of code to just a handful.

Recording coming soon! Follow me on Twitter, blog (on the right) or leave a comment below to be notified when available

Oh, here’s my dog again, he’s so cute.


My slides can be downloaded here.

My scripts can be downloaded here.

My Slides and Scripts for IT Pro intro to Office 365 Dev PnP PowerShell

I had the awesome privileged of speaking at this years SharePoint Saturday New England. It was a great event, and huge thanks to the organizers and sponsors!

SharePoint Saturday New England SPSNE

I presented on the Office Dev PnP PowerShell initiative. This is a great open sourced framework to help expedite a lot of SharePoint related tasks like site management, user management and more.

My deck can be downloaded here.

My supporting scripts can be downloaded here.


Some great customer service with @Spotify

Everyone once in a while you come across fantastic customer service, and I really wanted to highlight this experience.

I posted:

2 minutes later, I posted:

@SpotifyCares replied:


Yup, created me a playlist, just for me… Note the time the songs were added, when they posted it. My son, he’s 5 by the way, was blown away too.

SharePoint Hybrid Search and Crawled Properties

Hybrid search with SharePoint and Office 365 is key. It’s an integral piece in any large migration strategy as well as creating an enterprise search experience by letting users search for on-prem content in O365 when setup correctly.

Hybrid search has been out for quite some time now, but it appears a problem, new or not, has arrived with your crawled properties. (I think it’s a newer issue, but no one else thinks so.)

The problem is that any fields/properties which are crawled on-prem, from SharePoint sites or custom sources like Salesforce (through BA Insight’s sweet connectors), any custom properties, may not appear for you in Office 365.

In my case, crawling Salesforce highlighted this problem. I could then recreate the problem with an on-prem SharePoint site.

After a successful crawl, going to the Search Schema in Office 365 Search doesn’t show the properties as expected. You can check the properties in the search center on-prem and see them there, but not in Office 365. I opened a ticket with Microsoft SharePoint Server team, not the O365 team (anything outside of O365, even hybrid, is outside of their purview). They spent a couple of weeks recreating and troubleshooting this to find the fix.

The solution is to log into Office 365 with the service account your on-prem search service is using. In my case, logging into O365 Admin with domain\spsearch allowed me to see these missing properties. I could then create Managed Properties and then all users could use the data. That’s it! Your service account doesn’t need any licenses, just admin privileges.

I don’t like this solution since it’s a service account, and as a service account, it’s for services, not users to actually use. So I posted the issue on UserVoice, I’d love your vote!

Also, if you have a minute, up vote my other UserVoice about the overall hybrid experience.

‘Til next time, Happy SharePointing!