Group by and send email

Help Studio
1

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

2

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

3

Hi,

FYI, Approach of using Dictionary and LINQ GroupBy:

image
image767×1280 36.7 KB

Sample20230125-9L.zip (6.2 KB)

Please note this is mock sample.

Regards,

4

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

5

Hi,

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

Regards,

6

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

7

Hi,

how about the following?

image
image767×1558 46.9 KB

Sample20230125-9Lv2.zip (16.4 KB)

Regards,

8

Hi,

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

image

9

HI,

Can you share the xlsx file?

Regards,

10

EmailMaster (1).xlsx (9.4 KB)

11

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

image
image1373×703 56.4 KB

Sample20230125-9Lv3.zip (24.2 KB)

Regards,

12

Hi

It works, Thank you!!

1 Like
14

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

15

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
16

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?

image
image923×2280 134 KB

Sample20230125-9Lv4.zip (31.6 KB)

Regards,

17

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

18

Try to modify result.xlsx to MasterList.xlsx

19

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

20

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
21

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.