Loop multiple cc using vlookup

Help Activities
, ,
1

Hi all, I am currently working on a project which sends out reminder emails to reporting manager for contracts that are expiring, department heads have to be cc-ed in the email too, we have manage to complete the following,

  1. Sort contracts according to reporting manager email, vlookup another excel (master list) to include the department head to be cc-ed in the email.

  2. Currently the reminders are sent out by sorting the reporting manager emails, however if the manager is taking care of more than one department, only one department head get cc-ed in the email.

I have attached the part where we generate emails.

How can I create a loop to ensure that both departments head get cc-ed in the email?

2

@weelearns

Try using this

String.Join(",",Sort_After_Admin_Vlookup.AsEnumerable.Select(function(x) x(10).ToString).ToArray.Distinct)

I hope this datatable Sort_After_Admin_Vlookup has only rows for one manager and all the department headers under him

Cheers

3

Hi @Anil_G, thanks for the quick reply, sorry may i ask where should i add this?

4

@weelearns

Here

image
image948×466 16.8 KB

cheers

5

Hi Anil, this is not working, it copied the whole email column, heres a sample excel,

image
image846×121 4.01 KB

The current setup will send and email to Alan and cc John, we would like to cc Celia too. and it will continue in a loop, the next reminder email will be sent Tom, cc peter.

6

@weelearns

then use a filter datatable and filter the input table on primary manager in loop…and then use the given expression on the filtered table…then you will have cc of only Alan related Dept heads

the same is mentioned here

cheers

7

May I check what should the value be in this case to filter according the same manager?
image

8

@weelearns

You can filter with recipient…the email id of manager that you are extracting

Cheers

9

Hi @weelearns

to get the list of department heads emails id for cc

  1. Once you sorted the datatable for reporting manager, loop through that datatable, then do the following operation

str_cc = String.Join(“;”,Master_dt.AsEnumerable().Where(Function(r) r(“Primary Manager Email”).ToString.Trim.Equals(row(“manager_email”).ToString.Trim)).Select(Function(e) e(“Dept head”).ToString.Trim).ToArray())

I hope the datatable which has contract details (including manager email id) have email id of manager in column manager_email, if not you can change the manager_email column name with desired one.

here i am looping through the sorted table which has the reporting manager email id , that is why in above query we have row variables which represent the row through which it is being iterated.

the above variable str_cc will give the emails of dept head in string seperated by ;

Let me know if this approach helps you

Thanks & Regards,
Nived N

10

@Anil_G, i tried to filter by manager email, created a variable above. is there soemthing wrong with the way i filter the manager?

image
image980×571 87 KB

but got an error at the next section - Add Data Row: There is no row at position 0

11

@weelearns

I guess you are trying to filter manager name with manager email…

Can you check that

Cheers

12

Hi @Anil_G, is this correct?

image
image574×522 57 KB

How can i make it into a loop too ? I have added it under for each row, i hope thats correct

image
image1073×773 65.5 KB

13

@weelearns

I hope in the loop you are getting each manager…

Else you can get unique manager list in a separate datatable amd then loop on it

To get all unique managers list use uniquedt = inputdt.DefaultView.ToTable(True,"Primary Manager Email") - this has to be used in assign

Hope this helps

Cheers

14

Hi Anil, i tried both methods but still unable to run the script…
Flowchart.2023.03.21 - Copy (1).xaml (34.7 KB)