Sending Reminder Emails Using Power Automate

Introduction

Sending reminders is a general use case in the approval process, where the business users (approvers, Managers, developers, etc) need to be reminded about the pending tasks. In this, article we will see how we can achieve reminder emails using Power Automate via email to the specific person who is responsible for completing the tasks.

Prerequisites

Access to a SharePoint online site with minimal ‘contribute’ rights.
Organization subscription to M365. I am using an E5 subscription from the developer tenant. You can create developer tenants for free using the developer program from MSFT. More details are in the references section.

Design and Architecture

Here a SharePoint online list is being used that has the following fields.

Field Name	Data Type
Title	A single line of text
Assigned To	Person or group
Reminder Stage	Choice
Reminder Sent	Date and time
Due Date	Date and time

Columns

Conditions for reminder

The first Reminder is to be sent before 7 days of the due date.
The second reminder is to be sent before 3 days of the due date.
Wait for 3 days, if no action is taken escalate to the manager.

Steps

Step 1. Log in to the Power Automate maker portal https://make.powerautomate.com with your organization or developer subscription.

Step 2. Click on the ‘create flow’ option and check for scheduled flow. Configure the schedule to run every day. You can change it to every day, week, or year.

Create

Step 3. Since we are sending reminders every day in this case, select ‘Schedule cloud flow’.

Scheduled cloud flow

Here I am selecting the schedule as ‘Day’.

Flow name

Step 4. Get the items that are due in 7 days. For this select the ‘+’ option in Designer, search for ‘get items SharePoint’, and add the ‘Get items’ action.

Choose an operation

Step 5. Configure the ‘Get items’ action. Here I am getting the items which have due within 7 days. For here I have used the query where the ‘DueDate’ value is less than or equal to 7 days from today.

Here is the actual query.

DueDate le '@{getFutureTime(7,'Day','D')}

Filter query

The expression is

getFutureTime(7,’Day’,’D’)

Below is what the action looks like after configuring the query.

Get items

Step 6. From the get items output parse the values and select the following values which will be used in our reminder flow process.

ID: item ID
AssignedToEmail: The email id of the assigned field
ReminderStage: The current reminder stage. The default value is none.
Due Date: The due date value for the item
Title: The title of the item
Item Link: Direct url to the item which can be sent by email.
AssignedToPerson: The display name of the ‘AssignedTo’ field.

For this click on the ‘+’ option in Designer and look for the ‘Select’ action and add it.

Data operation

Step 7. After adding the ‘Select’ add the ‘value’ from the ‘Get items’ in the ‘From’ field. For the ‘Map’ values. Enter as shown in the below screenshot. Please note that for the ‘Value’ column in the below table, you have to provide the internal name of the field for the select operator.

Key	Value
ID	Item()?[‘ID’]
AssignedToEmail	Item()?[‘AssignedTo/Email’]
ReminderStage	Item()?[‘ReminderStage’]
DueDate	Item()?[‘DueDate’]
Title	Item()?[‘Title’]
ItemLink	Item()?[‘{Link}’]
AssignedToPerson	Item()?[‘AssignedTo/DisplayName’]

Below is the screenshot of the Expression for the “AssignedToEmail” Key. Please update the key values for other fields. In the below screenshot ‘AssignedTo’ is the internal name. The Email is the value coming as an object within the json object. If you pass the response from ‘Get items’ to the VS code, you can find it.

Value

Expression

Similarly, if you want to have the display name of the ‘AssignedTo’ the query will be changed to

item()?[‘AssignedTo/DisplayName’]

After configuring all the mapping values, below is the final action it would look like. This can change based on your mapping values.

Select

Step 8. Now add ‘Apply to each action and get the output from the ‘Select’ action.

Step 9. Here I have used the ‘Compose’ action to get the day difference between the dates Due Date and Today.

div(sub(ticks(items('Apply_to_each')?['DueDate']),ticks(convertTimeZone(utcNow(),'UTC','Central Standard Time','yyyy-MM-dd'))),864000000000)

Ticks convert date time to 100 nanosecond intervals. In a day there are 864 billion ticks. To get the days divide the ticks value with 864 billion. To get a better understanding of ticks’ function, please refer to the reference article by Will Skinner.

Here is the flow design. The very first time the reminder flow runs; it gets the tasks that are due within 7 days. If there are any tasks that are pending due in the next 7 days it gets the result items. For each item, it does the following

Updates the value in the list which is ‘ReminderStage’.

HTTP

The expression for ‘ReminderSent’ which is the internal value of the field in the SharePoint list is below.

convertTimeZone(utcNow(),'UTC','Central Standard Time','yyyy-MM-ddTHH:mm:ss')

This basically gets the current date time which is in UTC and converts to central standard time using the ‘convertTimeZone()’ function.

Gets the Assigned to Email reminds to take action.

Send email

The expression for ‘ItemLink’ is.

items('Apply_to_each')?['ItemLink']

Step 10. Configure the condition if the ‘ReminderStage’ is ‘First’ and the task due is within 3 days.

Conditions

Here I am checking if the ‘ReminderStage’ is equal to ‘First’ and the task due is coming within 3 days.

ReminderStage expression is items(‘Apply_to_each’)?[‘ReminderStage’]
I am using the same expression for the task due which is used in composing action at step#9

Step 11. In the yes block update the SharePoint list status and send another email.

Send request

Second reminder

Step 11. The final condition block, in which the conditions are configured to check ‘ReminderStage’ is equal to the second, and the task due is today.

Add

In the ‘Yes’ block update the SharePoint list item to set the status to ‘Incomplete’.

Sharepoint

Get the Assigned To manager.

User

Send an escalation email to the manager.

Send email

Conclusion

Thus, in this article, we have seen how to effectively send reminders using scheduled flows. By doing this, we are not letting the flow to wait until the days to pass. It just runs once, if the condition is satisfied it executes the required actions, if not it flows through the end.

References