Send email reminders from Microsoft Lists using Power Automate

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.

Customize Issue Tracker from Microsoft Lists

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

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

Build a scheduled flow

Tip: Use the advance options to set your target time zone.

Recurrence advanced options

Click “New step”, search for “Variable” and select the “Initialize variable action.  

Set the “Name” to “varNumDays”; “Type” to “Integer” and “Value” to “30”.

Initialize variable

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

Initialize variable

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')}'

SharePoint Get Items

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.

Send an email action

My completed Flow looks like the image below.

My completed Flow

Save and test the Flow. If you have items due in 30 days the owner will receive a reminder email.

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

49 thoughts on “Send email reminders from Microsoft Lists using Power Automate”

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

      Like

      Reply

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

        Like

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

        Like

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

        Like

  3. 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!

    Like

    Reply

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

      Like

      Reply

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

        Like

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

        Like

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

        Like

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

        Like

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

    Like

    Reply

  5. 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?

    Like

    Reply

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

        Like

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

    Like

    Reply

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

      Like

      Reply

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

    Like

    Reply

    1. Hi Boris,

      The SharePoint Get items action ODATA filter should be:

      DueDate le ‘@{utcNow()}’ and Status ne ‘Completed’

      I hope this helps.

      NY

      Like

      Reply

  8. 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 🙂

    Like

    Reply

      2. 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 🙂

        Like

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

    Like

    Reply

      1. 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?

        Like

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

    Like

    Reply

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

      Like

      Reply

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

        Like

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

        Like

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

        Like

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

        Like

  11. 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).

    Like

    Reply

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

      Like

      Reply

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

    Like

    Reply

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

    Like

    Reply

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

      Like

      Reply

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

    Like

    Reply

    1. Hi Bhalchandra,

      Try putting “Expirydate” into a variable and try the OData filter again. Let me know if that worked.

      Thanks for reading.

      NY

      Like

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s