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:
 |
| 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.
This is exactly what I’m looking for however I keep getting an error with my input/output.
Would you mind sharing a screenshot of the error you’re getting?
addDays(utcNow(),30,‘MM/dd/yyyy’) – says it is invalid
Are you copying and pasting that in? If so, try manually typing it instead. Sometimes the quotes don’t copy over correctly and it causes errors.
Thank you so much !
I was looking for a detail step by step explanation
Q: Where does the ” ExpDate eq” comes from ?
My goal is to have an email sent 2 days after an event (SharePoint Calendar) is completed
In order to remind the trainer to update the training log
– I changed your the variable to:
addDays(utcNow(),-2,’MM/dd/yyy’)
– I tied to change the Filter Query to :
equals({body(‘Get_item’)[‘EndDate’]},outputs(‘Compose’))
but unseccesfull
HI,
Nice tutorial.
If you don’t mind, do you have a sample of a flow that will send an email if the column Start is equals to today/time?
Also in fig 10, what is the ExpDate eq’ parameter?
Getting errors on that part:
{
“status”: 400,
“message”: “The expression \”ExpDate eq’ 04/05/2019\” is not valid.\r\nclientRequestId: xxxxx\r\nserviceRequestId: xxx-xxx-xxx-xxx-xxx”
}
Thank you!
How to send one email to a list of recipients instead of using apply to each?
I do not want a large number of individual email threads, but just one email with the right recipients.