Excel date trigger to create flow email

[Anonymous]

Hi all, 

Long time watcher from a distance and learner, first time asking.

I want to use dates in my excel spread sheet column to trigger an email to be sent as a reminder including relevant information (Please see snippet).

 

The date I want to use is the 'Int Review'. Currently it is set up to turn orange when the date is within 30 days and than red on due date.

I would like to send an email triggered when the date is about 5 days from the due date, but am also fine to have it occur on the due if that is easier.

The information I would like include at the time of the email would be in column 'Type, Instrument No., Document Type & Int Review'. (I've created email flows from Microsoft Forms before so pretty ok with this bit)

So far I have tried using the below which seems to work until the 'Filter array' in the 'Choose a value' where I have used a mixture of expressions including addDates and formatDateTime which will end up with different types of errors. 

Any help that can get me through this hurdle or ideas would be greatly appreciated as I can not find a solution with the already existing ones online.

Thanks in advance, Jay

[Hardesh15]

@Anonymous  Please take help from here- https://powerusers.microsoft.com/t5/General-Power-Automate/Recurrence-Schedule-Reminder/m-p/1316976#M79839

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

[Anonymous]

Hi@Hardesh15 ,

I followed your link to a possible solution and set up the flow as outlined. Unfortunately when testing I was provided the following error message

Hope this helps with finding a solution 😁

[v-LilyW-msft]

Hi @Anonymous 

According to your description, you want to send an email when the date is about 5 days from the due date.

Do you want to put all the filtered rows in one email?

If yes, I set up a flow, you can take a try with the screenshots below.

This is my test excel.

Flow in details:

Expression1:formatDateTime(item()?['Int Review'],'yyyy-MM-dd')

Expression2:addDays(utcNow(),5,'yyyy-MM-dd')

Expression1:item()?['Instrument No_x002e_']

Expression2:item()?['Document Type & Int Review']

Save and test flow.

After flow runs, Test2 will receive an email like beolw.

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.

[Anonymous]

Hi @v-LilyW-msft, 

I have tried your way as well and have come up with the following error

I had tried this way previously as well but with no success. Thought I would try again following your instructions though reached the same outcome. 

Seems to have trouble reading the dd/mm/yyyy format. I have tried changing to mimic your date format (mm/dd/yyyy) in excel but it turns it straight back to dd/mm/yyyy.

I'm still open to any ideas or alternate flows.

Thanks,

Jay 

[Hardesh15]

@Anonymous  can you please add one compose and pass your 'Int Review' and run your flow. please share output.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

[Anonymous]

Hi @Hardesh15, I'm not sure where you want me to add the compose and with what expression. Sorry I'm still very new with this.

[Hardesh15]

@Anonymous add compose action just below list row present in a table action and pass your 'Int Review'  from dynamic content.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

[Anonymous]

Hi @Hardesh15 

Hopefully this is what you were after

otherwise I'm not sure 😀

[Hardesh15 · Accepted solution]

@Anonymous  See your date is in numbers. please expand list row present in a table action and choose date format - ISO

After that close your browser and flush cache. And then test your flow. When we do re submit in flow it actually cached data from excel connector.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

[meaganleigh]

Hi ya - this is probably the most straight-forward solution I have seen thanks!  I have the following excel spreadsheet and I need to email each author 7 days prior to the due date and advise them which of their topics are due - can you help me set up a flow that works please?

[CLPerez]

Thank you! How would I go about having different due dates be triggered? Let’s say Type A has to go through different stages of due dates, how would I do that?