Jared Matfess's Blog

Having fun with SharePoint, Office 365, and Microsoft Azure

Getting Salesforce Updates via E-mail with Microsoft Flow — February 10, 2019

Getting Salesforce Updates via E-mail with Microsoft Flow

This morning I woke to an excellent e-mail from Ragnar Heil paying me a compliment about a video I’d created about connecting to Salesforce using Microsoft Flow and then a question on how to take it to the next level. What Ragnar was hoping to accomplish was an e-mail when an opportunity is created in an account that he is interested in.

My starting point for figuring out how to do this was to first create a new Flow that grabs a single opportunity so that I could see the values, field names, etc. I logged into Salesforce, navigated to Opportunities, and then selected one for an account that I would want to “follow”. Next, I copied the Opportunity ID to my clipboard (it’s in-between the words Opportunity & View within the URL)


My first step was “Manually trigger a Flow” so that I could kick it off and read the various values. The second action was “Get Records”. I added a Filter Query condition of ID eq ‘0061R00000m4IosQAE’ in order to pull back that single record.


Next, I ran the Flow in order to see the values returned in order to help better understand the Opportunity object and the various columns associated with it.


Since I wanted to get Opportunities for a specific account I changed the Flow Trigger to be when a new record is created, pointed at the Opportunities Object Type, and filtered to just the AccountId for the customer I wanted to track: Record-Created-Account-Filter

From there you can create other actions such as send e-mails, write to Excel spreadsheets, etc.

Create a new Opportunity in Salesforce and watch your Flow fire! 🙂

Pro Tip for Getting Started with Flow’s Get Items & Get Records Actions — August 4, 2018

Pro Tip for Getting Started with Flow’s Get Items & Get Records Actions

I have been having a bit more fun recently with using Microsoft Flow to help automate more of the manual (aka boring) tasks that I have to do in a given week. I thought I would share a quick “Pro Tip” for those who are just getting into building workflows. The Get Records, Get Items, etc. actions are incredibly powerful and often times what you will want to use to start iterating over your content.

My Pro Tip for when you’re first starting to build out your workflow is to set the Top Count parameter to 1 and then run the flow so you can see what data will come back from the action. T


Running a quick test of your Flow you can examine the Body of the request and get to understand the different values for your data. Should you also want to take advantage of a Filter Query, it’s important to use the correct internal name for the field and this is a quick & easy way to figure that out. By limiting yourself to just 1 record, you can easily scan the data without the clutter of multiple records being returned.


Nothing fancy, just a quick Pro Tip for speeding up your Microsoft Flow development!

Automating the sending of presentation slides using Microsoft Forms & Microsoft Flow — July 30, 2018

Automating the sending of presentation slides using Microsoft Forms & Microsoft Flow

This past weekend I had the distinct pleasure of being able to co-present with one of my Slalom colleagues on “Leveraging Office 365 Services to Drive Office 365 Adoption”at SharePoint Saturday NYC. This was a new talk for me and I was very excited to try it out at one of my favorite events.

One of the questions I always receive during my sessions is how can the attendees get a copy of my slides after the presentation. There’s always the awkward dance of me as a speaker not exactly knowing where we are posting and then trying to remember that I need to upload them somewhere so that folks can grab them. I decided to head this off by leveraging Microsoft Forms & Flow during our presentation to help facilitate that.

During the event I asked folks to fill out a very brief Microsoft Form which asked them if they’ve rolled out O365 yet, if so which services, and then their e-mail address.


Then I built a very quick Flow that triggers when someone submits that particular form. And then the next action (since I’m always tweaking my slides) is to go out to my OneDrive and convert that instance of my presentation to PDF since I’m not supposed to send out Slalom themed decks in PPTX format.


Next I’ll get the actual response details from the form submission using the “Get Response Details” action. Flow will automatically throw this into an Apply to each loop however you’ll just set the Form Id to the Microsoft Form you are using, and then the Response Id which is the individual submission from the user. The loop will only run once.


Finally the longest step is to have it send the e-mail:

I kept it pretty simple with populating the To field from my 3rd question on the Form. The subject was hardcoded but I could always extend that in the future to pick the event name from a SharePoint list or something else but I went with the easy route on this one. In the body I provided links to Amy and my LinkedIn pages in case attendees wanted to connect in the future.

