Sending a Reminder Email from a SharePoint List

[ScottShearer]

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

[EricX]

Hello ScottShearer

I used your solution to create email reminder flow. But failed to get the DueDate value in "Send an email" action. Do you know why?

‎

[llrinct]

@ScottShearer 

I came across this solution today and found the same problem as the user above...I have no DueDate available to use even though it is in my SharePoint list.  Do you know why?

[asd2525]

@llrinct 

Without seeing your actual list I can't say why it is not showing up but when that happens to me I will just hard code it in with an expression. In this case you would just use

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

Hope this helps.

[ERD]

Thank you for this tutorial!

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? 

[llrinct]

@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? "

[ERD]

@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)

[cander23314]

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!

[jcope]

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.

[MattB5]

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?

[JoaoHenriques]

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!FlowerrorFlowdetails

[chintu94]

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.'."

[asd2525]

@chintu94 

You forgot to add the utcnow and the number of days. The equations should look like this addDays(utcNow(),7,'M/d/yyyy') 

Also I believe you don't want all the letters capitalized for the date format.

[chintu94]

Thank you, i will try to fix as suggested

[cgrath]

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: