Excel to HTML table to Send emails

[nishivarma]

Hi, I am looking for a solution for the below problem statement

Excel Database > Filter the data based on unique criteria > create HTML table > send it to unique email id of the team lead

1. The above image is the sample database that we have. The "Days Left to follow-up" column is dynamic that it keeps updating daily (formula driven) based on the days left for the end date.

1. I want to create a power automate flow that filters the "Days left to follow-up" column to less than 30 and below 
   
2. Now it needs to send an email to the Team leads. In the example we have two unique Team Leader names among the 5 rows. so it has to send emails to unique Team leads consolidating the relevant rows under them.
3. Like it needs to send one email to Khakashi showing the three relevant rows tagged under him
   
4. And another email to Kurenai showing members tagged under her within in the filtering criteria.
   

   Requesting your help. Thanks

[royg]

Hi @nishivarma,

You'll need to create a distinct list of the team lead emails. You can do that using 2 actions:

1. Select - after your get rows action, add a Select action and check the "Switch Map to Text mode" icon. in "From" select value and in "Map" select your desired email column:

2. Compose - add a compose action and for "Inputs" specify the following expression: 

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

The result of that action is an array of strings (the distinct emails).

You can now add an Apply to each action to iterate the emails, inside that loop you need a "Filter array" action to get the rows of the current team leader email, followed by a "Create HTML table" action and a "Send Email" to notify the team leader (current item):

That should get you started

[nishivarma]

Hi @royg 
Thanks for this, it really helped. I am successfully able to send the emails to Team leads with all their relevant rows in it. However, my requirement is also to send emails only for the data where value in "Days left to followup" is less than 30. I guess I need to add another Filter array. I have tried, it didn't work. 

Could you please help.

[royg · Accepted solution]

Hi @nishivarma,

After your Excel List rows present in a table action, add a Filter array action that trims down the items to meet your criteria:

Then update the following Select action - From: Body of Filter array action. Map: item()?['Email'].

Lastly, update the Filter array action inside the apply to each loop - From: Body of Filter array action.

Hope this helps

[nishivarma]

Hi @royg 

This works perfectly fine and I just stuck at the final step. Here is my full view of flow

• In the Second image, for the email Recipient I have used output from compose3. But I am getting the below error message
  

  The 'inputs.parameters' of workflow operation 'Send_an_email_(V2)' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'emailMessage/To' is required to be of type 'String/email'. The source type/format 'Array' of the value '["myname@Org.com"]' is not convertible to type/format 'String/email'.

  
  myname@Org.com - In the data I have my email id, just for illustration I have renamed this. Can you please help me cross this final leg?

[royg]

Hi @nishivarma,

In the To field of the send email action please locate Current item in the dynamic content options and replace the Compose3 outputs as this is an array of emails and you want to send an individual mail for every iteration

[nishivarma]

Thanks @royg . The Flow is complete and I am able to get the desired output. Much appreciated, Thanks again.