Send reminder emails using Microsoft Flow and Office 365

I was recently working for a customer and they wanted to send reminder emails to content reviewers so that they wouldn’t forget to approve their assigned content. A coordinator set a next review date field in an SP list and seven days before that date, the content reviewer should receive an reminder email.

My first thought was to use a retention policy that would kick off a SharePoint 2010 workflow created in SharePoint designer. But since this is Office 365 and old school workflows aren’t that awesome (plus the retention policy job runs once a week and it’s not possible to configure it to run daily in SharePoint Online AFAIK), I decided to use Microsoft Flow.

So this is the flow i created:

Let’s go through the steps:

  1. First of create an Recurrence action that will trigger an event to run at regular time intervals. In my case I want it to run once everyday.
  2. Next I create a Compose that returns a function value “@adddays(utcnow(), 7, ‘yyyy-MM-dd07:00:00Z’)” (remember to put the function within quotes or else it won’t work).
  3. Now that I have today’s date + 7 I can run Get items on the approved content SP List with an oData filter, which only returns items where NextReviewDate equals to the Compose Output (utcnow() + 7) and the Representative is not equal to null.
  4. Now that you have the list items, add an Apply to each step which loops all the items in the Get Items step, and then runs the Send an email step which sends an email to the representative of each item.