Automatically Email Reminders from Excel with Power Automate

Mynda Treacy

February 24, 2022

We can automatically email reminders from Excel with Power Automate and there’s no programming required. You don’t even need to open the Excel file to trigger the emails to send!

In the video and tutorial below we’ll look at a scenario where we have a list of tasks, and we want to automatically remind those responsible prior to the task due date.

Note: Requires Microsoft 365 and either OneDrive for Business or SharePoint.

Watch the Video

Subscribe YouTube

Automatically Email Reminders from Excel with Power Automate Step by Step

Below is a screenshot of my task list which is in an Excel table called Tasks:

excel task list table

The reminder date is a set number of days before the task due date. I want to send an email to the manager containing a list of their tasks when the reminder date falls due.

Launch Power Automate and Schedule Flow

Step 1: Your data must be in an Excel Table. Use the keyboard shortcut CTRL+T to format your data in a table or go to the Insert tab > Table.

Step 2: Save your Excel file on OneDrive for Business or SharePoint. Note: it doesn’t work with OneDrive Personal accounts, and you need a Microsoft 365 account.

Step 3: Login to OneDrive or SharePoint in your browser and navigate to Power Automate. Tip: if you don’t see Power Automate in the list of apps, click on ‘All apps’:

Launch Power Automate

Step 4: click on ‘Create’ and then choose ‘Scheduled cloud flow’:

create Power Automate scheduled cloud flow

Step 5: Name your flow and set the schedule. I want the reminders to be sent out daily:

Set Power Automate flow name and schedule

Step 6: You’ll now have one step in your flow for ‘Recurrence’:

Power Automate Recurrence step

Connect to Excel File with Power Automate

Click on + New step and choose ‘List rows present in a table’. Tip: if you don’t see this option in the list, you can filter it by clicking on the ‘Excel Online (Business)’ icon first.

Add new Power Automate step

Step 7: Choose the location OneDrive for Business or SharePoint:

choose file location

If you choose OneDrive, then the Document Library is also OneDrive. If you choose SharePoint, then the Document Library field is automatically populated for you.

Next, navigate to the file location and select the file. Note: there is a limit to the number of folders that will display in this list, so you may need to create a folder that appears near the top.

Lastly, select the table name containing your data. Remember, mine is called Tasks:

Power Automate List rows present in a table step

Step 8: Open the Advanced Options and add an ODATA filter for the ReminderDate field. Start by typing in the Filter Query field:  ReminderDate eq ‘

filter query

Then click on ‘Add dynamic content’, go to the Expression tab and in the formula bar enter this formula:

formatDateTime(convertFromUTC(utcNow(), 'E. Australia Standard Time'), 'yyyy-MM-dd')

Note: You can edit the time zone to your own using the time zones names listed here.

Filter query based on date with Power Automate

Lastly, set the DateTime format to ISO 8601:

Power Automate Advanced Options list rows present in a table

IMPORTANT: Note the expression in the filter query is surrounded by single quotes.

Extract Distinct List of Emails

Step 9: Next we need to get a distinct (unique) list of emails so that each person only gets one email. We do this by adding a select step. Click ‘Add Step’ and in the search box type ‘select’ and choose it from the list of actions:

Power Automate Add dynamic content

Click on Add dynamic content and choose Value from the list:

Power Automate Select Action

Click on the Text icon:

Power Automate Select Action Text icon

And choose Email from the list. Tip: enter the column name in the search field to filter the list:

Power Automate Select Action dynamic content

Step 10: Add a new step to compose a list of distinct emails. Tip: type ‘compose’ in the search filed to reduce the list of actions:

Power Automate Compose Action

Click on ‘Add dynamic content’ and go to the Expression tab and enter union()

Power Automate Union

Then click on the Dynamic Content tab to the left of the Expression tab and select ‘output’ from the list, then enter a comma and select ‘output’ again. You should have a formula like this:

Power Automate Compose union

Click OK to complete the formula for Compose.

Step 11: add a control

Power Automate add a control

And select ‘apply to each’:

Power Automate control apply to each

Click on ‘Add dynamic content’ and select ‘Outputs’ from the Compose step:

Power Automate apply to each

Extract Data for Email from Each Row

Step 12: Add an action:

Power Automate add filter action

To Filter array:

Power Automate add filter array

Click on ‘Add dynamic content’ and choose ‘Value’:

Power Automate filter array dynamic content

In the next fields choose ‘Add dynamic content’ and select ‘Email’ and ‘Current item’ as per the screenshot below:

Power Automate filter array current item

Generate HTML for Email

Step 13: Add an action to create a HTML table:

Power Automate create html table

In the From field enter dynamic content from the Filter array body:

Power Automate dynamic content for html table

Then ‘Show advanced options’ and change the Columns to Custom:

Power Automate html table custom columns

Enter names for the columns that you want in your table names in the Header column. And in the Value column enter expressions for the column names as they appear in the table using the item function like so:

item()['ColumnName']

Power Automate html table column expressions

Note that date fields need to be wrapped in the formatDateTime function otherwise they will appear as a date serial number in the email.

Create Email

Step 14: Add an action for sending the email. I’m using Office 365 Outlook:

Power Automate email action

Choose Current Item from Dynamic content.

Power Automate email dynamic content

Add a subject and then add dynamic content for the body by inserting the Output of the Create HTML table action:

Power Automate email current item

Click ‘Save’:

save Power Automate

Your completed flow should look like this:

