Category Archives: Office 365 SharePoint Online

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.

How?

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

condition_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

yes_condition

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:

select_map

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.

select_emails

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!

send_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

no_condition

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.

select_group

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.

join

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.

get_items

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.

select_emails2

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

send_email2

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

update_sp_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’!

 

 

 

 

Advertisements

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.

cooper.png

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.

Enjoy!

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!

PowerShell isn’t just for Developers, IT Pros can rock the awesomeness in Office 365

I had the pleasure to co-present PowerShell and Office 365: ITPro #Awesomesauce with my friend and colleague Mike Dixon at the Boston Office 365 User Group yesterday. It was a great session with a lot of good questions. It was recorded so I’ll share the link to the video once it’s available. Big thanks to Hitachi Consulting for the sponsorship too!

I really enjoyed this talk because it kind of went against my natural thread. I’m a developer at heart, been a developer since graduating high school in 1997. I went from IT role to IT role but stuck with development. I’m an architect now (I feel all grown up), and I help teams design some pretty amazing solutions spanning technologies. However I won’t let go of the code. I support my team by pushing the best code practices, code reviews and writing my fair share of code. I like to learn new code in my spare time. I just love it.

This session was targeted to IT Pros: the non-developer type, “those guys” that used to make us developers’ lives harder with their governance policies and limitations on servers (I’m dating back to 2000’s, no one does that now, right? :| ). Given the huge push to cloud: SaaS, PaaS, IaaS, etc. the IT Pro’s role has increased dramatically in my eyes. In a lot of cases, when we come into a company to help create a solution on Office 365, it’s usually been lumped on top of the IT team, who already have their full time job. Going to the cloud is sold as easy and cost effective, but it does take dedication and training to do it right. Generally, new solutions are spun up in the cloud, but the legacy systems remain intact, full production ready with the same SLA for months after they’ve moved to the cloud. It’s not fair to IT.

This session helps bridge the gap a little between developer and IT Pro by providing some real value to IT. PowerShell doesn’t have to be scary or looked as a developer “thing”. It’s awesome, and can do some amazing things that once was only for development teams. Any IT Pro (admin, grunt, wire jockey) can type in commands and build something beautiful. That’s what it is, just commands. This is why I enjoyed this talk, enabling the IT Pros to do more and hopefully bolster them up to do their jobs in Office 365 more effectively and less tediously.

By the way, here’s the deck!