Using Flow to send Email Reminder from SharePoint Data

This is part one of a two-part post.  In this post, I will show you how to use Microsoft Flow within O365 to send reminder emails based on a date in a SharePoint list.

Desired Functionality

A client has a SharePoint list of employee certifications and their expiration dates.  They want to implement a solution where the employee would be notified via e-mail automatically 30 days prior to their certification expiration date to remind them to update their certification.

Another requirement was to allow the employee to update only the expiration date of their certification and not any of the other information in that list item. They would want to include a hyperlink in the e-mail that goes out to update the items expiration date.   We will accomplish this piece using PowerApps and the walk-through on how to do that is in Part 2: Passing Parameters to a PowerApp.

To make the e-mail notification piece work, we will use Microsoft Flow's Recurrence feature to kick off the Workflow every day and loop through the SharePoint list to see if any items are due to expire within 30 days.

The Steps:

1. Create a Blank Flow

Figure 1 - Create a Blank Flow
On the next screen, we want to select the "Search connectors option"

Figure 2 - Search Connectors and Triggers

2. Add an Action and search for "Schedule - Recurrence".  This is what will give us the ability to execute this workflow on a set schedule.

Figure 3 - Create a Schedule - Recurrence Trigger

3. Set the recurrence to your specification.  In this case, we want to check once a day.  Additionally, you can specify at what hour and/or minute of the day that you want the Flow to kick off.

Figure 4 - Configuring the Recurrence Settings

4. Next, we need to add a Scope.  A scope is just an easy way to group two or more actions together.  If you are use to SharePoint Designer workflow then a Scope is comparable to a Step or Stage.

Figure 5 - Adding a Scope

5. Within our Scope, we want to add a "Compose" Action.  This action is letting us create a variable that we can use later on in a different action.

Figure 6 - Adding a Compose Action

6. We want to set the input value to:


Figure 7 - Configuring your Input

7. Now that we have our variable, we need to return the items from the SharePoint list.  We will do that with the "SharePoint - Get Items" Action.

Figure 8 - Adding the Get Items Action

8. Put in the Site Address of your list and select your list from the dropdown
9. Now we need to configure the Filter Query.  We are telling Flow what items from the list we want to return.  In this case, we only want to return items where the Expiration Date is equal to our variable (30 days).  In the Filter Query put the following:

ExpDate eq 'Output'

Figure 9 - Setting the Filter Query

10. Now we want to add an "Apply to Each".  This is going to take the output of our array (list of items from the Get Items in Figure 9) and apply whatever actions you specify to each record.

Figure 10 - Adding an Apply to Each

11.  In the "Select an output from previous steps", we are going to select the "value" option from our Get Items function.

Figure 11 - Configuring the Apply to Each

12. Next, for each item from our SharePoint list that is set to expire in 30 days, we want to send out an e-mail to the Employee to let them know their certification is expiring.  To do this, we will use the Send Email Action.  Just fill out the To, Subject and Body information according to your requirements.

Figure 12 - Send Email Configuration

13.  That's it, all that's left is to publish your Flow!

To see how to include a hyperlink with the current item ID in your Flow e-mail to the PowerApp, please see Part 2: Passing Parameters to a PowerApp.