Email to multiple people using Excel array

[jtao]

I have done researched in the last couple of days but still hasn't got much ideas.  Client supplied an Excel spreadsheet with staff names, overdue courses, managers email addresses .  The requirement is to email staffs' overdue course to their session managers.

For example, 

To manager David :-

Staff A has overdue course 1 on 22/10/2020

Staff B has overdue course 2 on 25/10/2020

Staff C ..., Staff D

To manager Steven : -

Staff F has overdue course 5 on 1/11/2020

Staff G ..., Staff H

I am only able to email all staffs' overdue courses to one person but not to multiple people where their staffs are under.

[sumurthy]

If you are comfortable using TypeScript (JavaScript), you can use Office Scripts to achieve this very easily. It reduces the number of steps in your flow and keeps things small and tight. 

I have a video of how to achieve something very similar using Office Scripts here: 

https://github.com/sumurthy/officescripts-projects/tree/main/Filter%20Table%20Get%20Visible%20Range%...

It keys on 'station' column. But you can do that with your Manager email column and select/summarize the fields you need to be emailed out. 

This (unrelated) video shows how to use Office Scripts in Power Automation - 

https://github.com/sumurthy/officescripts-projects/tree/main/Excel%20Calculation

Please message if you need help. 

[jtao]

Thanks for your suggestion, Sudhi

I have read the Type Script and videos.  It is required to add the codes to the Excel file in order to be run by Power Automate (Excel - Run script)

However, the client will get the file generated by 3rd party software and supply daily.  It is impossible to add a piece of code manually.  The whole process needed to be done by Power Automate but may be more complicated.

[jtao]

This should be common request.  If anyone has any tips or experiences, please advise !

My boss is waiting for the answer.

[jtao]

This has not been resolved yet.  I am desperately waiting for any suggestions.

[bradleyhorton]

If you create an array which is a list of unique managers, then use apply to each and filter the excel array in this you'll be left with a,b,c,d for David and f,g,h for Stephen. 

[Paulie78 · Accepted solution]

Make your boss happy....

I built the flow from your data and recorded a video of it. I didn't edit it so it is a bit rough, but you should be able to follow along. Apologies for the sound of my son playing video games in the background and me vaping in the foreground 😂

https://www.youtube.com/watch?v=SAlTMbC_4lk&feature=youtu.be

It's still processing at YouTube, so you might have to wait a little while for the high definition version to become available.

[jtao]

Hi Paulie,

You are awesome !  I am very appreciated that you have made such an excellent video with steps by steps guide.  I have applied your solution with great success !

[Paulie78]

Glad it worked, sorry about the quality of the video, I just knocked it together quickly.