Reminder email based on due date in excel

[athota]

Hello,

I am new to power automate, i am trying to create a flow which will send a reminder email to my team member on a specific due date. I am from India and my time zone is "(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi", my reminder due date in excel is in date-month-year (dd-MMM-yyyy) format. An email has to be sent for the task on the reminder date mentioned. I have not added email id in the table as it is not dynamic. Can any one please help me build the flow.

Thanks

[v-LilyW-msft · Accepted solution]

Hi @athota 

According to your description, you want to send email based on due date in excel.

I set up a simple flow, you can refer to below screenshots.

Configure Recurence advanced options, the flow will runs at 9:30 every day in your time zone.

You can modify it according to your needs.

Expression: formatDateTime(item()?['Reminder Date'], 'yyyy-MM-dd')

Expression: formatDateTime(utcNow(), 'yyyy-MM-dd')

Then save and can test it.

This is my excel:

After Flow runs, I will receive an email as below:

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[athota]

Hi @v-LilyW-msft ,

Thank you for the solution it worked. I am receiving emails as per the due date. However, the email content for task has unnecessary code in place of task, please see screengrab below.

I was not getting the "Task" array as you have got, it is only showing me "Item" array. It is showing excel task array, but when i select it the flow is not running. I tired by adding the expression for Task as well but the flow is not running.

I am actually fetching the data from another sheet to this sheet and reflecting the data, is this the cause for above error? Please help.

[v-LilyW-msft]

Hi @athota 

Could you share me the outputs of Select?

Best regards

[athota]

Hi @v-LilyW-msft ,

Here is the output of select.

INPUT:

{

    "@odata.etag": "",

    "ItemInternalId": "d1245d8a-ce74-47bf-ab30-59de929f2ed8",

    "Task": "Test task 3 for automation",

    "Due date": "2021-07-12T00:00:00.000Z",

    "Reminder Date": "2021-07-09T00:00:00.000Z"

  }

OUTPUT:

{

    "Task Name": {

      "@odata.etag": "",

      "ItemInternalId": "d1245d8a-ce74-47bf-ab30-59de929f2ed8",

      "Task": "Test task 3 for automation",

      "Due date": "2021-07-12T00:00:00.000Z",

      "Reminder Date": "2021-07-09T00:00:00.000Z"

    },

    "End Date": "2021-07-12"

Please let me know if anything else is required.

Thanks!

[tom_riha]

Hello @athota ,

I think your 'Select' action is not configured properly, you should use item()?['Task'] as the Task Name, but you're using the whole item() dynamic content.

[athota]

Hi @tom_riha ,

I have used the whole item dynamic content as I was not getting the task option to select, please see screengrab. I have tried to use the excel rows but that also failed and the flow didnt run.

Please let me know if you any further information.

Thanks

[tom_riha]

The item()?['Task'] is an expression, copy paste it into the expression field and enter it there instead of the Item dynamic content.

[athota]

Hi @tom_riha  and @v-LilyW-msft ,

It worked now, I tried using the expression before but the flow didn't run, that is when I used the other options. I have tried it again and it worked. Thank you so much for your help.

Thanks!

[athota]

Hi @v-LilyW-msft  and @tom_riha ,

I have interacted with you before on this ticket regarding a task reminder flow, the flow I created was working fine. I am trying to replicate the same flow for another data sheet, but it doesn't seem to be working. Below is the error i am getting, can you please help me?

I am adding the screen grab of my new flow below, which is an exact replica as suggested by @v-LilyW-msft .

Expressions i am using are as below:

formatDateTime(item()?['Reminder Date'], 'yyyy-MM-dd')

formatDateTime(utcNow(), 'yyyy-MM-dd')

item()?['Task']

formatDateTime(item()?['End Date'], 'yyyy-MM-dd')

My data sheet:

I am using one drive for business for this flow and the date format when edited online is showing as below, is this the cause for the issue?

Thanks!

[athota]

Hi @v-LilyW-msft  and @tom_riha

Please will you be able to help?

[athota]

Please can any Microsoft employee or community user look into this concern?

Thanks!

[dimitrisval]

I have created a flow up-to the filter array and when I run it, I get the following error:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(formatDateTime(item()?['Reminder Date'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))' failed: 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

I have copied exactly as per instructions. any ideas what this error means and how to resolve it? You help is much appreciated

[tom_riha]

Hello @dimitrisval ,

my guess would be that you've got a row in the Excel table with empty 'Reminder date' = it can't format an empty date = error.

You should first filter out the rows where the 'Reminder date' is empty and only then use this other 'Filter array' with the condition you shared.

[krish1729]

Hello,

Filter array not getting the output from excel.

Please help.

Thanks,

krishna

[dimitrisval]

Thank you all for your replies. it was a time formatting issue that now I have resolved. Thanks again.

[krish1729]

Hello,

 An email has to be sent for the task on the reminder date mentioned. I have added email id in the table as it is dynamic.

TaskReminder DateSend to 

Add data in excel	23 May 2022	example@outlook.com
Remove data	23 May 2022	test@outlook.com
Update data	23 May 2022	test@outlook.com

[krish1729]

Hello @dimitrisval 

Need solution for the above query. kindly request you to help.

Thanks.

[dimitrisval]

Hi krish1729, This is what I did. Confirmed the Excel data is formatted as Date. (Used custom format as I like dates displayed as dd/mmm/yyyy not dd-mmm-yyyy)

Then  in my flow set the following:

The format date is shown below: Please note I have added 30 to utcNow() as I wanted my reminders 30 days before the expiry date.

formatDateTime(convertFromUTC(addDays(utcNow(),30), 'GMT Standard Time'), 'yyyy-MM-dd') 

Please note Date format in filter. it worked with this as it matched the one returned from Excel query

Also, in order to ensure I get data from Excel (during debugging) I have removed the filter and made sure I can see data in the List rows output. The data will show the date format returned from Excel then you can adapt your filter. I hope this helps.

Regards

Dimitris

[krish1729]

Hello @dimitrisval 

But i need to send that data to a specific email as mentioned in the above query. Please see inline:

Task                                                Reminder Date                 Send to 

Add data in excel	23 May 2022	example@outlook.com
Remove data	23 May 2022	test@outlook.com
Update data	23 May 2022	test@outlook.com

I wanted to share first row data to example@outlook.com ,

Second row data to test@outlook.com.

I don't want to add an email id every time.. data needs to send their receiving id as given in the table.

Thanks