My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Automatically Email Reminders from Excel with Power Automate

You are here: Home / Power Automate / Automatically Email Reminders from Excel with Power Automate
Power Automate Emails From Excel
by

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

Power Automate Emails From Excel

More Power Automate Posts



Category:

Reader Interactions

Comments

  1. Paul Martin

    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

  2. Leandro

    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

  3. Natalie Cotela

    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

    • Mynda Treacy

      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

      • Natalie

        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

      • Natalie

        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

  4. aileen

    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

  5. Lisa Santoro

    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

      • Lisa Santoro

        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

        • Mynda Treacy

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

          Reply

  6. hatem

    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

    • Mynda Treacy

      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

  7. Jared Davignon

    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

  8. Serene

    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

  9. Margaret

    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

  10. Chris Rayers

    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

  11. Kamlesh Dhuri

    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

  12. Omkar

    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

  13. Lina

    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 A@hotmail.com;
    Provider B, reminder date: 09/10/2023, Provider B@hotmail.com;
    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

  14. Susie

    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

  16. Gabriela

    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

  17. M. Kanai

    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

  18. Lim Wee Siang

    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

    • Mynda Treacy

      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

  19. justice

    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

    • Mynda Treacy

      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

      • Joan Topper

        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

        • Mynda Treacy

          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

  20. Hermes Emanuel Chavez Marin

    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

  21. Mark

    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

    • Mynda Treacy

      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

  22. Ellen

    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

  23. Priyansh Sharma

    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

      • Priyansh Sharma

        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

        • Mynda Treacy

          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

        • Carl Reyes

          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

  26. TSTZ

    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

    • Mynda Treacy

      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

      • TSTZ

        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

  27. Lily Zhang

    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

    • Mynda Treacy

      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

  29. jack

    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

    • Mynda Treacy

      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

  31. Stephanie

    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

    • Mynda Treacy

      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

    • Stephanie Grace

      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

  32. Connie Young

    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

    • Mynda Treacy

      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

  33. J

    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

    • Mynda Treacy

      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

  35. pranav

    Hello,

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

    Reply

    • Philip Treacy

      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

      • Liv Wilson

        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

        • Mynda Treacy

          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

  36. manikishore

    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

  37. El Conquistador

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

    Reply

  38. Spring

    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

    • Spring

      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

    • Mynda Treacy

      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

    • varun gupta

      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

  39. Danny

    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

  40. Keith

    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

  42. Sarah

    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

    • Mynda Treacy

      If there are multiple lines that apply to the same email address they would be included in the one email.

      Reply

  43. Igor

    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

    • Mynda Treacy

      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

      • Igor

        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

        • Mynda Treacy

          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

  44. Wessel

    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

      • Wessel

        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

      • Wessel

        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

        • Mynda Treacy

          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

  45. Shir Nixon

    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

    • Mynda Treacy

      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

  46. Jermie

    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 ‘{“jermie.arnold@csulb.edu”:””}’ is not convertible to type/format ‘String/email’.

    Did I miss something? Thank you for your help.

    Reply

  47. Annie S

    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

    • Annie S

      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

      • Mynda Treacy

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

        Reply

  48. Neil Small

    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

      • Neil

        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

        • Mynda Treacy

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

          Reply

          • Neil Small

            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

  49. Alex

    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

  50. Andrea

    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

    • Mynda Treacy

      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

  51. Peter Pejavi

    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

    • Mynda Treacy

      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

        • Mynda Treacy

          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

  52. Thomas

    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

    • Mynda Treacy

      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

      • Milena

        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

        • Mynda Treacy

          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 Reply

Your email address will not be published. Required fields are marked *

Current ye@r *