How to send email notification for upcoming event using Power Automate

“How to set up a notification 7 days prior to upcoming event using Power Automate?”

“Do you know of a way to set up reminders for each instance on the calendar?”

You might consider using Power Automate action ‘Delay until’ to send a notification for an upcoming event. But it’s not the right approach. Running a flow on each event you’ll end up with a ton of flows running for days, until day 30. Each flow will end 30 days after it started, no more waiting. Let’s try another approach.

Use a scheduled trigger

Instead of running 1 ‘waiting’ flow per item, you can build only one Scheduled flow, running daily and sending emails. It isn’t even a complex flow, as in the simplest form it needs just 3 actions, including the Scheduled trigger. So the 1st step: create a new Scheduled flow with daily recurrence.

Filter upcoming event(s)

The next step in the flow is to filter only the relevant events. Since the flow is running once a day, you need to filter events at any times during that day, from 00:00 to 23:59. That means you need two dates for the filter, today+X days and today+X+1 days. All relevant upcoming events in X days will be between these dates.

Get the dates

To get the dates, you’ll need to combine 3 expressions, utcNow() to get today’s date, addDays() for the +X days part and formatDateTime() to get the time part from 00:00 to 23:59. Let’s take an example of today+7 and today+8 days and store them into variables.

formatDateTime(addDays(utcNow(),7),'yyyy-MM-dd')
formatDateTime(addDays(utcNow(),8),'yyyy-MM-dd')

All events between these 2 dates will fit the ‘upcoming event in 7 days’ filter. If you’d like, you can learn more about the expression in this article.

Now, when we got the dates, it’s time to use them in a filter. First in a SharePoint calendar list, then in Outlook calendar.

Filter in SharePoint calendar

You use Power Automate action ‘Get items’ to get the selected events from the calendar list. Like in any other filter, you must use column internal name in the filter. If we continue with the example above for notification 7 days prior to event, the filter should be:

(Start_x0020_Date gt '@{variables('Today+7')}') and (Start_x0020_Date le '@{variables('Today+8')}')

That means get items where ‘(Start Date > today+7 days) and (Start Date <= today+8 days)’.

If the filter doesn’t work for you, you can check this debugging article.

Filter in Outlook calendar

If your events are in Outlook calendar, it’s a bit easier to filter them as Power Automate has an action just for that ‘Get calendar view of events’. You just use the calculated dates in variables for the Start Time and End Time inputs.

Send the email notification

Last step in the flow is the ‘Send an email’ action. Use the data from the ‘Get items’ or ‘Get calendar view of events’ actions and send the emails. Maybe even from other than your email address.

Summary

I’d say there are 2 important points from this article. First, if you have some time related process, it’s always better to have a single scheduled flow to process all items. It can be flow for reminder, notification, escalation, archiving or something else. One daily flow always beats many paused item flows.

Second, you should understand that when you’re working with dates, they often include also time. It’s not possible to use [equals] operator when hours, minutes and seconds are involved. You have to work with a range. Get the beginning and the end of a range and filter all in between.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Add a Comment

Your email address will not be published. Required fields are marked *