Sending email to unique list of Managers with the list of their users

[Kashif_ptcl]

Hi,

I'm fetching data from excel where I have to pick the unique manager names and their email addresses and send an email to all the Managers with the list of the users reporting to them who have not completed the online training. The Manager should receive one email with the complete list of users and I have to dynamically set/pickup the Manager name in the email body. 

The dataset looks like below:

Name	Manager	Email	Video
User 1	Mark	dummy@example.com	Module 2
User 2	Riley	abc@hotmail.com	Module 2
User 3	Tony	xyz@gmail.com	Module 2
User 7	Riley	abc@hotmail.com	Module 2
User 4	Tom	jjj@abc.com	Module 2
User 5	Mark	dummy@example.com	Module 2
User 6	Mark	dummy@example.com	Module 2

With reference to the above dataset, Mark should receive one email with the list of three users (User 1, User 5, User 6) 

The body of the email

Hi [Manager],

The following people have failed to attend the [Video] training:

- 

- 

[Rahber]

Hi ,

You can use the following article as starting point https://powerusers.microsoft.com/t5/General-Power-Automate/Read-rows-Excel-and-send-mail/td-p/45195

and this

https://powerusers.microsoft.com/t5/Building-Flows/Consolidated-email-based-on-date-in-excel/m-p/471...

Please click Accept as Solution if it resolved your problem or give it a Thumbs Up if it helped you in anyway this will allow other people to search correct solutions effectively.

Thanks,
Rahber

[Kashif_ptcl]

Hi Rahber,

Thanks for your reply. I already know and have read both the articles, in fact, I have built the flow which is working perfectly fine, it's reading the data from excel, filtering the records from excel based on criteria and I'm also able to filter the unique values from a particular field and successfully sending the emails. The only challenge I'm facing is not able to get the Manager's name(one of the fields in excel) dynamically in the email body. 

[faustocapellanj]

Hi @Kashif_ptcl 

Can you please post screenshots of your flow to see what you have done so far?

Thanks.

[Kashif_ptcl]

Please see attached the screen shots, the workflow is running fine except from pulling the Manager Name dynamically in the email 

body. 

[faustocapellanj]

Hi @Kashif_ptcl 

I have attached a file with a flow I created around your requirements. There are a few things you need to change, such as column names to match the columns in your Excel file. I tried to create an Excel file with the same columns you have, but the Excel screenshot didn't seem to show all the columns your file has. Please import it in your tenant, make the necessary changes to match your side and test it. Once done, please let me know if it worked.

Regards,

[Kashif_ptcl]

Thanks .

Yes I will definitely have a look and implement it by changing the column names. 

[Kashif_ptcl]

Hi 

I have been trying to import the flow package under the solutions import but I'm getting the below error.

 

[Kashif_ptcl]

Hi 

Moreover, I have also tried importing the package file under the My flows Import. The package gets uploaded but the Import button is not enabled. See below. 

[faustocapellanj]

Hi @Kashif_ptcl 

You have to click the Action button for each of the 3 resources listed and set up the respective connection for each one.

[Kashif_ptcl]

Thanks Fausto.

I'm getting the below error while imprinting.

[faustocapellanj · Accepted solution]

Hi @Kashif_ptcl 

Please see the screenshots below for each action in the flow

1. Manual Trigger
   
   
   
2. Initialize variable (renamed to Initialize varManagers)
   
   
   
3. Initialize variable (renamed to Initialize varUsers)
   
   
   
4. List rows present in a table
   
   
   
5. Select
   
   
   
6. Apply to each (renamed to ForEach Manager)
   
   
   1. Compose (renamed to Create Manager Array)
      
      
      
   2. Set variable (renamed to Set varManagers)
      
      
      
7. Apply to each (renamed to ForEach User)
   
   
   
   1. Compose (renamed to Manager Info)
      
      
      
   2. Filter array (renamed to Filter Response By Manager)
      
      
      
   3. Select (renamed to Select Response Info)
      
      
      
   4. Create HTML table
      
      
      
   5. Send an email (V2)
      
      

[Kashif_ptcl]

Hi @faustocapellanj 

I have just implemented and executed your solution. It worked perfectly fine and the way I required.

Thanks for all your help, assistance and responses to overcome the problem by meeting the exact requirement. 

[faustocapellanj]

Hi @Kashif_ptcl 

I'm glad to hear the solution worked for you.

Regards,

[ky3]

Hi faustocapellanj, 

Thank you for the awesome script. 

For my case, i would like to include column 'Name' (assume this is the email list) in the CC email list.

I read few articles but not 100% clear.

Would you please share your flow how it can be customized further to include the CC email?

Thank you.