Do you want to send an email based on a date in a SharePoint list using Power Automate?
Managing dates in a SharePoint list can be crucial for keeping track of deadlines, project milestones, and contract expiration dates. One efficient way to ensure these dates don’t get overlooked is by utilizing Power Automate to send email notifications based on specific dates in your list.
Power Automate offers deep integration with SharePoint and setting up a flow to send reminders based on a date in your lists is actually quite easy. You will actually be able to create these flows from SharePoint with a few clicks.
The process covered in this article will teach you how to create and configure a flow that triggers an email when a specific date approaches, is reached or has passed. This will help to ensure timely action and follow-up by your team.
SharePoint Set a Reminder Feature to Build a Reminder Flow
SharePoint lists can help you store important information including dates.
Within your SharePoint list, you can create custom flows to send email reminders based on the dates in your list.
Utilizing the Set a Reminder feature, you can set up an automated reminder flow in just a few steps.
Here’s how to create a reminder flow from your list.
- Go to your SharePoint list.
- Click on the Automate option in the list menu.
- Select the Set a reminder option. This will reveal a submenu that lists all the date columns in your SharePoint list.
- Click on the column name containing the date on which you want to base your email. In this example, it’s a column named Date.
This will open a window pane on the right that asks you to sign into the required apps for the flow.
- Grant the necessary permissions and press the Continue button.
Next, you will see a window that allows you to name the flow and set the number of days prior to the date that you want to be reminded.
- Give your new flow a name in the Flow name field such as Send Email Based on Date. This will be the name you see later in the Power Automate portal.
- Set the number of days prior to send the reminder in the Remind me this many days in advance field.
- Use a positive integer number to get a reminder before the date.
- Use a negative integer number to get a reminder after the date.
- Press the Create button to finish the setup and create your new reminder flow.
📝 Note: This will create the flow in your Default Environment. You can now go to your default environment to see and edit the flow further as required.
This creates a flow that queries your list once per day. The query filters the list to those items that have a date within the required number of days and then sends an email with links to these list items.
Email Reminder Flow [Step by Step]
Above you can see the full flow that is created. Here is a breakdown of what each of these steps are.
1. Recurrence Trigger
This flow uses a Recurrence trigger set to run every day at 15:00. You can edit this to run at your preferred time each day.
2. Build Table Rows
This is an Initialize variable action. It creates a string variable that’s used to build the HTML table used to display the list items in the email.
3. Days to remind me in
This is a Compose action used to store the number of days before the date to send the reminder.
This is set by the user in SharePoint during the creation of the flow, but can be updated in this Compose action to any other number.
4. Get my profile (V2)
The Get my profile action gets your user details such as your display name, which is used as dynamic content in the email body.
5. Scope to get items due in 1 day
A Scope action is used to group together all the actions that might fail.
When they are all inside the Scope, you can then use the Configure run after settings in the subsequent action to send a notification that the flow failed if any of the actions inside the Scope fail. This way you’re less likely to miss an important reminder.
You should consider moving the Get my profile action inside the Scope as well, since it could also fail.
6. Today + x days
addDays(utcnow(), int(outputs('Days_to_remind_me_in')), 'yyyy-MM-dd')
A Compose action is used to perform and store the above calculation. It adds your selected number of days to the current date.
7. x Days + 1
Another Compose action is used to add 1 day to the Today + x days value. This creates a 1-day range of dates that can be used to filter the SharePoint list items.
8. Get list name by HTTP request
This Send an HTTP request to SharePoint action to retrieve the SharePoint list name based on the list GUID.
It is actually not used in the flow and can be deleted without consequence.
9. Get items
The Get items action is used to retrieve the filtered list items based on the date range from Today + x days to x Days + 1.
concat(concat('Date',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('Date',' lt ','''', outputs('x_Days_+_1'),''''))
The above expression is used in the Filter Query input to dynamically create the correct Odata filter query.
Date ge '2023-09-14' and Date lt '2023-09-15'
For example, if the flow runs on 2023-09-07 with a 7-day reminder, it will result in the above filter query.
A Condition action then tests if anything is returned from the Get item filter query with the above expression.
11. If Yes Branch
No actions are added to the If yes branch of the Condition action. This means no reminder will be sent.
12. If No Branch
All the email reminder actions are added to the If no branch of the Condition action.
13. Apply to each
An Apply to each action is used to iterate through the filtered list items and create the HTML to list all the items in a table within the email notification.
14. Append to string variable
Inside the loop, the Append to string variable action is used to accumulate a list of items due for each user. This includes the HTML table markup.
15. Send me a reminder email
Finally, outside the loop, the Send me an email notification action combines more HTML with the table rows created in the Append to string variable to create the full email body of the reminder.
16. Couldn’t send a reminder
In case any errors occur, an error handling mechanism is used. This will send you an email if the flow results in an error.
This action is set to only run if there is an error within the previous Scope action by using the Configure run after settings.
Change Flow to Email Specific Users
This flow as is will only send an email reminder to yourself, but you might want to send to email to other users as well.
The flow can be edited to achieve this by replacing the Send me an email notification action named Send me a reminder email with another email action such as the Outlook connector Send an email action.
This way, you can set the To field to your required audience.
You can create flows to send email reminders based on dates within a SharePoint list with just a few clicks.
Using the Set a reminder feature from SharePoint is an easy way to quickly create reminder flows based on dates.
This automatically creates a flow that will send you an email with all the upcoming list items. This can also be customized as needed to send emails to other users.
Did you know about this feature in SharePoint? Have you used it before? Let me know in the comments!