Send Email and Details from Excel Spreadhsheet

[cnorth15]

Hi All,

I am new to Power Automate and just learning some of the basics still. I am trying to create a flow that will run everyday and will look at an Excel spreadsheet and email the user from one column if the date matches the date the flow is running in the next column e,g,

I want to read the spreadsheet if Today's date matches Column D then Email Column E the details from B A and C for example.

Thanks

A	B	C	D	E
Surname	Forename	Grade	Date	Owner Email
Smith	John	1	31 May 2023	dan.bob@test.com
Bloggs	Fred	3	22 June 2022	dave.Bond@test.com

[mgrachii]

hi, where is your excel file located?

[cnorth15]

Hi,

The file is stored in my OneDrive for Businesses but I am also thinking about possibility of using a file stored on SharePoint

[v-qiaqi-msft · Accepted solution]

Hi @cnorth15,

Where to store this excel file does not matters.

If you want to send email with the details of a record matching the date, please check the following flow:

add(div(sub(ticks(utcNow('MM/dd/yyyy')),ticks('1900/01/01')),864000000000),2)

addDays('1899-12-30',int(item()?['StartDate']),'yyyy/MM/dd')

[cnorth15]

Hi @v-qiaqi-msft 

It look like it worked and sent 2 emails to me and 1 to a colleague out of 3 rows with today date been the ReminderDate however the contents of the email show details for all 3 rows, I only want to show the details of the Row based on the email recipient, if that makes sense.

Thanks

[v-qiaqi-msft]

Hi @cnorth15,

Tell me how you configure your flow.

I have added the condition to list only the records meeting the date.

[cnorth15]

HI @v-qiaqi-msft ,

Your original post look to be working, I had to make some changes to my data to get it work.

I think I have made some alteration which seem to work for me that now send the email with just the Forename and Surname however the value in the date field is coming out a bit Send an Email ReminderDate at end

different to what I expected. My main difference is the expression ReminderDate in place of your StartDate (Formula is same) however in my Send an Email the Reminder Date come out formatted not matching the original date in the excel file. When I or my colleague receives the email the names change to match the differences in the excel file as planned but the date come out as "with a review date of (44685)." instead of 04/05/2022 or 04 May 2022.

[v-qiaqi-msft]

Hi @cnorth15,

I think I have pointed how convert the date into date format in my first reply, please check for reference:

addDays('1899-12-30',int(item()?['StartDate']),'yyyy/MM/dd')

Note that you should replace the item()?['StartDate'] with your dynamic content.

[cnorth15]

Hi @v-qiaqi-msft 

I have updated the dynamic content,

addDays('1899-12-30',int(item()?['ReminderDate']),'yyyy/MM/dd')

ReminderDate is both the name of the my expression in the the filter query,

ReminderDate eq 

add(div(sub(ticks(utcNow('MM/dd/yyyy')),ticks('1900/01/01')),864000000000),2)

and is the name of the column in my excel that holds the date, if anything different doesn't work. 

However when the email come the ReminderDate and another date value changes to a 5 digit number e.g. review date of (44686).

If I output the HTML the the date come out as the expression properly but I was hoping it would do the same with the ReminderDate from the List rows from table?

Thanks