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

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.

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

Here I am selecting the schedule as ‘Day’.

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.

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')}

The expression is

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

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

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.

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.

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.

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’.

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.

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.

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.

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.

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

Get the Assigned To manager.

Send an escalation email to the manager.

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

• https://developer.microsoft.com/en-us/microsoft-365/dev-program
• https://learn.microsoft.com/en-us/dotnet/api/system.timespan.ticksperday?view=net-7.0#definition