Under the advanced options I included the Attachments Name & Content from the output of the Convert to PDF step above, I also made sure to set the Importance to High and that Is HTML to Yes so that I could enable formatting as depicted in the Body section of the e-mail.

So that was it – a pretty simple Flow which has a pretty high impact, especially during a live presentation, so that the people will walk out with the slides and not have to worry about tracking me down later!

Hope you enjoyed this quick blog post!

Using Microsoft Flow Get Items Action and Filter by E-mail address of a Person Field — July 14, 2018

Using Microsoft Flow Get Items Action and Filter by E-mail address of a Person Field

This blog post is going to win an award for best title ever. 🙂

A colleague and I were working on a problem where we wanted to aggregate all the list items that were assigned to a particular person, and then send them an aggregated e-mail in a simple HTML table. Sounds easy enough right?

To get started – let’s create a simple list with Title, Owner (Person), and DueDate (Date/Time) columns like below:


Next, we’re going to create our Flow from a Blank Template and have the Trigger be Flow Button for mobile – essentially manual for testing purposes. Next, add the “Get items” action and enter your site address & list name:”


Before we add the filter to Get items, I recommend you save and test the flow so that you can see what gets returned from SharePoint before you apply your filter.

What gets returned is in the Body section of the Get items outputs:


Looking at what’s returned you’ll see that the Owner column has properties such as Claims, DisplayName, and Email.

Now, let’s go ahead and add the filter so that we’ll return List items that have tim@matfess.onmicrosoft.com as the Owner.

Show advanced options to show the Filter Query input


Now let’s add the filter and notice the case sensitivity. You’ll want to reference the e-mail attribute as ColumnName/EMail so for my example it’s Owner/EMail


Now try running your Flow and you should see success!


The output of the Get Items is now a collection of all the Items returned based on your filter. Now you can loop through the results and take the appropriate action based on your use case.

Happy Flowing!


Monitoring Yammer Conversation Content with Microsoft Flow & Cognitive Services — July 7, 2018

Monitoring Yammer Conversation Content with Microsoft Flow & Cognitive Services

I’m currently working with a client on preparing to roll-out Yammer as part of a new Employee Portal project. One of the key project objectives is to help drive employee engagement through this new portal however there are executives who are concerned with how employees might use their new found digital voice. The plan is to build a “Voice of the Employee” Yammer group to drive those connections and leverage the Modern Yammer webpart embedded in the page to surface those conversations. Yammer does offer the ability to configure “naughty words” and alert an administrator if those are triggered however those can also result in false positive responses. As I pondered how I might be able to help appease some of the executives’ concerns with how Yammer might be misused, I started to wonder if perhaps I might be able to leverage technology to help better detect misuse of Yammer – and that’s how I came up with this proof of concept.

First off, you’re going to need access to Azure and specifically to provision an instance of Text Analytics which will be used to detect the sentiment of the text that is posted to Yammer. Using Machine Learning, Microsoft’s Cognitive Services will extract key phrases from the text and provide a score based on data gathered to assess the sentiment of what the person had typed.


When you click create you’ll give the service a name, assign it to a subscription, decide on which datacenter to run the service from, which resource group to add it to, and then finally allows you to pick the pricing tier.

Microsoft is rather generous in providing you 5,000 transactions per month for free which is great for getting you going. At the next level of 25K transactions (calls to the Text Analytics API) you would be charged $74.71 (as of 7/6/18) which will likely go down as more people are on the service. You can see the various pricing tiers below as of when this blog post was written.


After Azure finishes provisioning your resource you’ll want to grab your Keys which will be used later to setup Flow to be able to connect to Text Analytics and receive a sentiment score.


Now comes the fun part! We’re going to hop into Microsoft Flow and create our awesome workflow!

You’re going to start from a blank template and have the trigger action be when there is a new message in a group. You’ll configure the action to point to your Yammer instance and select the appropriate group which you will be monitoring.


The next action is to Detect Sentiment which you will configure using the information you received from Azure. You’ll name the connection, provide the account key from Azure, and then provide the Site URL which is the Text Analytics API based on the region where you provisioned your service.


Example of API:


Once you’ve created the connection you’ll want to have it reference the Message Text value from the Yammer post.


Your next step is to add a condition to check and see if the score received back from Text Analytics is less than .3 which would indicate that the post is negative. Text Analytics uses a machine learning classification algorithm to generate a sentiment score between 0 and 1. Scores closer to 1 indicate positive sentiment, while scores closer to 0 indicate negative sentiment. I noticed another Flow template available which leveraged Text Analytics for detecting negative Tweets and they also used .3 as their negative threshold – so for consistency sake, let’s follow their lead!


