Recurring Email from Excel Data based on a condition

[yash5]

I am hoping for some guidance on feasibility and approach for the following flow:

1) Every Saturday, read a table via "Excel Online" with following fields: Date 1, Date 2, Name 1, Name 2

2) If Date 1 = Today's date, Create an email using Date 2 (deadline) in the body, and send to Name 1 and cc: Name 2.

3) If today's date has no matches in the Date 1 column, email to admin@123.com

Thank you,

Yash

[v-alzhan-msft · Accepted solution]

Hi @yash5 ,

Please refer to screenshot below to create the flow:

The fist expression in the Condition for the Date1's date:

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date1']),-2)),'yyyy-MM-dd')

The second expression in the Condition for today's date:

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

The expression for the Date2 in the email body:

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date2']),-2)),'yyyy-MM-dd')

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yash5]

Thank you so much Alice!

I will try this and accept as solution in next day or so. 

[v-alzhan-msft]

Hi @yash5 ,

Waiting for your feed back.

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yash5]

Hi @v-alzhan-msft  Alice, 

Thank you for your input. I setup my flow today but it failed at the date comparison step, see below:

Could you explain what these date expressions are doing? I tried replacing date 1 expression in the condition with the field value "date 1", and it is able to go through without error but match is not accurate, ie: condition 2 is executed and no parameters in the condition 1 are met even though multiple should be.

Thank you for your help!

[yash5]

Error Screen

[v-alzhan-msft]

Hi @yash5 ,

Please make sure that the date column in your excel table has been converted to date type without time.

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yash5]

Hi Alice @v-alzhan-msft , 

I updated the formatting and there was no time in there. Let me know if I am missing something here:

[v-alzhan-msft]

Hi @yash5 ,

Please share all the expressions in your flow.

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yash5]

[v-alzhan-msft]

Hi @yash5 ,

You shouldn't use my expression directly, please update the date column name in my expression with yourselves:

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date From']),-2)),'yyyy-MM-dd')

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date to']),-2)),'yyyy-MM-dd')

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yash5]

🙊I am sorry, I totally missed that.

I updated the expressions and it works. However, for Date 2, I get the following output:

How do I change the date format to April 3, 2020?

Thank you so much for all your help!

[v-alzhan-msft]

Hi @yash5 ,

Take a try to formate it as screenshot below:

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yash5]

Thank you so much for your help through this. I would love to learn more about expressions and dynamic content. 

Also, for the outlook add-in, do you think it would be possible for flow to look-up emails in the address book based on name?