cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SJohnston10
Regular Visitor

Send email based on date in Excel

 

Good evening,

I've created a few Power Automations, but the send email on date specified in my Excel Spreadsheet has me stumped.

I don't know exactly where to put in the formulas that change the date into the correct format.

So, I want to send a reminder email to teachers when a student is to return to PE after their PE excuse expires.  

Thank you in advance for your help. 

pe note.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

 

Hi @SJohnston10 ,

 

If you have used Filter Query to filter records, you should not use the Dynamic content output by List rows present in table action to configure Send an email notification action.

You need to use the output from the body of Filter array.

Remove Apply to each 2 and use the expression to get the value of the field in Filter array.

 

Please use this expression to get the value of the field:  item()['ColumnName'].

Get the column name from the Body of Filter array.

 

Image reference:

38.PNG

Hope this helps.

 

Best Regards,

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

View solution in original post

Yes, I found it went twice.  I removed the Apply to each 2.  I do have to say it took me a couple of times to realize I didn't have to insert the condition first.  I just select email and then it adds the condition and then I chose Body.  This note is just in case a newbie is looking for how to fix that.  🙂

Thank you again for all of your help!  

View solution in original post

9 REPLIES 9
fchopo
Super User
Super User

Hello @SJohnston10

When you get a date in excel, a integer value is returned, which is the number of days from 12-3o-1899 until today. So, you could filter the elements where date_ends are less or equal than to date with the following formula:

addDays('1899-12-30',int(item()?['Date'])) leq utcNow('yyyy-MM-dd')

 

Have a look at this example:

flow.png

 

1) Each day

2) Get the rows in an Excel table stored in a SharePoint site.

3) Filter the rows where Date is less or equal than today.

4) For each row, send a reminder email.

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Thank you for your very detailed instructions. 

I have followed them but the emails are not sending.

This is what I have so far.  Can you see where I'm going wrong?pe note.png

 

Hello @SJohnston10 

Is the flow not working because of an error, is it not sending any email but it is executed without any errors? Could you check your data in Excel, or share an screenshot of the execution results in Power Automate?

It is normal that the "apply to each 2" is created when you add the send mail action, since the filter action could output more than 1 record.

Regards,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
SJohnston10
Regular Visitor

It says that they are successful, but nothing ends up being sent.  Based on the Excel Spreadsheet I should get one reminder.

Thank you again for being so willing to help.  It is appreciated.  🙂

PE Note Runs.PNG

 

Hello @SJohnston10 

Try to use the ticks function on both sides of the comparison and let's see if it works:

ticks(addDays('1899-12-30',int(item()?['Date']))) leq ticks(utcNow('yyyy-MM-dd'))

 Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Thank you.  That allowed the emails to be sent.  However, it sent all three instead of the one that should have been sent.  So I changed the Apply to Each 2 to Body also and it only looked for those items that were filtered.  

PE Note Runs2.PNG

 

 

Hi @SJohnston10 ,

 

If you have used Filter Query to filter records, you should not use the Dynamic content output by List rows present in table action to configure Send an email notification action.

You need to use the output from the body of Filter array.

Remove Apply to each 2 and use the expression to get the value of the field in Filter array.

 

Please use this expression to get the value of the field:  item()['ColumnName'].

Get the column name from the Body of Filter array.

 

Image reference:

38.PNG

Hope this helps.

 

Best Regards,

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

Yes, I found it went twice.  I removed the Apply to each 2.  I do have to say it took me a couple of times to realize I didn't have to insert the condition first.  I just select email and then it adds the condition and then I chose Body.  This note is just in case a newbie is looking for how to fix that.  🙂

Thank you again for all of your help!  

I tried this flow and it is not working for me.

This is my flow:

palacim_0-1666804162570.png

When I run it this is the error I am getting.

palacim_1-1666804285013.png

 

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,763)