Sending email based on excel

[Hybrid_Pete]

Good afternoon

I'm looking to make an email reminder based from data in an Excel sheet, I've made a few automated flows so far but this one has me stumped. The flow below will do everything I need except instead of sending information from the relevant row it sends several emails with all rows from the Excel sheet on it. For ease of sharing I've removed all but three lines on the Excel sheet, one with a previous date, one with today's date and one with a future date on it. I then manually triggered the flow to show what happens, the last screenshot is of my inbox after the emails have been sent. I notice the date is in a five digit format rather than date format and have checked the Excel to make sure the cell is correctly formatted and it appears to be.

[gabibalaban]

@Hybrid_Pete ,

Please take a look on the last row of the List rows present in a table action. It is about the format date. Use ISO 8601 to get a readble format. After that you can datetimeformat() function to set your format. 

Hope it helps!

[Hybrid_Pete]

Thank you for the swift reply, I set the DateTime Format to ISO 8601 but I don't follow what you mean by 'After that you can datetimeformat() function to set your format.'  Could you expand on that please?

EDIT

I just ran the flow and it now puts the date in a somewhat readable format but the flow will still send an email for every line on the excel sheet rather than just the one that matches the date

[gabibalaban]

@Hybrid_Pete ,

make a Short test to see how the date looks like now.

Then on the last action - subject property use with dynamic content expression - 

formatdatetime(your date references,’dd.MMM.yyyy’)

sorry for my bad English 😊

[gabibalaban]

@Hybrid_Pete ,

I review your request once again. Here are my observations:

It seems that in Apply to Each you used the result of the Excel - List rows present in a table action and not the result ofthe Filter array action. Actualy you don't need the Filter array action at all, you can use the Filter query of the Excel - List rows present in a table action using

Data eq '@{div(sub(ticks(utcNow()),ticks('1899-12-30T00:00:00.000Z')),864000000000)}'

(this will filter from Excel file table source rows where Data equals with today, Data being the Name of the Date Column from Excel).

To avoid sending email with date format like yyyy-MM-ddThh:mm:ss.000Z (ISO 8601 format), you can use formatDateTime(utcNow(),'dd.MM.yyyy').

[Hybrid_Pete]

I've done my best to follow your advice but this still doesn't work for me. I thought it might be easier to add a column in the Excel that auto updated with today's date but I assume that the sheet will need to be manually opened and saved in order to update the todays date formula.

[v-LilyW-msft · Accepted solution]

Hi @Hybrid_Pete 

According to your description you want to send an email when 'Bas. start date' is equal to today.

I set up a flow you can take a try with the following screenshots.

This is my test Excel

Flow in details:

Expression1:formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['Bas_x002e_ start date'])),'yyyy-MM-dd')

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

Expression:formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['Bas_x002e_ start date'])),'yyyy-MM-dd')

After flow runs, I will receive an email like this:

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.

[Hybrid_Pete]

Thank you very much for taking the time to walk me through this, I copied what you did and this works perfectly for me. I also learned a few techniques in the process and that I need to learn a little bit more on how expressions work 😀