Power Automate steps

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

5 Pro Excel Dashboard Design Tips

Combine Files With Different Column Names in Power Query

195 thoughts on “Automatically Email Reminders from Excel with Power Automate”

  1. Hi there,
    Thank you so much for this tutorial! I have followed it to what I believe to be exactly, and the flow works fine.
    I have a CC column in my excel table and I want to put that field in the CC email send option but it is not working. Also is there a way we can put person’s name in the email body, e.g. Dear John, ?
    Thanks.

    Reply

  2. Hi Mynda,

    I am hopeless with technology but trying to set up a flow so it sends automatic email from excel on expiry date(I have put 1 month before the actual expiry date in excel) of reagents.
    I have reagent name, expiry date and email in my excel table.
    Since my table needs no “composing”, I did not add this step. (or do I need this step anyways? or what does composing actually do???)
    When I try running a test, it fails at “List rows present in a table” step and I cant figure out why.
    Error says “Syntax error at position 21 in ‘Expiry eq’2025-02-18”. inner exception: Syntax error at position 21 in ‘Expiry eq’2025-02-18”.clientRequestId: eb6d4da1-239b-4fe5-a89a-212f5d6af568″
    Would you know why it keeps failing?
    Sorry probably I do not make too much sense. I have been doing this for like 3 days and cant get it to work!

    Reply

  3. I’ve tried this out and finally got it working. As many people apparently I couldn’t get the filter with ReminderDate eq ‘ condition ‘ to work . Removing the filter worked, but couldn’t get it to work with the filter condition.

    So, for what it’s worth : after lots of trial and error (really lots ! tried all kind of things ) I added a Compose step before loading the data which I called ComposeCurrentDate: formatDateTime(convertFromUTC(utcNow(), ‘W. Europe Standard Time’), ‘yyyy-MM-dd’) (using W European time)
    I then refered to that compose in the filter step of the Action to ‘List rows present in a table’ :
    ReminderDate eq ‘@{outputs(‘ComposeCurrentDate’)}’
    And bingo – thats worked…

    ps: form not working in Opera

    Reply

  4. Awesome tutorial! I’ve been curious about the Excel tasks that can be assigned using the @mention in cell comments. I’m curious if you have any content available in tutorials or your courses that cover using those in advanced scenarios? In my mind I’m thinking of generating a dynamic task list table by pulling in tasks from any of my @mention tasks regardless of what workbook it is in and then using this tutorial to automate reminders.

    I know there was some mention of Microsoft eventually incorporating these @mention tasks into ToDo or Planner but its been years and still nothing, so its up to us now to fill in the gaps 🙂

    Reply

    • Hi Matt,

      AFAIK you can’t extract information from comments in Excel, however if you @metnion someone AND assign a task to them, it will appear in their Planner under My Tasks > Assigned to me.

      Mynda

      Reply

  5. Hello. Is it possible to send an email only to tasks marked as in progress in a status column? Do you have the steps for that? thank you

    Reply

  6. Hi Mynda,
    I’ve 02 columns for Email Id’s and the Mail ID’s in second column are for escalation mail.
    I want to send the mail to the second column Email Id’s while keeping the first column Email Id’s in CC. May you please guide me on this?

    Reply

  7. Is there a way i could automate the reminder email base on the following rules:

    keep send the reminder emails on daily basis until the recipient has completed task on certain link given.

    eg: reminder date :16/9/2023. However, recipient only complete the task given and self declared on 20/9/2023. Reminder emails should be sent out on daily basis from 16/9/2023 until 19/9/2023. It would stop send out reminder email on 20/9/2023 onwards.

    Thanks in advance for your guidance.

    Reply

  8. I love your teaching style on Power Automate. Wonder if you do have provided the subscription virtual course for Power Automate? I eager to learn more from you 🙂

    Reply

      • Hi Philip Treacy,

        Glad to inform that i managed to run successfuly and received notification emails, after clicked “Test Manually”.

        Question – if i wish to let the tool auto detect future reminder dates and auto send out daily email notification in accordance to future dates given in “Reminderdate” column, without the hassle of manually click “test manually” on daily basis, where is the setting i need to do? Thanks.

        Reply

  9. It is possible to add another column in excel for time? For example i have an event which will started at 1-1-2023, 10:00am, then the reminder email will send it before 15 minutes.

    Reply

  10. hi Mynda
    i’m stuck on step 7, when i try to insert my table i’m getting the below error

    Could not retrieve values. The dynamic invocation request failed with error: { “status”: 409, “message”: “Graph API is unable to acquire or refresh a lock on the file because it is already locked. Please close edit sessions and try again.\r\nclientRequestId: 8a933602-c3a7-4814-b244-c4c733dcb88c\r\nserviceRequestId: b887b625-e404-4ef9-8dad-cc9e5b9788ad”, “error”: { “message”: “Graph API is unable to acquire or refresh a lock on the file because it is already locked. Please close edit sessions and try again.” }, “source”: “excelonline-ukw.azconn-ukw-001.p.azurewebsites.net” }

    Reply

  12. Hi there,
    Thank you so much for this tutorial! I have followed it to what I believe to be exactly, and the flow works and I receive an email with the headers “DueDate” and “Task” but there’s no additional data as to what the due date or task is. Do you know maybe what is wrong, and which step of this workflow needs adjustment to get the actual item in the above-mentioned columns can be displayed in the email? Thank you!

    Reply

  13. Hi! Your tutorial helped me very much and everything works wonderful, so many thanks! I implemented this at my company and boss was so pleased. However, the task fails if there are no tasks due on that date. Can you please help me get around this? Thank you!

    Reply

  14. Hi Mynda,
    In my situation I want the reminder emails to go to a single person and I don’t have that person listed in the excel table. How can we adjust the steps so that it still collects all the data but then I simply add the person in the Send an email (V2) step

    Reply

  15. First of all, thank you very much for the help. I implemented it in the work team, but I have some doubts:
    1- How can I do it if I don’t have all the dates in the expiration column/ I have cells without data?
    2. When I want to add another data to be sent in the mail, the “Select” option is transformed to a cycle. How can I avoid it?

    Thanks in advance!

    Leandro López

    Reply

  16. Hi there,

    This was the best PA tutorial I have ever followed.

    Whenever I add the filter in the ‘List rows present in a table’ step, it runs successfully but generates no data or email. I tried with two different expressions in that field, please see below.
    ExpirationDate eq ‘formatDateTime(convertFromUTC(utcNow(), ‘Mountain Standard Time’), ‘MM/dd/yyyy’)’
    AND THEN
    ExpirationDate eq ‘formatDateTime(addDays(utcNow(),7),’MM/dd/yyyy’)’

    If I remove that filter query, then it runs and sends the email to the assigned person, but it sends for all on items on the excel sheet listed on their email and not just those due within 7 days.

    Any help is much appreciated!

    Reply

    • Hi Natalie,

      Glad the tutorial was helpful. Have you tried using the date format as per my formula i.e. ‘yyyy-MM-dd’ not ‘MM/dd/yyyy’?

      Don’t worry about the format in your file (assuming it’s a proper date serial number and not text), just make sure the format in the Power Automate formula is entered as I showed in the tutorial.

      Mynda

      Reply

      • Hi Mynda,
        I just tried that adjustment, but it did not work. I have two examples on my file listed with today as the expiration date too. I still see it as not including any data so does not generate an email.

        Yes. My date within my file is in date format. I have it in short date format. Is that ok?

        Thank you,
        Natalie

        Reply

      • Hi Mynda,

        I figured it out. I had to update my format for the time expression in the html table action.

        Next question – – – how do I update the expression in the filter query field so it sends the notification for any expirations in next 30 days?

        Thank you,
        Natalie

        Reply

  17. Flow save failed with code ‘InvalidTemplate’ and message ‘The template validation failed: ‘The inputs of template action ‘Apply_to_each_2’ at line ‘1 and column ‘3040’ is invalid. Action ‘Apply_to_each’ must be a parent ‘foreach’ scope of action ‘Apply_to_each_2’ to be referenced by ‘repeatItems’ or ‘items’ functions.’.’.

    pls help me with this error pls

    Reply

  18. HI Mynda,
    I am getting this error message when using the date for the filter query:
    The child type ‘T.NextBillingDate’ in a cast was not an entity type. Casts can only be performed on entity types.
    I can’t figure out what this means, as my code looks like your example.
    T.NextBillingDate eq ‘@{formatDateTime(convertFromUTC(utcNow(), ‘Eastern Standard Time’), ‘MM/dd/yyyy’)}’

    Reply

      • I found out by trying different things that it did not like the “.” in my field name. When I changed the field name to just “NextBillingDate” it worked as expected.
        I was also able to the date part of the query to select records with dates 7 days in the future to send my email ahead of time. I used this instead of the one above:
        formatDateTime(addDays(utcNow(),7),’MM/dd/yyyy’)

        Reply

        • Glad you figured it out, Lisa! As a general rule you should avoid using operators when naming anything (columns, sheets, files, cells etc.).

          Reply

  19. Hello i have this error

    here is an unterminated string literal at position 23 in ‘Reminder eq ‘2023-02-12’.
    inner exception: There is an unterminated string literal at position 23 in ‘Reminder eq ‘2023-02-12’.
    clientRequestId: 1b2525af-121f-4aea-906e-e9754608af71

    Reply

    • The error message you are encountering is related to a syntax error in the expression.

      It’s indicating that there is an unterminated string literal in the expression ‘Reminder eq ‘2023-02-12”. The position 23 is pointing to the start of the string literal ‘2023-02-12’, which is not properly terminated with a closing single quote.

      To fix this error, you need to add a closing single quote to the end of the string literal to properly terminate it. The corrected expression should look like this: ‘Reminder eq ‘2023-02-12”

      If you’re still having trouble, try checking the entire condition to ensure that all string literals are properly terminated and that the expression is correctly formatted.

      Reply

  20. I have the flow working but how can I add multiple due dates to the same task, such as a task that has a monthly due date?

    Reply

  21. I kept getting status code 400 and not sure how to fix this.

    “There is an unterminated strong literal at position 27 in ‘ReminderDate eq ‘2023-01-27’.\r\n Inner exception: There is an unterminated strong literal at position 27 in ‘ReminderDate eq ‘2023-01-27’.\r\n”

    Reply

  22. Hi
    I love all your videos and are well articulated. I tried replicating the above tutorial but on the 3rd step after adding List rows present in table, Select Data operation it does not present all the rows in the table. What can I do to fix this?

    Reply

  23. This is absolutely brilliant.
    Thank you so much.
    It took me a few goes before I got it working – stupid things like a space on the end of the column header so it couldn’t be found, or missing the step to make the email a text field.
    But eventually it worked and I was able to expand on it a little to make it suit my requirements.
    Thanks for not only the video but the steps in the description

    Reply

  24. Getting Error as :

    Syntax error at position 19 in ‘ReminderDate eq2023-01-11’.
    inner exception: Syntax error at position 19 in ‘ReminderDate eq2023-01-11’.
    clientRequestId: e0458996-a2c9-44dc-87cd-d52c0344471a

    Reply

  25. Please help getting below error
    Syntax error at position 13 in ‘Reminder Date eq ‘2023-37-10”.
    inner exception: Syntax error at position 13 in ‘Reminder Date eq ‘2023-37-10”.
    clientRequestId: a9f74c1b-fe21-4e4b-a2ad-1445c2b04416

    Reply

  26. Hi Mynda,
    I followed your instruction to create a flow to send out reminder emails to providers based on the reminder dates, it worked really well until two providers have the same reminder date. If two providers have the same reminder date, the flow will send 4 emails out, 2 are correct, 2 are incorrect with mismatch information of the other provider.
    For example:
    Provider A, reminder date: 09/10/2023, Provider [email protected];
    Provider B, reminder date: 09/10/2023, Provider [email protected];
    on the reminder date:
    Provider A receives two emails one with correct information, one with information of Provider B

    I feel that this is should be an easy fix?

    It would be great if you knew how to fix this, if not I will go to the power automate forum.

    Thank you very much for creating this page, it has helped me a lot with my work.

    Reply

  27. I need something far simpler.
    Excel data columns:
    Project Name, Company, Contact Person, Contact Email, Date Entered, Follow up date.

    I need an email sent to me, on the follow up date, to tell me to contact the Contact person, at contact email, regarding the Project, when the followup date column is “today.”

    I’m doing-
    Recurrence- every day,
    List Rows present in a table
    (have tried all the different reformatting of dates)
    And even though I KNOW I have 2 that I need to get this email on, I get ALL false results!
    I don’t know what I’m missing and I’ve tried about 17 different people’s helps on this site and many others.

    Reply

  29. Hi Mynda,

    Thank you so much for this detailed tutorial! I followed it and was able to successfully set up the flow (after some trial and errors that I was able to troubleshoot via comments here); however, I’m very inexperienced with expressions so I just want to make sure I understand what I’m filtering for in Step 8. Is it simply asking it to send the email to that person on that date that I have entered in the Reminder Date column?

    Also, I’m not sure what I should set my recurrence to. If I do ‘daily’ will it send the email every day or just on the date listed under ‘Reminder Date’? If I do weekly, will it pick up all the dates within that week or just the date that the flow is running? For example, if I have the dates listed of 2/19 and 2/21 and I run the flow on 2/20, will it send it to both?

    Thank you so much!

    Reply

  30. Hi, this looks like exactly what I need, but I’m getting an error I can’t seem to fix. The flow is failing in the Apply to Each phase with the following exceptions:

    The execution of template action ‘Apply_to_each’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Compose’)’ is of type ‘String’. The result must be a valid array.

    It looks like I need to change a String to an array, but I’m not sure how. Any thoughts? Thanks!

    Reply

  31. Thanks for your guidance, however i received below error message. Appreciate if can help.

    Action ‘List_rows_present_in_a_table’ failed

    Syntax error: character ‘‘’ is not valid at position 16 in ‘ReminderDate eq ‘2022-12-22’.
    inner exception: Syntax error: character ‘‘’ is not valid at position 16 in ‘ReminderDate eq ‘2022-12-22’.
    clientRequestId: d5e5b7a1-a63b-480e-be03-fbf7376a87d9

    Reply

    • Hi Lim,

      The error is telling you that you have a single quote in the wrong place or missing. Remember, you have to close the expression with a single quote. See the example image in the tutorial above.

      Mynda

      Reply

  32. hello Mynda,
    Great tutorial, I have been able to replicate the flow however, i keep getting this error :
    Action ‘List_rows_present_in_a_table’ failed

    Invalid filter clause: cannot find the ‘ReminderDate’ column.

    clientRequestId: 007f7eab-cd0a-419f-b81e-a284284fdf1c

    serviceRequestId: 45445e0b-188a- 4d4d-9d82- de8f6b89603d;8b3fa538 -f1e5-44a4- 9def-c51e767af1ed; bf61f0bd-eb46- 42fa-ab7c- b648ec886d11

    please help.

    Reply

    • Hi Justice,

      The clue is in the error message. It says it cannot find the ‘ReminderDate’ column. I suspect you don’t have a column with this name in your Excel file.

      Mynda

      Reply

      • i am getting the same error as Justice
        “Invalid filter clause: cannot find the ‘ReminderDate’ column.
        i have searched and researched how to find the real name excel names this column with no luck, any suggestions would be greatly appreciated. i have even deleted the column and created new with out changing the name given and im still getting the same error.

        Reply

        • The real name is the name you’ve given the column header. In the screenshots above you can see that my column (B) header is ‘ReminderDate’ with no spaces. I recommend you don’t have spaces or periods or any other operator characters in your column names.

          Reply

  33. i tried this exactly but it did not work it seems like it does not pull the email at all because when i check the json for select it shows nothing

    Reply

  34. Thank you for this tutorial! Super helpful and detailed!

    I can successfully run my flow but am not receiving an email? I’ve mirrored your table at the start of the video, but when the flow runs successfully, I do not get one. I’ve also double checked spelling.

    Any advice to troubleshoot would be much appreciated.

    Reply

    • If it’s identical in every way, then I’d say it has to do with the dates in the file. Note: you cannot use the TODAY() function to return a date relied upon by Power Automate.

      Reply

  35. Hi Mynda,
    this instruction is awesome! Thank you so much!
    Is there a way to get a reminder for all items that are either due today or in the past if the status is not “completed” or “cancelled” in my Excel table?
    Also, I added a link to some text in the email, but when I receive the email, there is no link. When I go back to the flow in edit mode, the link isn’t there anymore either. I have added the link and saved the flow again several times, but it does not work. Would you have any idea why that is?
    Thank you!
    Ellen

    Reply

  36. Hi, thanks for such an illustrative demo. After lot of attempts i could finally run my flow successfully however am not getting any emails yet. Any suggestions?

    Reply

      • I have used UTC as suggested, for GMT do in need to do something different

        I am getting [ ] in Select and Compose

        Below is the output for List Row Below Table

        {“statusCode”:200,”headers”:{“Pragma”:”no-cache”,”Transfer-Encoding”:”chunked”,”Vary”:”Accept-Encoding”,”x-ms-request-id”:”ec391189-2b21-4cfd-8bdf-33899178a677;5148f7f1-8283-4a0a-86fc-cea7e673cae7;14229e19-27fb-4385-9f97-9a3da01cca72;c314e42e-8205-490b-9b46-68833df41344;40e4e0b5-4689-408d-a7d1-61e9938d006f;f9c19cb0-693d-447d-af4d-e39671fe6238″,”OData-Version”:”4.0″,”Strict-Transport-Security”:”max-age=31536000; includeSubDomains”,”X-Content-Type-Options”:”nosniff”,”X-Frame-Options”:”DENY”,”Timing-Allow-Origin”:”*”,”x-ms-apihub-cached-response”:”true”,”x-ms-apihub-obo”:”false”,”Cache-Control”:”no-store, no-cache”,”Date”:”Thu, 01 Dec 2022 06:50:57 GMT”,”Content-Type”:”application/json; odata.metadata=minimal; odata.streaming=true”,”Expires”:”-1″,”Content-Length”:”294″},”body”:{“@odata.context”:”https://excelonline-wus.azconn-wus.p.azurewebsites.net/$metadata#drives(‘b%21sg-WwmD4-0C9qW-1_tkvzZXC-MOYeS9Ogt7ERGhDfmezJR_hANSxSYylZ-MZZpt_’)/Files(‘014WKUSOSWGYOY6FQKIFBYZ7YFCLGH2OL3’)/Tables(‘%7B20F2C41C-9DA1-412F-9375-9AAD3A587827%7D’)/items”,”value”:[]}}

        Reply

        • I didn’t suggest to change the time zone, merely that the Power Automate server may use a different time zone to you which may explain why you haven’t received an email yet. Please post your question in the Power Automate forum, as you’ll find more experts there that should be able to help.

          Reply

        • Hello Priya, any chance you have made this work already? I’m getting the same error. Flow is successful however there is no email received yet. I am getting [] in compose and select also.

          Reply

  39. Thanks for the tutorial and it was a great help.

    But I keep having errors which stated as follows,

    InvalidTemplate. Unable to process template language expressions in action ‘List_rows_present_in_a_table’ inputs at line ‘0’ and column ‘0’: ‘In the template language function ‘convertFromUTC’, the value provided for the time zone id ‘E. Myanmar Standard Time’ was not valid.

    My Input formula is as follows,
    formatDateTime(convertFromUTC(utcNow(), ‘E. Myanmar Standard Time’), ‘yyyy-MM-dd’) Is it because of the Time Zone? or my formula?

    Reply

    • Hi,

      There’s no such time zone ID as “E. Myanmar Standard Time”. Your time zone is just “Myanmar Standard Time” as per the link to the list of time zone names linked to in the post above. Therefore, your code should be:

      formatDateTime(convertFromUTC(utcNow(), 'Myanmar Standard Time'), 'yyyy-MM-dd')

      Hope that helps.

      Reply

      • Thanks for the help!!

        I changed the Time Zone as per your instructions and the error changes as follows.

        Syntax error: character ‘‘’ is not valid at position 16 in ‘ReminderDate eq ‘2022-11-03”.
        inner exception: Syntax error: character ‘‘’ is not valid at position 16 in ‘ReminderDate eq ‘2022-11-03”.
        clientRequestId: b0aad91b-b126-4671-8c69-29c0039627dd

        My Input formula changes into :
        formatDateTime(convertFromUTC(utcNow(), ‘Myanmar Standard Time’), ‘yyyy-MM-dd’)

        Another issue is when I try to build a new flow, ‘Add dynamic content’ Tab didn’t show up and I cannot input formula anymore.

        Reply

  40. It’s great and it helps me a lot, thank you.
    I still have an issue that I set up the reminder date as a formular according to the approval status, the reminder date always stays as TODAY so that I can receive the reminder every day until it is approved. but now I didn’t receive any reminder even the reminder date has changed to Today automatically, is it because of the formular template on the reminder date? could you help me on this? much appreciate !

    Reply

    • Hi Lily,

      The TODAY function only recalculates in the Excel file if the file is opened. Power Automate will only know if the the date has been updated if you open the file and save it again.

      Mynda

      Reply

  42. Syntax error: character ‘‘’ is not valid at position 15 in ‘ ReviewDate eq ‘ 2022-10-17 ”.
    inner exception: Syntax error: character ‘‘’ is not valid at position 15 in ‘ ReviewDate eq ‘ 2022-10-17 ”.
    clientRequestId: c6edc547-3953-43e6-b618-d4684f723f3e

    Reply

    • Hi Jack,

      The clue is in the error message. The single quote is not in the right position. Maybe there’s a space before/after it, maybe you copied the character from somewhere else and it’s not recognised. Try typing it in again.

      Mynda

      Reply

  44. I have modified my dates yet this still won’t work.

    ‘Note that date fields need to be wrapped in the formatDateTime function otherwise they will appear as a date serial number in the email.’

    What do you mean by the above statement? That my excel should be formatted to what?

    Reply

    • In the Create HTML Table dialog shown in step 13 I am formatting how the information in the email will appear. Dates in Excel are actually stored as date serial numbers, not dd/mm/yyyy or mm/dd/yyyy. If you want to see them in the email correctly formatted, then you need to use the formatDateTime function, as shown in the this image:

      Reply

    • This is the error, I keep getting
      Syntax error at position 27 in ‘ReminderDate eq’2022-09-27”.
      inner exception: Syntax error at position 27 in ‘ReminderDate eq’2022-09-27”.
      clientRequestId: 7e122428-36ed-4602-ade8-83031e060ccf

      Any help would be greatly appreciated.
      My Column is called ReminderDate on my excel.

      Reply

  45. I continue to get an error message for ‘List rows present in a table’ failed. It states “Syntax error at position 27 in ‘ReminderDate eq’2022-09-13”.
    inner exception: Syntax error at position 27 in ‘ReminderDate eq’2022-09-13”.”
    Am I to use the name of the column name on my

    Reply

    • Hi Connie,

      “ReminderDate” in the formula should be the name of the column containing your reminder date in your file. Note that this column name should not have any spaces in it.

      Reply

  46. I’m getting the following error details when I run my test:

    A digit was expected at position 5 in ‘2022ExpirationDate eq 2022-09-07’.
    inner exception: A digit was expected at position 5 in ‘2022ExpirationDate eq 2022-09-07’.
    clientRequestId: a2c3c2ad-e606-4196-af97-4777cd4306cb

    Here’s the formula I’m using:

    2022ExpirationDate eq ‘formatDateTime(convertFromUTC(utcNow(), ‘Eastern Standard Time’), ‘yyyy-MM-dd’)’

    Help is greatly appreciated!

    Reply

    • Make sure you don’t have any empty cells in the date column. Also, change the date format from yyyy-MM-dd to MMM d, yyyy. This is not the date format of your file, it’s the date format Power Automate requires.

      Reply

  48. Hello,

    My flow test is successful. But the test output shows empty brackets [] for the ‘Select’ step. What could be the reason ?

    Reply

    • Hi Pranav,

      Hard to say without seeing your data and flow. What does your data consist of? What is the output from the ‘List rows present in table’ step?

      Regards

      Phil

      Reply

      • Hi,
        Jumping on this comment as I’ve experienced the same thing. No error message, just empty brackets (and I’ve popped myself in the spreadsheet and aren’t getting the email). Here is the output of the ‘List rows present in table’ step:

        {“statusCode”:200,”headers”: {“Pragma”:”no-cache”, “Transfer-Encoding”:”chunked”, “Vary”:”Accept-Encoding”, “x-ms-request-id”:”0617f38e- a1be-463d-aff9- 3851db18be19; eb9197d9-55ab-429a-8d62- 8db303f560ca; c9474724-c315- 4980-84a5-8d9ca1c8671e; 49d99eb9-0cde- 412d-bd5d- 8dd4521ab6e3; 8513c8e4-4068- 4937-a00d- 76fae8398ef3; 2d8a4e3b-9610- 432c-afe6-5a3e20432d63; a287d6bb-4de2 -4855-a870 -1b53e4493abf” ,”OData-Version”:”4.0″, “Strict-Transport-Security”:”max-age=31536000; includeSubDomains”, “X-Content-Type-Options”: “nosniff”, “X-Frame-Options”:”DENY”, “Timing-Allow-Origin”:”*”, “x-ms-apihub-cached-response”:”false”, “x-ms-apihub-obo”:”true”, “Cache-Control”:”no-store, no-cache”,”Date”:”Tue, 16 Aug 2022 02:03:35 GMT”, “Content-Type”:”application/json; odata.metadata=minimal; odata.streaming=true”, “Expires”:”-1″, “Content-Length”:”294″}, “body”:{“@odata.context”: “https://excelonline-ase .azconn-ase.p. azurewebsites.net /$metadata#drives (‘b%21- 62Pkm2gmEujXOdrUjVl6V9c9m2RwSdLotBh6yu _UAlaHohNICggSbFzQUqtwi5U’) /Files (’01JUJWZXJHMRX2QBQM7VC3JHGPHTCOLGPI’) /Tables(‘%7BA143009E- 1774- 4BB5-BFF0-231164296B20%7D’) /items”,”value”:[]}}

        Reply

        • Like Phil said, it’s hard to say without seeing your data and flow. Make sure none of the column headers in Excel have spaces in them. Power Automate doesn’t like spaces!

          Reply

  49. i have two column with email ids of
    in To mail as per your procedure we can send

    but in cc mail how to send?

    Reply

  50. Will this method work if you are using it to send email to email addresses that are outside of your organization?

    Reply

  51. Why you use ‘MMM d,yyyy’ after formatdatetime? not ‘MMM dd,yyyy’?

    It didn’t work for me, failed test, not sure where went wrong.

    Reply

    • The execution of template action ‘Create_HTML_table’ failed. The column values could not be evaluated: ‘In function ‘formatDateTime’, the value provided for date time string ‘44740’ was not valid. The datetime string must match ISO 8601 format.’.

      Here is error message. I tried all sort of format I think went wrong. but still same error message.

      I am on Eastern standard time zone.

      Thank you for your help!

      Reply

    • It’s not clear whether you used MMM d, yyyy or MMM dd, yyyy, but you should use MMM d, yyyy. Also, ensure the dates in your Excel file are formatted with your local date format.

      Reply

    • I faced the same issue. Found out that, In excel file if you are leaving any cell blank in DueDate or ReminderDate columns then it throws this error. The “formatdatetime” function does not understand the empty cell. So, you can add any date or any text in empty cells in excel file to resolve this. It worked for me.

      Reply

  52. thanks for this flow. Please provide information on how can we format hyperlinks within the table if one of the column has a hyperlink.

    Reply

  53. Hi Mindy, this is a great tutorial. I was able to create the flow exactly as you described. But I have one issue, the email goes out to every email address that have the same reminder date.
    The only difference between your setup and the flow I created is that I am using and expression on my “due date” filter query to calculate the date the email is to be sent out. duedate eq ‘addDays(utcNow(), 2, ‘MMMM dd, yyyy’)’. Do you think that’s the reason it’s not filter the emails correctly?

    Reply

  55. What if there are multiple rows that need to be sent individually to the same email? Wouldn’t this prevent that? Why would you only want to send 1 email per email address? That doesn’t really make sense….

    Reply

  56. Hi i’m trying to use it for a spreadsheet at work bu there’s something going on with the date time.

    Syntax error at position 11 in ‘Expire date eq ”17-06-2022’.
    inner exception: Syntax error at position 11 in ‘Expire date eq ”17-06-2022’.
    clientRequestId: cd59dc6b-c427-46e2-b068-76d7ee9524db

    The days are in front of the months because it’s a South America pattern.

    As for the formula:

    formatDateTime(convertFromUTC(utcNow(), ‘E. South America Standard Time’), ‘dd-MM-yyyy’)

    I’d like some help as soon as possible.

    Reply

    • Hi Igor,

      Don’t worry about matching the expression to your date format. You’ll notice mine are also dd/mm/yyyy in my Excel file, but in my formatDateTime expression I have used yyyy-MM-dd. It’s only later when constructing the email html that you want to format to match your region.

      Mynda

      Reply

      • Ok, no problem. I corrected that but it seems to have happened again. Trying to understand what happened.

        Syntax error at position 11 in ‘Expire date eq ‘2022-06-20”.
        inner exception: Syntax error at position 11 in ‘Expire date eq ‘2022-06-20”.
        clientRequestId: 23db5eb8-2b63-4531-bb9e-d38937b6ad58

        Reply

        • I can’t tell you much more than the error you’re getting, which is there is a syntax error. This means the expression/formula is not correct. Check the expression. Check the header names in your file match those you’re referencing in your expression. Check you have the single quotes around the expression in the Filter Query field. Make a smaller sample file to see if you can narrow down the issue. Good luck.

          Reply

  57. Good day

    Please could you assist with following error – I tried everthing

    Syntax error: character ‘‘’ is not valid at position 18 in ‘ ReminderDate eq ‘2022-06-16’.
    inner exception: Syntax error: character ‘‘’ is not valid at position 18 in ‘ ReminderDate eq ‘2022-06-16’.
    clientRequestId: 44fae884-154f-4530-927c-b5d6a89a39c1

    I dont know wheter it is to do that when I start typing ReminderDate eq it doesnt give options:
    My data is as below:

    ReminderDate eq ‘@{formatDateTime(convertFromUTC(utcNow(), ‘E. Australia Standard Time’), ‘yyyy-MM-dd’)}

    Kind regards
    Wessel

    Reply

      • Thanks Mynda, I tried spotting it before I posted, but can’t seem to find where Im missing the quote

        I thought its at the back, but the original post also doesn’t have one there.

        Kind regards

        Reply

      • Sorry to post again:

        My exact code for that line is:

        “$filter”: “ASSIGNEDDATE eq ‘@{formatDateTime(convertFromUTC(utcNow(), ‘South Africa Standard Time’), ‘yyyy-MM-dd’)}”,

        Error code
        There is an unterminated string literal at position 27 in ‘ASSIGNEDDATE eq ‘2022-06-16’.
        inner exception: There is an unterminated string literal at position 27 in ‘ASSIGNEDDATE eq ‘2022-06-16’.
        clientRequestId: b083e4a5-c61f-4a1f-924d-a179ed890b5f

        Im also not making use a due date and reminder date, but only one colun ASSIGNEDDATE

        Thank you so much

        Regards

        Wessel

        Reply

        • Hi Wessel,

          Have you checked the two single quotes are in place around the function as shown in this image:

          If so, I would go back to your file and make sure there are no discrepancies/typos in the column names and date fields etc. Lastly, (I’m out of ideas after this), check the UTC time is actually the current date in South Africa. i.e. you’re 2 hours ahead of UTC, so if it’s not 16th June in UTC time when you run the flow, you’ll get an erroneous error.

          Mynda

          Reply

  58. First I want to thank you for the good and detailed explanation, both in the video and in the document you attached.

    I would appreciate your help:

    I Created my work flow and saved correctly. After running I get the following error message:

    There is an unterminated string literal at position 27 in ‘ReminderDate eq ‘2022-06-13’.
    inner exception: There is an unterminated string literal at position 27 in ‘ReminderDate eq ‘2022-06-13’.
    clientRequestId: aa6a7a51-3582-4263-8c67-7871be11ac82

    Reply

    • Hi Shir,

      Glad the tutorial was helpful. Position 27 is the 3 in the date string. I suspect you haven’t entered the correct double/single quotes to finish the expression.

      Mynda

      Reply

  59. Created my work flow and saved correctly. After running I get the following error message:

    The ‘inputs.parameters’ of workflow operation ‘Send_an_email_(V2)’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ’emailMessage/To’ is required to be of type ‘String/email’. The source type/format ‘Object’ of the value ‘{“[email protected]”:””}’ is not convertible to type/format ‘String/email’.

    Did I miss something? Thank you for your help.

    Reply

  60. I’m setting up my very first flow following your very helpful steps. When I clicked save at the end I received this error: Flow save failed with code ‘OpenApiOperationParameterValidationFailed’ and message ‘Input parameter ’emailMessage’ validation failed in workflow operation ‘Send_an_email_(V2)’: The parameter with value ‘”@items(‘Apply_to_each_2’)”‘ in path ’emailMessage/To’ with type/format ‘Object’ is not convertible to type/format ‘String/email’.’.

    Any thoughts on what I might have done wrong? Thank you!

    Reply

    • I realized I needed to change Apply_to_each_2 to the Apply_to_each. I was able to successfully set up the flow but when I run it it fails.

      Reply

      • Great to hear you’re making progress. Keep plugging away and troubleshooting. I’m sure you’ll get there.

        Reply

  61. Hi
    I am very new to this system and just trying out my first Automate flow and setting up is straight forward thanks to your helpful site but on running my flow I am hitting a wall with the required date error as below any I am stuck and need some guidance on what I am doing wrong.

    Syntax error at position 15 in ‘Calibration due date eq ‘29.38.2022”.
    inner exception: Syntax error at position 15 in ‘Calibration due date eq ‘29.38.2022”.
    clientRequestId: f4fb03de-02c0-4d34-90ed-9505228b8679.

    Many thanks
    Neil

    Reply

      • Hi
        Yes agree the dates in the spreadsheet don’t read like that, I Just cant work out what I have done wrong.

        I have checked what is written and it all seems fine, can you explain what I might be looking for when the error says inner exception and Position 15?

        Syntax error at position 15 in ‘Calibration due date eq’02.43.2022”.
        inner exception: Syntax error at position 15 in ‘Calibration due date eq’02.43.2022”.
        clientRequestId: c30ddf4b-1f42-4814-83b1-94b10b1bb2a0

        Any help would be much appreciated.
        Neil

        Reply

        • Check what is in position 15 in your expression for that step. You might be missing a comma or closing bracket.

          Reply

          • Hi
            Ok had a breakthrough another set of eyes saw a simple spelling mistake in my spreadsheet so the code was fine, its running now so will see what the output is tomorrow.

            I am sure I’ll have more questions then…..

            thanks for your help.
            Neil

  62. Hi,
    Thanks for a very helpful tutorial.
    I would like to know if there’s a way to set this up with reminders going to the same person every time, so no need for an email column and the steps that require selecting email addresses. Which part do I need to cut out?
    Thanks again

    Reply

  63. How can I troubleshoot why I am not receiving an email, I have tested the power automation and get a message it was successful. However, I am not getting an email. I have confirmed my email address is correct.

    Reply

    • Hi Andrea, it may have gone to your junk folder. You can check the sent items folder to see that the email was actually sent.

      Reply

  64. Thanks for the nice tutorial, I have tried to follow it through step by step but it’s failing to send the email. Somehow the SELECT stage is showing empty results when I review the results from a test run.

    Reply

    • Hi Peter, I’d say you don’t have any dates in your Excel file that match the filter. Also keep in mind that if you’re using the TODAY function that it will evaluate based on the Power Automate server location, not necessarily your location. I had this problem in testing myself.

      Reply

        • If you don’t have the Email field appear in the list of dynamic content when you search then it suggests that you don’t have a column in your Excel table called ‘Email’.

          Reply

  65. Very nice flow! I’m wondering how it would be possible to filter the excel list on all task equal or less than current date (to receive the overdue tasks as well in the html table)?

    Reply

    • Thanks, Thomas.

      You can substitute eq for equals with lt for less than, or le for less than or equal to. Obviously with lt you’d have to add 1 to today’s date e.g. utcNOW()+1

      Mynda

      Reply

      • Hi Mynda, I am trying to set the reminder for 3 and 7 days ahead of deadline but expression formatDateTime(convertFromUtc(utcNow()+3, ‘Central Europe Standard Time’), ‘yyyy-MM-dd’) is set as invalid, can you help me, please?
        Also if I want to combine conditions (the date condition and the cell value from “Status_Code” is either 1 or 2, how should I manage to list the correct rows?

        Thanks a lot, the flow works perfectly otherwise :-),
        Milena

        Reply

        • I don’t think you can put +3 on the utcNow() function. The purpose of that function is to perform the +3 based on the locale you specific in the next argument, i.e. ‘Central Europe Standard Time’

          Reply

Leave a Comment