Send email to recipients based on excel

[Unlycky]

Hello,

I wanted to create a flow that send e-mail containing HTML table with Driver code, Driver name, Certificate name, Date of expiry and Driver care to specific person when one of the documents is expired or will expire in 30 days and till this everything is working well.

After I've created this I wanted to go one step further and group up drivers connected to the same driver care manager and send email directly to him about his drivers only (each driver have only one driver care) and I really don't know how to do it.

This is what so far I've got:

 

[MarconettiMarco]

Hello @Unlycky ,

sure!

Please follow this steps:

1. List rows present in a table: in the filter query insert "Expiry eq '

formatDateTime(addDays(utcNow(),30),'dd/MM/yyyy')

'

2. Add a "Compose" action in which put the following expression:

union(outputs('List_rows_present_in_a_table')?['body/value'],outputs('List_rows_present_in_a_table')?['body/value'])

3. Add the "Select" action, where by clicking on the following button, you'll have to insert the Manager value

4. Add another "Compose" action in which put the following expression:

union(body('Select'),body('Select'))

5. Create an "Apply to each" loop where the outputs must be the previous Compose action output;

6. Then create an HTML table and, if you want, format it like this:

Here the style:

<style>
table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  width: 100%;
  text-align: left;
  border-collapse: collapse;
}
table td, table th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
table tbody td {
  font-size: 13px;
}
table thead {
  background: #1C6EA4;
  border-bottom: 2px solid #444444;
}
table thead th {
  font-size: 15px;
  font-weight: bold;
  color: #FFFFFF;
  border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
  border-left: none;
}
</style>

7. Finally send an email

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[Unlycky]

Hello @MarconettiMarco ,

thanks for your help. I have a problem because the table comes empty this is the output I'm getting from list rows:

And this is the input:

[MarconettiMarco]

Hello @Unlycky ,

are you sure that in your table are there records with Expiry date equal to 11/08?

Anyway the Expiry date format must be equal to the Date Format used in the following expression:

formatDateTime(addDays(utcNow(),30),'dd/MM/yyyy')

You can use any Date Format you want, based on the ISO 8601 format.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[Unlycky]

@MarconettiMarco 

I've found a problem, Power automate reads excel time as a flat value. How do I convert it to date ?

[MarconettiMarco]

@Unlycky is it like 47893?

BR,

Marco

[Unlycky]

@MarconettiMarco 

yes exactly

[MarconettiMarco]

ok @Unlycky ,

if your're not able to format the cell in the excel file, please use the following expression:

formatDateTime(addDays('01/01/1900', <your_value>),'dd/MM/yyyy')

 so for example:

formatDateTime(addDays('01/01/1900', 44752),'dd/MM/yyyy')

 will return 12/07/2022

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[Unlycky]

@MarconettiMarco 

How can I format date in Excel ?

I'm getting error 

"The template language function 'addDays' expects its second parameter to be an integer. The provided value is of type 'String'. Please see"

I've tried adding int() but didn't help.

[MarconettiMarco]

hello @Unlycky ,

please use this expression:

formatDateTime(addDays('01/01/1900', int(44752)),'dd/MM/yyyy')

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco