Group by and send email

image

Is there a “group by” function in UiPath

id 1 & 2 needs to be attached and send to abc@abc.com email address as a SINGLE email as it is to the same receiver
id 3 would be a single email attachment

As an example we can create a lookup dictionary where email is the key and value is a list of arrays

Assign Activity
dictLK | Dictionary(Of String, List(Of String) =

(From d in YourDataTableVar.AsEnumerable
Group d by k=d(“email add”).toString.Trim into grp=Group
Let il = grp.Select(Function (x) x(“id”).toString.Trim).toList
Select t = Tuple.Create(k,il).ToDictionary(Function (t)t.Item1, Function (t.Item2)

So you can iterate over the dictionary and can send the aggregated ids in each loop to the sender

Hi,

FYI, Approach of using Dictionary and LINQ GroupBy:

Sample20230125-9L.zip (6.2 KB)

Please note this is mock sample.

Regards,

Hi,

Thanks for the mock up,
i have 2 excels

  1. store the email master list of companies (DtEmail) ID appears on this master list
    2.store the attachment path which then i have to do a assign activity to get
    ie
    CurrentRow.(Name).tostring+CurrentRow.(ID).tostring+“.pdf”

the unique thing to match file to email address is through the ID

Hi,

Can you share specific input as file? it’s no problem if dummy data.

Regards,

EmailMaster.xlsx (9.2 KB)
MasterList.xlsx (8.6 KB)

Read MasterList append to get filename CurrentRow.(Name).tostring+CurrentRow.(ID).tostring+“.pdf” pick files from folder
Read EmailMaster to get Email address based on ID

Hi,

how about the following?

Sample20230125-9Lv2.zip (16.4 KB)

Regards,

Hi,

This solution works however for dtEmail i have additional row for CCs how should i include them in the dictionary?

image

HI,

Can you share the xlsx file?

Regards,

EmailMaster (1).xlsx (9.4 KB)

Hi,

It depends on which mail you use, however, if you use Desktop Outlook, the following will work.
(Because it can be used cc addresses as semicolon separated string)

 dictCcEmail = dtEmail.AsEnumerable.ToDictionary(Function(r) r("id").ToString,Function(r) r("Cc1").ToString+";"+r("Cc2").ToString)

Then

Sample20230125-9Lv3.zip (24.2 KB)

Regards,

Hi

It works, Thank you!!

1 Like

Would also like to update the MasterList with status "Done “+DateTime.Now.ToString”
MasterList.xlsx (8.7 KB)

Hi,

Yes it will work.
If you have many cc columns, the following might be better.

dtEmail.AsEnumerable.ToDictionary(Function(r) r("id").ToString,Function(r) String.Join(";",r.ItemArray.Skip(2)).Trim(";"c))
1 Like

Hi,

For this additional requirement, it might be better to use Dictionary<string, DataRow[]> type instead of Dictionary<string, DataTable>

Can you try the following sample?

Sample20230125-9Lv4.zip (31.6 KB)

Regards,

Hi
would like to write back into MasterList Excel file, method above would create another excel file results.xlsx

Try to modify result.xlsx to MasterList.xlsx

modifying it would replace the entire excel, would want to just add on the “Done and datetime”

Please use WriteCell activty and calculate target cell from row index of current index, as the following.

image

It might be better to raise new topic because it’s apart from title of this topic and it will help those who face same issue, I think.

Regards,

1 Like