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