Import Excel rows, compose table and send email

[Atonkin89]

I'm struggling with a solution to this use case. I'm sure someone has done it already but can't locate any examples. 

I have an Excel table that contains rows of tasks with columns for status, description, due date and owner. Eg. Excel Table contains 50 tasks, Person 1 has 5 tasks, Person 2 has 10 tasks etc, with varying due dates and status (active or closed).  Using an Instant Flow trigger, I'd like to get the rows, filter out closed tasks and send an email to each Person; that email contains an html table that lists the active tasks assigned to them, sorted by due date. 

I would greatly appreciate any advice you have on how to do this. Thank you. 

[DamoBird365 · Accepted solution]

Hi @Atonkin89 

Are you able to share some sample data (as text and not a screenshot) and I can build something up to demo it to you.  You would want to list rows from the table (order by due date and filtered by status=closed), use a select (in text mode) on the people names, followed by a union on the select in a compose to get the distinct names. Then you would do an apply to each on the distinct names with a filter array on the rows where the name = current item. You can then create an html table and email it.

I have a similar video but if doesn't include the distinct names bit.  Happy to do a new video on this one if you can supply some data.  https://youtu.be/5msxh5Ux8DY

Damien

[Atonkin89]

Thank you so much for helping! A video would be fantastic. I'll send some sample data shortly.  Thx 

[Atonkin89]

https://1drv.ms/x/s!Ai3DW1KkAOTUhniJZ4SjjSOOf-Pu

Here's sample data

[Atonkin89]

@DamoBird365 sample data posted above. Thx 

[DamoBird365]

I’ll try take a look at weekend. Your data lacks any reference to emails btw. How would you anticipate the solution knowing the emails to use? Would you do a user lookup or include these in the excel file?

[Atonkin89]

The source data only contains display name (last name, first name) so I have to insert a step that gets the UPN email address from our Outlook domain   

[DamoBird365 · Accepted solution]

HI @Atonkin89 

Not managed a video but hopefully this explanation will help.

I converted your data to a table and updated with names on my tenant:

Then I list the rows with a filter status eq 'pending' - you had mentioned opened /closed but the data doesn't match the requirement.  You can update to suit.

I use select to get an array of usernames (enable text mode on the select) and then union on the body of the select.

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

Then add an apply to each to loop through the distinct names, filter all of the data from the list rows where the user name is equal to the current item (i.e. each of the distinct names).  Create an HTML table, which you can customize by watching my video I referenced before and then search for your user (as you mention you don't have the email in the table).  Note that I have limited the results to 1 as in the next part, Power Automate will put you into a loop and really you want to assume that you will only return the top email.  Failing to do so can potentially return multiple results and email people unintentionally.

Finally, take the email from the search for users and insert it into send an email action (it will create an apply to each but given you have limited the number to 1 it will only email the first match) - add the HTML table.

Good luck!  If you're still unsure, let me know.

Damien

[saran19901]

I'm struggling with a solution to three use case. I'm not sure someone has done it already but can't locate any examples.

first one should be manual flow and second and third should be event triggered.

@abm  @DamoBird365 - Guys i have gone through ur videos, but not able to find it. If you could help me with video for this complex scenario that would be helpful.

1. filter email id and send html table to unique emails

i wanted to filter like below and send email to each email id with Subject as: ARS1859_ Account details.

send 5 emails with respective table in it.

2. Use case: when we get reply from these emails, it has to store in local drive/one drive by creating a folder name as site code and save the email as attachment.

3. Use case: It check whether we received response from all the email that we sent and trigger a email to me that we received a email from everyone.

[saran19901]

https://docs.google.com/spreadsheets/d/1iGwXF0Ta0u9QYGD4Tb2ANHCTCq0aH4lT/edit?usp=sharing&ouid=10723... 

@DamoBird365  - data is there attached, i have seen your video and replicate for my first use case, but able to do it. hope u can look into this.

[akinco]

Awesome video to merge two data sets, thank you!  I have one data set i'm exporting from ServiceNow  - these are stories our stakeholders need to test.  I need to take that data set, collate all the stories for each stakeholder, and email the list of stories to the correct stakeholder.  right now i get a single email for each row in the spreadsheet.  any guidance is greatly appreciated!

thank you!