In this blog post I will show how to send email reminders for Microsoft Lists items based on a date column using Power Automate. I am using a customized version of the Microsoft Lists Issue Tracker template. See Microsoft Lists: Updated Issue Tracker for more info.
Automated email reminders give users time and opportunity to intervene in business process prior to expiration or end dates. In this example we will base our Power Automate Flow on the “Due Date” column and will send reminders 30 days in advance of the date.
Create a new Flow by clicking “Automate” > “Power Automate” > “See your flows”.
Click “New” > “Scheduled-from blank”.
Provide a Flow name, I.e. “Issue Tracker – Daily”, set “Starting” to “10:00 AM”, “Repeat every” to “1”, “Day” and click “Create”.
Tip: Use the advance options to set your target time zone.
Click “New step”, search for “Variable” and select the “Initialize variable action.
Set the “Name” to “varNumDays”; “Type” to “Integer” and “Value” to “30”.
Click “New step”, search for “Variable” and select the “Initialize variable action.
Set the “Name” to “varReminderDate”; “Type” to “String” and “Value” to the following expression:
addDays(utcNow(), variables('varNumDays'), 'yyyy-MM-dd')
Click “New step”, search for “SharePoint” and select the “Get items” action. Note: Microsoft Lists is really SharePoint, so that is why we are using the Power Automate SharePoint actions.
Set the “Site Address” and “List Name” to you target site and list.
Use the “Advanced options” to set “Filter query” to the following ODATA filter query:
DueDate eq '@{variables('varReminderDate')}'
Click “New step”, search for “Office 365 Outlook” and select the “Send an email (V2)” action.
Set “To” to “Person or group the issue is assigned to Email”. Power Automate will put this into a “Apply to each” loop so it can send multiple emails based on the previous “Get items” action.
Set “Subject” and “Body” to something similar to the image below.
My completed Flow looks like the image below.
Save and test the Flow. If you have items due in 30 days the owner will receive a reminder email.
This Flow is relatively simplistic in design and function but illustrates how value can be added to existing business processes by automating tasks and giving users an opportunity to act prior to a due date.
Thanks for reading.
NY
Hello, How do you add status as well as the due date? For example, if the status is complete It should not send a reminder.
LikeLiked by 1 person
Great question and something I should have included in the Flow.
Update the SharePoint Get items action Filter Query to:
DueDate eq ‘@{variables(‘varReminderDate’)}’ and Status ne ‘Completed’
I hope this helps and thanks for reading!
NY
LikeLike
How could I send an email to the person that was selected in the Assigned to field. This would mak the program way more useful.
Thanks in advance
LikeLike
It is currently set that way. In the “Send an email” action the “To” value is set to “Person or group the issue is assigned to Email”. Let me know if I can further clarify.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm,
I managed to expand your sample further by providing a link back to the item so the person being notified can opt to change the Status flag. This brings me to a new challenge: Restricting the respondent to just the Status column – i.e., he shouldn’t be allowed to edit any other column. I’m thinking of creating a SharePoint Group where members are the only ones allowed to edit the Status column – is this possible? Could you please show me an example?
Thanks in advance. You’ve been most helpful.
LikeLike
Hi Ricardo,
Unfortunately, column permissions are not possible with Lists. You can hide columns using a custom view and new/edit form or PowerApps but at the end of the day, you are just hiding columns and not securing them.
I’m sure there are ways to work around the issue but none of which will simple.
Sorry I couldn’t help more.
NY
LikeLike
Hi Norm, I’m trying to create the same flow for Work Progress Tracker rather than Issue Tracker but I encounter 2 different obstacles as follows:
1. There’s no option to select “Person or group assigned the issue is assigned to Email”
2. The formula – “DueDate eq ‘@{variables(‘varReminderDate’)}’ and Status ne ‘Completed’” don’t seem to work after adding “and Status ne ‘Completed'”
Let me know if you need more info. Thanks so much for this guide!!
LikeLike
Hi Yaw,
For issue #1 use the “Assigned to” column.
For issue #2 replace “Status” with “Progress” and it should work.
Thanks for reading!
NY
LikeLike
Hi Norm,
Trust you are doing well.
Seeking your guidance while I am motivating young developers in the ICT & QA team in achieving a needful feature in the Weekly Call Tree List, they have created with MS Power Automate. The staff receives an outlook email that has link to the Power Automate List on Sharepoint where one has to create a new listing with certain mandatory information on each Monday which is monitored by the security for the staff welfare during the lockdown working from home.
As staff members might not act on this mail instantly on a hectic Monday, I understand there is a need to put to “enforced Flag for Recipient Reminders tick marked” just like we can do in Outlook mail under Message > Follow Up tab inputting a certain time like 4PM staff must come open this mail weekly call tree mail and take requested action. I believe this reminder is achievable in Power Automat generated weekly call tree mail. as much I read your articles on the subject. I would be delighted to receive your contextual guidance which will be of great inspiration to my colleagues not to give up.
Warm Regards,
United Nations, RSC, Entebbe Uganda
LikeLike
Hello Mohamed,
Thank you for your message. You may want to consider using the “Flag email” action in Power Automate. This will require the Message ID from the sent messages. Use the Get Messages actions to get the Message ID.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm, I have added the and Status ne ‘Completed’ – my field is called status and I have a completed option, but it is still sending reminders to users even though they have completed their actions.
LikeLike
Hi Sumayya,
Make sure the case if the same in the column and the ODATA filter.
You can share screen shot to ny at normyoung dot ca.
NU=Y
LikeLike
Thanks!
LikeLike
How can I send an email automatically when a new issue is assigned to a specific person?
Not a reminder that their action or issue is due on xx days. But something like: “A new issue has been assigned to you” from the moment I added a new issue. Currently, they don’t receive notifications when I assigned task to them. Thank you!
LikeLike
Hi Fox,
Create a new automated Flow using the “When an item is created” trigger. Add a “Send an email” action and set “To” to “Person or group the issue is assigned to Email”.
I hope this helps and thanks for reading.
NY
LikeLike
That worked! Thanks!
LikeLiked by 1 person
Hi Norman,
Is there a way to change “From (Send as)”? As of now, by default it will come from me. Is it possible to change that to whoever logged the issue?
Thanks,
Fox
LikeLike
Hi Rahima,
Yes, you can two different ways.
1) Use the “From (Send as)” option in the “Send an email (V2) action but be aware that you will need permission to use this account.
2) Create a “service account” for your Flow (connections) that that will be the default sender. This is my preference for a number of reasons including that it can be configured not to have a password expiration policy, shows recipients that the message is system generated etc.
I hope this helps and thanks for reading!
NY
LikeLike
Hi Norm, for the ‘Link to Item’ part of the email would it be possible to link to a tab in a channel in MS Teams? I run my Issue Tracker in a channel tab under the MS Lists app in Teams.
LikeLike
Hi Sean,
I don’t think you can pull the Teams version of the Lists link dynamically using the out-of-the-box functionality. I think you could kludge it together using Graph API calls but I don’t know if it would add value to the user’s experience. The Lists experience across SharePoint, MS Lists and MS Teams are not consistent yet so there is jumping between apps.
It’s a great idea and I know my users would appreciate a consistent experience.
Thanks for reading!
NY
LikeLike
Hi
love the article as it has provided a much needed example of real flow.
Question more in context of the Issue Log Tracker – how to do stop the count of number of days outstanding (Days Old) when the status is complete? At the moment in my tracker the count keeps going even though the item is complete.
Thanks
LikeLike
Hi Amardip,
Check out this blog post on how to change the dates old column: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/
Thanks for reading!
NY
LikeLike
Hi Norm, when I add my column to the Get Items step, I get the following error message “Column ‘DueDate’ does not exist. It may have been deleted by another user.” The column does exist, so is there a reason flow thinks it doesn’t?
LikeLike
Hi Chelsea, “DueDate” is a custom column that I added to the Issue Tracker template. You can see all of the customizations in this article: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/
I hope that helps. Thanks for reading!
NY
LikeLike
Hi Norm,
I must be missing something but I’ve set the column as listed in the link and I’m still receiving the same error as Chelsea.
Thank you for this tutorial.
LikeLike
Hi Ron,
Make sure that the “DueDate” column spelling in the OData filter matches the internal column name for “DueDate”. If you don’t see DueDate then create it using this post: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/.
If that doesn’t help, let’s book some time to sort this out.
https://calendly.com/norm-young/sharepoint-power-automate-help
Thanks for reading.
NY
LikeLike
Hi Norm, I tried following logic but looks like I am doing something wrong. For example we have 12 reminders to be sent tomorrow to different people (some are repeating so 4 people needs to get their reminders). When I add these emails they all get reminders, for their issues and from others. is there a possibility that person responsible gets reminder for their issues only? and not all what is due that day?
Thanks for helping
LikeLike
Hi Borisa,
Make sure that varReminderDate is set correctly and the SharePoint Get items action has the OData filter query set to: DueDate eq ‘@{variables(‘varReminderDate’)}’
Also, make sure that the “Person or group the issue is assigned to” column only has the person responsible listed.
If that doesn’t help, let’s book some time to sort this out.
https://calendly.com/norm-young/sharepoint-power-automate-help
Thanks for reading.
NY
LikeLike
Hi Norm,
big thanks for support, I managed to realize what were the issues. I had 2, first was related to Date wasn’t formatted like in your guide, and 2nd was some email issue in my responsible column. With this fixed reminders are working.
I am now moving to next task which is to send reminders to items that are already passed. For example for items that had Due Date before today. In both cases after status is ‘Completed’ it shouldn’t send any more reminders.
Borisa
LikeLike
Hi Boris,
The SharePoint Get items action ODATA filter should be:
DueDate le ‘@{utcNow()}’ and Status ne ‘Completed’
I hope this helps.
NY
LikeLike
Hi there,
Just a question, while what you have covers most of what I am looking for, do you know how I would go about doing a summery email with all the tasks that person has due that day. Some may have quite a few tasks due on one day and it would be nice to have then all in one email and not send out 4 or so emails?
Thank you 🙂
LikeLike
I should add, one email per person with their tasks due listed.
LikeLike
Hi Daniel,
Check out this article: https://normyoung.ca/2020/12/21/send-a-single-reminder-for-multiple-items-using-lists-and-power-automate/
Let me know if that works for you.
Thanks for reading!
NY
LikeLiked by 1 person
Hi Norm,
Thank you for the link to the post, that is what I been looking for. I did run into a few issues but left a comment there 🙂
Thank you again 🙂
LikeLike
Hi, I’m working with Lists (app) in one of my Teams channel, what do I need make my list shoot emails – i.e., implement the above in Teams? Thanks.
LikeLike
Hi Ricardo,
You can follow the steps outlined in the blog post and it will work in Teams. Be sure to get the underlying SharePoint site URL from the Team by going to the Files tab and then clicking the Open in SharePoint button.
If your requirements are simple you may want to consider using List Rules. Check out this blog post: https://normyoung.ca/2021/01/21/a-first-look-at-rules-in-microsoft-lists/
Thanks for reading!
NY
LikeLike
Hi again Norm,
I couldn’t find the “Automate” menu in my Teams-channel tab – the interface is not the same as the one in your blog. Were you using a separate product/service that I should subscribe to?
LikeLike
Hi Ricardo, perform the steps from SharePoint or Lists and you should be fine.
Norm
LikeLike
Hi Norm,
Firstly, my thanks for all the great info – I am just beginning to wade into SP Lists, Flows, etc, so this blog is extremely helpful!
Is this flow specific to Issue Lists, or can it work against a Work Progress Tracker List? It instantly fails with the following message: “The expression “DueDate eq 2021-03-06″ is not valid. Creating query failed.”
(Incidentally, my original requirement was to send reminder emails beginning 5 days before the due date – I will check other blog posts of yours to not send them if the status is Completed.)
Thanks in advance!
Scott
LikeLike
Hi Scott,
The Flow was built around the MS Lists Issue Tracker template but can be easily updated to match other List designs.
Does your list have a DueDate column? If not, change DueDate to your column and go from there. If you share your List schema (column names, types) and the date column you want to trigger on I can provide more guidance.
Thanks for reading!
NY
LikeLike
Hi Norm – thank you very much for your reply,
My list does have a Due Date column (I created it after building the List using the work progress tracker) – after looking more closely, it seems the issue I am having is with the date itself.
After following all of your steps (I chose 5 days versus 30 days), when I run the flow, it fails on the filter query, as it appears to be looking for a date 5 days from now – I ran this on Feb 22nd: “The expression “DueDate eq 2021-02-27 and Progress ne ‘Completed'” is not valid.” (I use Progress, not Status).
When I write a very basic version of this flow (recurrence, get items, send mail), where the only thing in the query is Progress ne ‘Completed’, the flow runs fine – of course, I lose the ability to send reminders for items beginning within 5 days of being due (and continuing until Progress is marked Completed).
Any thoughts why I am not able to get the two going together? I tried adding less than/equal to 5 days, but that didn’t help lol.
Thanks for your help! I really appreciate it (we all do!). Be well –
Scott
LikeLike
Hi Scott,
In the post I was working on the assumption that a reminder would be sent once when the due date is 30 days from the current date.
Check your single quotes in the Flow. WordPress converts ‘ to ‘.
I hope this helps.
Norm
LikeLike
Hi Norm,
I believe I have solved my own issue – for my purposes, I used DueDate le and now the Flow only runs against the list items that are due in 5 days or less (or past due, if they have not been marked Completed).
The next thing I will probably try is creating a flow that adds emails to CC each day closer to the due date (as a form of escalation) – is that sort of flow possible, or would I have to create very specific flows ie for items due today, due tomorrow, etc?
Thanks again for all your help!
Scott
LikeLike
Hi Scott,
Yes its possible just use a date range in the filter query. DueDate ge “date range 1” and DueDate le “date range 2”.
Thanks for reading!
NY
LikeLike
I got a great tip for making sure the column name is correct — always go to list settings and copy the column name directly from the URL. It covers name changes and the %20 typos
LikeLike
This is great. So easy to follow! One question I have: I have a row with multiple people in the “assigned to” column. Is there a way that each individual can check a status column saying that they’re done with the particular task so they don’t get the reminder email, but the others still do if they haven’t updated the status to completed?
If this doesn’t work, can I add an extra column that works as an exception column, so it’ll email “assigned to” except for those in the new exception column (folks from the “assigned to” column that completed the task).
LikeLike
Hi Bob,
Thanks for those kind words, I appreciate it!
You would need columns that represent the task and the owner. For example, Task1, TaskOwner1, Task2, TaskOwner2 and so on. The Flow would have to be expanded to include multiple “Get items” and “Send emails” for each Task and Owner. It’s not a hard Flow to put together, just repetitive and assumes that the process has the same (or less) number of steps.
You could build something more dynamic but that would lead to more complexity that may outweigh the effort of a “quick and dirty” Flow.
I’m happy to help further if you provide more details on the process.
Thanks for reading!
NY
LikeLike
I am getting: The expression “DueDate eq ‘2021-03-07” is not valid. Creating query failed.
I don’t know where that actual date is coming from as I don’t have that in the DueDate field.
LikeLike
Hi Brian,
A couple things. I updated the Issue Tracker to include a due date. It’s called out at the start of the article: https://normyoung.ca/2020/08/11/microsoft-lists-updated-issue-tracker/. It looks like the single quotes are wrong. ‘2021-03-07‘ vs ‘2021-03-07’. WordPress is converting quotes from ‘ to ‘.
Let me know if you need more help and thanks for reading!
NY
LikeLike
Great post. covers all the bases and easy to red
LikeLike
Thank you, Patricia and thanks for reading!
NY
LikeLike
Hi Norm, have found this really useful. However, after following the instructions I get the following error message when testing the flow – The expression “DueDate eq ‘DueDate eq’2021-03-31”” is not valid. Creating query failed.
clientRequestId: 55f0b83a-dcda-40de-bf8c-f45ad6c01dc7
serviceRequestId: 55f0b83a-dcda-40de-bf8c-f45ad6c01dc7
Any help on this would be really appreciated.
Thanks
LikeLike
Hi TM Robinson,
The expression should be DueDate eq ‘2021-03-31’ where the date should be the variable created in the previous step. The single quotes are very important. Unfortunately, WordPress has been converting ‘ into ’.
Let me know how you make out and thanks for reading!
NY
LikeLike
Hi Norm,
I tried to run the flow using calculated Expiry Date , but shows following error
The field ‘Expirydate’ of type ‘Calculated’ cannot be used in the query filter expression.
LikeLike
Hi Bhalchandra,
Try putting “Expirydate” into a variable and try the OData filter again. Let me know if that worked.
Thanks for reading.
NY
LikeLike
Hi Norm
Facing the following error, could you pls help
The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Get_items’)?[‘body/value’]’ is of type ‘Null’. The result must be a valid array.
LikeLike
Hi Abhishek,
Make sure you are getting results back with the SharePoint Get Items action.
Thanks for reading!
NY
LikeLike
hello! I am getting lost on the step “Set “To” to “Person or group the issue is assigned to Email”. Power Automate will put this into a “Apply to each” loop so it can send multiple emails based on the previous “Get items” action.”
What specific actions do I take? I’ve tried typing in “Person or group the issue is assigned to Email” in the To field, in the Dynamic content field, in the Expression field – and coming up blank. Also in the Subject and Body, can you please provide what I should copy and paste and into where?
LikeLike
Hi Dave,
The “Person or group the issue is assigned to Email” comes as a result of the SharePoint Get Items action. If your List does not have the “Person or group the issue is assigned to” column you won’t find it in the results. You can substitute with any Person column of your choosing.
I hope that helps and thanks for reading!
NY
LikeLike
Hi Norm, what if instead of an email for each task we would like to group all tasks in one table and send it to the person responsible?
LikeLike
Hi Perdo,
Great idea! Check out this post: https://normyoung.ca/2020/12/21/send-a-single-reminder-for-multiple-items-using-lists-and-power-automate/.
Thanks for reading!
NY
LikeLike
Hi Norm, many thanks! This was exactly what I was looking for!!
LikeLiked by 1 person
Hello Norm,
Great flow! I am trying to test it and getting an issue that says:
“The expression “Project End Date eq ‘2021-07-24′” is not valid. Creating query failed.”
Project End Date is the date column in my SharePoint list that I want Power Automate to pull from. Do you happen to know why this error is occuring?
LikeLike
Hi Peter,
Use the internal column name. I’m guessing it’s ProjectEndDate. Also make sure your single quotes are ‘.
Thanks for reading!
NY
LikeLike
Thanks for your quick response!
Now it says:
Column ‘ProjectEndDate’ does not exist. It may have been deleted by another user.
The column in my sharepoint list is “Project End Date”. Not sure why it won’t pick it up. Any thoughts?
LikeLike
Hi Peter,
Flow can’t find the column. Go into List settings and confirm the internal name by clicking the column and look for “Field=” at the end of the URL path.
I hope this helps.
NY
LikeLike
Hi Norm,
My flow does not trigger automatically, I need to manually use the Test function each time and then it works. I have sent the value for “At these hours” 10 so in my preview, it shows “Runs at 10:00 every day”. Please help, thank you.
LikeLike
Hi Rodney,
That is odd behaviour. Are you using the Recurrence action as described in the article?
Thanks for reading!
NY
LikeLike
Hi Norm,
Thank you for your reply, it is working now. Followed your article, works well. Thank you so much!
LikeLike
Hi Norm,
Like some others, Im getting this error:
The expression “Retest_x0020_Date eq ‘addDays(utcNow(), variables(‘varNumDays’), ‘yyyy-MM-dd’)'” is not valid. Creating query failed.
The column name is copied form the end of the column URL.
Any ideas for me?
LikeLike
Hi Paul,
In your ODATA filter, the single quote should be a straight single quote and not opening/closing single quotes. WordPress formats my straight single quotes to opening/closing single quotes.
Try that and let me know how you make out.
Thanks for reading!
NY
LikeLike
Hi Norm,
I’ve made sure they are straight single quotes and I’m still getting the same error.
Does the ‘yyyy-MM-dd’ format in the expression make a difference if my column settings say “Enter date in D/M/YYYY format” ?? I’ve tried changing the expression to ‘dd-MM-yyyy’ and it doesn’t work either
LikeLike
Hi Paul,
What are you regional settings so I can test on my end?
NY
LikeLike
Hi Norm,
Time zone: (UTC + 12:00) Auckland, Wellington
Locale: English (New Zealand)
Sort Order: General
Calendar: Gregorian
LikeLike
Hi Paul,
I tested with the same settings and it works as expected. You should be able to specify any date format so long as it meets these reqs: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings.
Are you adding/clicking the variable or typing in the variable name?
NY
LikeLike