web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Blogs / Power Automate Community Blog / Sending a Reminder Email fr...

Sending a Reminder Email from a SharePoint List

Views (52849)
ScottShearer Profile Picture ScottShearer 25,290 Most Valuable Professional

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:

image.png

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.

image.png

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

image.png

 

Finally, I loop through all of the items returned and send an email to the POC.

image.png

 

Here is my Flow from Top to bottom:

image.png

 

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

*This post is locked for comments

  • cgrath Profile Picture cgrath 14
    Posted at
    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: 

     

    cgrath_0-1680208947543.pngcgrath_1-1680208986309.png

     

  • chintu94 Profile Picture chintu94
    Posted at
    Sending a Reminder Email from a SharePoint List

    Thank you, i will try to fix as suggested

  • asd2525 Profile Picture asd2525 68
    Posted at
    Sending a Reminder Email from a SharePoint List

    @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 Profile Picture chintu94
    Posted at
    Sending a Reminder Email from a SharePoint List

    Based on the instructions above, i created the below workflow and got error

    chintu94_0-1677873941041.png

     

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

     

    chintu94_0-1677872630416.png

     

     

     

  • JoaoHenriques Profile Picture JoaoHenriques
    Posted at
    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!FlowerrorFlowerrorFlowdetailsFlowdetails

     

     

  • MattB5 Profile Picture MattB5 9
    Posted at
    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?

  • jcope Profile Picture jcope
    Posted at
    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.

  • cander23314 Profile Picture cander23314 2
    Posted at
    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!

     

    cander23314_0-1657303832119.png

     

  • ERD Profile Picture ERD
    Posted at
    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)

  • llrinct Profile Picture llrinct
    Posted at
    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? "