If you click on Edit in advanced mode you can copy & paste the line below:

@less(body(‘Detect_Sentiment’)?[‘score’], 0.3)

If the text meets the condition of being less than .3, then I perform two actions:

  1. I get the user details of the person who made the post
  2. I lookup that person’s manager by passing the User Email variable fromt he previous Get User Details action. I’m sure you know where this is going! 🙂


Finally, I have an action that sends an e-mail to both the Corporate Communications admin who wants to be notified of these negative posts. Second, I CC the user’s manager so that they are also aware of what has taken place on Yammer.

So obviously this is very customizable to meet your needs, but I merely coded in the “To” line for the person or shared mailbox that is monitoring Yammer content along with a subject line to let them know it’s a negative message that was posted.

In the body I provide the person who caused the offense, the message they posted, and then a link to the conversation thread.


Just so you can see what happens when the data is passed to Text Analytics – notice the post I added to Yammer was “Super duper stupid” which was given a score of .221 (with a lot of extra numbers) which pushes it below the .3 threshold that was set.


To get a little meaner I submitted an additional post on Yammer which said “Our executives are so stupid” which if you’ll notice received an even lower score meaning that there’s a higher probability of this post being negative.


So a couple of thoughts about this setup:

  1. You definitely do not want to run this type of a Flow for all your Yammer groups, that will definitely become expensive as adoption increases.
  2. There is a lag between when someone posts on Yammer and when your Flow fires. This can mean a potentially negative message is visible for a few minutes. Testing in my O365 Developer tenant, I found that most of the time the Flow ran within 3-4 minutes of the post being made in the group.
  3. I really think this is the type of thing you might setup “in the beginning of Yammer” to help quell the fear of those anxious executives that feel that employees may misuse Yammer.

When I look at Yammer as a product I really do focus on the positive impact that it can have on an organization, while respecting the fact that there is always the possibility of the platform being misused.

One of the interesting things to consider is how you could perhaps leverage Cognitive Services to catch a negative message before it’s even posted to Yammer. What if as a user is typing it alerted them that what they might be posting could be perceived as negative, remind them of the social media policy they signed, reinforce the consequences of platform misuse. Extend that same thought to Outlook and have it quarantine any e-mail that you are about to send that is snarky and perhaps unprofessional. Imagine being able to catch those moments before they happen using something like Text Analytics could be quite powerful!

Very cool stuff indeed!

UPDATE: I’ve submitted the following User Voice for Microsoft’s consideration https://yammer.uservoice.com/forums/399627-yammer/suggestions/34781296-leverage-microsoft-cognitive-services-for-sentimen


How to remove a user’s access to their Exchange Online while allowing them access to other Office 365 Services — January 3, 2018

How to remove a user’s access to their Exchange Online while allowing them access to other Office 365 Services

Acquisitions and divestitures can create some very interesting requirements from organization’s Legal Departments. I am currently working at a client who is in the process of divesting part of their business and they posed a rather interesting challenge to help facilitate the transition period for departing employees. They requested the following:

  1. Maintain all ~100 Exchange Online mailboxes in-tact
  2. Provide “Auto-Replies” in order to alert mail senders that their e-mail address has changed
  3. Prohibit the users from accessing their old e-mail box including reading old e-mail or sending new e-mail
  4. Still allow the users access to other Office 365 services including OneDrive, OneNote, the Office client applications, & SharePoint Online.

When presented with this list I was doing rather well until I hit condition #4 which put a wrinkle in my plans. Traditionally when performing a divestiture, I would usually follow a process of generating PST’s, providing to the new company, putting the mailbox on Legal Hold, converting the mailbox to a Shared Mailbox, remove the O365 license, etc. However, the additional requirement to maintain their access to other O365 services made this quite challenging.

At first I tried going down the path of trying to change permissions on the user mailbox which proved to be useless. There was no way of removing the user’s access to their own mailbox. Next, I started thinking about some sort of a leap frog type approach of creating a shared mailbox, backing up / restoring the user mailbox into it, auto-forwarding all new e-mails from the user mailbox to the shared mailbox without keeping a copy, etc. As you might imagine this became overly complicated rather quickly.

