cancel
Showing results for 
Search instead for 
Did you mean: 
ScottShearer

Sending a Reminder Email from a SharePoint List

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

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?

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

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

Helper I

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? 

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

Helper I

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

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

 

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.

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?

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

 

 

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

 

 

 

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

Thank you, i will try to fix as suggested

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

 

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/