Sending a Reminder Email from a SharePoint List
ScottShearer
25,270
Most Valuable Professional
Like
Share
ReportMany Power Automate developers have requirements to send out reminder emails a number of days before or after a date stored in a SharePoint list. For example, a list might have columns for Due Date, POC and Status. The requirement is to send an email to the POC one week before the due date if the item status is not complete. The following will explain how to meet this requirement.
Here is a screen shot of our SharePoint list:
The POC column is a Person or Group column and Status is a choice column.
I'll start my Flow with a recurrence trigger set to fire once a day.
Next, I'll use a SharePoint Get items action. My Get items action uses a Filter query.
Here's what you need to know about the Filter Query:
- The purpose of the Filter Query is to limit the items returned to those that meet our criteria
- The SharePoint column name is the internal name. The internal name may be different than the display name (the name you see). Internal names never have spaces. The best way to determine a columns internal name is to examine the output from your Get items action and see how the column name is referenced - that is the internal name.
- I use the following operators in my query:
- "eq" for equals
- "and" in order to combine two conditions
- SharePoint stores dates in the following format: yyyy-MM-dd
- I need to make sure that the date to which I compare the date stored in SharePoint is formatted the same way
- I use the addDays() expression to add 7 days from the current date and format the date
- I use the utcNow() expression to provide the current date
- The expression that I use can be seen in the action comments
- Note the single quotes around my parameters
Finally, I loop through all of the items returned and send an email to the POC.
Here is my Flow from Top to bottom:
Please note that SharePoint stores all dates as UTC dates and you may need to account for your time zone.
For more information on using OData filters with Power Automate and SharePoint, see this blog post from Pieter Veenstra.
Comments
-
Sending a Reminder Email from a SharePoint List
I'm new to power automate. I tried to set up the flow as described. But I get this error when I test it. "The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each_2')' is of type 'Object'. The result must be a valid array."
My flow looks like this:
-
Sending a Reminder Email from a SharePoint List
Based on the instructions above, i created the below workflow and got error
I get following error message when i run the flow, not sure how to resolve it, could you please help?
"Unable to process template language expressions in action 'Get_items' inputs at line '0' and column '0': 'The template language function 'addDays' expects a timestamp as the first parameter, the number of days to add as the second parameter, and an optional format string as the third parameter. The function was invoked with '0' parameter(s). Please see https://aka.ms/logicexpressions#adddays for usage details.'."
-
Sending a Reminder Email from a SharePoint List
Hello thanks for this great help!
I have tryied to run that to track team action plans in teams list. POC or Responsible persion is set to "Person" type collumn so i can pull the email from each.
When i test the flow i got the bellow error message and i do not know how to over cross it... can you help?
I want that 3 days befor action is due, the POC or Responsible for action receives an reminder email.
Below is my flow error when testing and flow details.
Many thanks!
Flowerror
Flowdetails
-
Sending a Reminder Email from a SharePoint List
Hi! Thanks for this great tutorial. I have one question:
Does the 'Due Date' column have to be formatted as date only? I used this tutorial to create a flow to remind people to update a column in my list with the actual number of participants in an event (the list is a record of planned events). At the time when I implemented this flow, the 'End date' column was formatted as date only. But then, I was asked to change the formatting to date and time so that the Calendar View would show more details about the duration of the event (this is not available if you format the Start and End dates for an event as date only). Since then, the flow stopped working. I guess this is because of the change in the format, but would you have any idea how to solve this issue?
-
Sending a Reminder Email from a SharePoint List
How would I structure the addDays expression so that two email reminders are sent, at 90 days AND at 180 days after the date stored in the SharePoint List? @ScottShearer any help would be greatly appreciated.
-
Sending a Reminder Email from a SharePoint List
Thanks for this nice example. I have it implemented and working, but when I'm not able to get it working with multiple POCs. Any feedback on what to try? The "To" field contains "Notification Party Email" which is from the column containing multiple POCs. Thanks!
-
Sending a Reminder Email from a SharePoint List
@llrinct I found a solution yesterday! I followed this blog post and it worked great.
Send one email per user with multiple SharePoint items in Power Automate (tomriha.com)
-
Sending a Reminder Email from a SharePoint List
@ERD I was going to ask the same thing.
If anyone could help, that would be great!
"Is it possible to combine the items for each person? So if a person has a number of overdue items in the list they only get 1 email instead of an email for each overdue item? "
*This post is locked for comments