I then shifted my focus to some of Office 365’s security tools and mainly InTune Conditional Access Security Policies. Since my client is currently licensed for E3 with Enterprise Mobility + Security, they have access to both InTune & Conditional Access Policies.

This all came together really nicely as I was able to create a Conditional Access Policy that would block Exchange Online. I could then select users or ideally a group that would receive that automatic block (across OWA, Client, and Mobile).


When testing this out via OWA with the disabled user, I was greeted by this awesome error message:


And yet when I accessed OneDrive it loaded just fine:


One interesting thing I did notice that when I applied that policy, the user’s mailbox disappeared from the Exchange Online admin console. Yet, I was still able to make changes to the mailbox using PowerShell (for example setting the AutoReply).

Here’s the order of operations for the actual cut over:

  1. We put all the mailboxes on litigation hold
  2. Ran a PowerShell script to set the Auto-Reply
  3. Create an O365 Group (either Mail-enabled Security or Distribution List)
  4. Create the Conditional Access Policy and Include that Group to the policy blocking access to Exchange Online

This was so much simpler than the other convoluted process we were pursuing to try and auto-forward messages between mailboxes, etc. Azure Conditional Policies were the absolute key to making this happen and I was very fortunate that my client was already properly licensed.

More info on Enterprise Mobility Suite Licensing can be found here:


Creating Restricted Booking Conference Rooms in Exchange Online — December 26, 2017

Creating Restricted Booking Conference Rooms in Exchange Online

I have recently been working with a client to migrate rooms & resources from Lotus Notes to Exchange Online and the topic of “restricted conference rooms” has come up quite a bit. Basically these are conference rooms that a select group of individuals have the ability to reserve – examples being an Executive Conference Room, or a conference room that a particular department owns. The main challenge with Exchange Online vs Lotus Notes is that all resources & equipment show up in the Global Address List.

Through the GUI in Exchange Online you can setup “Delegated Booking” which is essentially a workflow for reservation requests that will be routed to users you specify as part of the delegated booking process. However, you will find that in large organizations that there are some executive admins that would prefer to not receive those reservation requests and would rather the room just be locked down for only them to reserve.

There’s no easy way to do this through the user interface however there is some clever PowerShell to accomplish this. What the experience will be is when a user that users you designate having booking ability will receive accept/decline based on the room availability. Anyone else submitting a reservation request will receive an automated decline message from the room.

Let’s take an example: Conference Room: CTBerlinMainWestCRW001@matfessconsulting.com 

You’ll connect to Exchange Online from your PowerShell window and run a command called Set-CalendarProcessing against that room mailbox.

There’s 3 parameters you’ll want to include:
AutomateProcessing – basically this is saying to have the room either accept/decline the reservation if it meets other requirements. If you don’t set this to AutoAccept, the room won’t do anything when you send it a reservation request
AllBookInPolicy – this is either enabling/disabling all users to submit reservation requests to this room. For the purposes of a restricted conference room we will set this to false.
BookInPolicy – this is where you would submit a list of comma separated users that have the ability to reserve this room. This can also be a Distribution List or Mail-Enabled Security Group (recommended)

When you tie it all together, here’s the full command to make a conference room restricted:

Set-CalendarProcessing -Identity “CTBerlinMainWestCRW001” -AutomateProcessing AutoAccept -AllBookInPolicy $false -BookInPolicy “jared@matfessconsulting.com”

So there you have it – a restricted conference room! A small recommendation to improve the user experience would be to update the display name to include (RESTRICTED) so that other users know it’s restricted. Alternatively, you can always set a MailTip which would be displayed similarly to an Out of Office message prior to the user submitting the reservation request. This appears in both OWA as well as the Outlook client.


See TechNet article for additional Set-CalendarProcessing parameters:

The Definitive Guide for When to use What — October 28, 2017
Improving your Work/Life Balance with MyAnalytics — September 26, 2017
SharePoint Saturday Toronto Slides! — August 19, 2017

SharePoint Saturday Toronto Slides!

Wanted to thank the amazing crew of speakers, sponsors, organizers, and attendees that made SharePoint Saturday Toronto amazing. I was delighted to speak to a pretty full room about “When to use What in Office 365”.

SPS Toronto Session

For those of you who came out to my session feel free to grab the slides below:

Definitive Guide For When to use What – Matfess

Thanks again for having me, and I look forward to seeing you again next year!