Group by and send email to next cc reciepents doesnt work

[Simon1 (Simon Michalov)]

Hi experts,
I will be thankfull for any advice because I didnt find any solution and read lot of topics here:

I have source: excel sheet table like example:
Column 4------Column 5 (cc email)------Column 6(email reciepent)
A -----------AnotherReciever5@emai.com--------Reciever1@email.com
B-----------AnotherReciever6@emai.com---------Reciever1@email.com
C-----------AnotherReciever7@emai.com---------Reciever2@email.com

By bellow WF I grouped data by Column 6 because I need to send data in single email in table: for rows A+B to reciever1@email.com with CC to AnotherReciever5@email.com) ; AnotherReciever6@email.com) and single email in table: for rows C to Reciever2@email.com with CC to AnotherReciever7@emai.com

It is done by bellow WF, where I read range from excel sheet and then group by Column 6

To add CC recievers I create dictCC variabile= I read range from same excel sheet because CC email recievers are in Column 5, so I need to connect/vlookup Column 6 with to Column 5 and add them to dictionary: (this caused a problem, what is explain below) :

Then every item in data table is grouped= For each - currentItem … In- dictGroup and every grouped data are collected to listStrings

after that for each row in DT is appended to list with defined columns, next activity: Generate datatable from text creates a datatable and datatable is convert to HTML text with another activity and attached to email body as table…

In send emai activity in field „TO“ and „CC“ I have defined values:

I GOT TWO ERRORS THAT I AM NOT ABLE TO RESOLVE:

1. If in column 6 or column 5 is not any duplicate value, everything works as expected and email for data A is send TO Reciever1@email.com with CC AnotherReciever5@emai.com , but if in Columns 5 or Column6 are duplicate values (like in example below), I get error: „An item with the same key has already been added. Key: reciever1@email.com
   A -----------AnotherReciever5@emai.com--------Reciever1@email.com
   B-----------AnotherReciever6@emai.com---------Reciever1@email.com
   C-----------AnotherReciever7@emai.com---------Reciever2@email.com

2. In one excel sheet column is date format which is converted to list of string and attached to email body as table: so it change format from 1.2.2025 to 02/01/2025 00:00:00 , how I can avoid it? Format should be dd/mm/yyyy.

I read another topic that help me partially resolve first issue here:

Need help filtering a datable and sending out emails based on unique line items

alternative (note in test.xlsx i had to change your emails to valid emails e.g. oliverATyahoo to oliverAT@yahoo.com or else it will throw error) test.xlsx (9.7 KB) Main3.xaml (12.9 KB) Convert_DataTable_X_HTML.XAML (13.2 KB) 1. uniqueCreatorList (list of string variable) = dt.AsEnumerable.Select(function(row) row("Created").ToString).Distinct.tolist 'this means get all unique creators of datatable` 2. for each creator in unique creator list Send email: a) to = unique Creator b) cc = Strin…

cc variable works good:
cc = String.Join(“;”, dtGroup.AsEnumerable.Where(function(row) row(6).ToString.Equals(currentItem)).Select(function(row)
row(5).ToString))
is it any way how I can implement it to my WF? I cant use invoke code for datatable to HTML to get table in body email because read range activity cant read headers (duplicate columns names) so columns names are missing then, for me is better firstly append items to list and define the column names.

Thank you.
Simon.

[Anil_G (Anil Gorthi)]

@Simon1

Rather ready without column names and using create html contwnt create the table

Also after reading based on duplicates you can reqrite or if you have full list ready prepare list and add column names

Cheers

[Simon1 (Simon Michalov)]

Dear Anil,
thank you for your asnwer, rather I will use current WF where I will read range without column header and then with append list item activity I define which columns need to be included and by create datatable from text activity I am able to define column names based on data in list…
Regarding sending grouped email aslo to CC recievers, can you help me please? how I can send email also to CC recievers based on column6 (email recievers) as is explained above?
Thank you.

[Anil_G (Anil Gorthi)]

@Simon1

You need not get the data again and convert to dictionary and all…while grouping data anyways contains the emails for cc …so use that datatable directly

strinng.Join(";",Dt.AsEnumerable.Select(function(x) x(6).ToString)) this will get you all the cc emails as semi colon separated …use this directly inside group from the datatable created in you .ToDictionary

Cheers

[Simon1 (Simon Michalov)]

Dear Anil,
thank you so much it works perfect now, I put new assign value of String.Join(“;”,Dt.AsEnumerable.Select(function(x) x(6).ToString)) under existing assign activity for DictGroup

but if in table are duplicates email addresses, it put same email address double times in CC:
A -----------AnotherReciever5@emai.com--------Reciever1@email.com
B-----------AnotherReciever5@emai.com---------Reciever1@email.com

email for A+B will be send to Reciever1@email.com and in CC is AnotherReciever5@emai.com ; AnotherReciever5@emai.com (double time) but I know that it is because of String.Join(“;”,) so it is not really big problem for me and it could work by this way - thank you.

Can you advice please also for second issue?
In one excel sheet column is date format which is converted to list of string and attached to email body as table: so it change format from 1.2.2025 to 02/01/2025 00:00:00 , how I can avoid it? Format should be dd/mm/yyyy.

Thank you.
Simon.

[Anil_G (Anil Gorthi)]

@Simon1

For suplicates after .select add .Distinct…that will remove duplicates

For dates just parse them and it should work

Cdate(datestringhere).ToString("dd/MM/yyyy")

Cheers

[Simon1 (Simon Michalov)]

@Anil_G
thank you so much, Distinct works perfect and it resolved all issue that I have with cc reciepents.
Regarding date, I try to adjust form CurrentRow(3).ToString(“dd/mm/yyyy”)

It is defined in Append Item list activity, where I collect all data from xlsx sheet to list and then convert it to datatable from text:
CurrentRow(0).ToString+ “,” +CurrentRow(1).ToString+“,” +CurrentRow(2).ToString+“,” +CurrentRow(3).ToString(“dd/mm/yyyy”)+“,”+CurrentRow(4).ToString+vbCrLf

it thorws error BC30512: Option Strict On disallows implicit conversions from String to Integer

Thank you.
Simon.

[Anil_G (Anil Gorthi)]

@Simon1

you have to do like this Cdate(CurrentRow(3).ToString).ToString("dd/MM/yyyy")

also error looks different if using in assign looks like left side value is of type integer …convert to string

cheers

[Simon1 (Simon Michalov)]

Dear @Anil_G
it works great! I used “mm” instead of “MM”, so it is different? for month format, “MM” have to be used?

can you please explain to make it clear for me
strinng.Join(“;”,Dt.AsEnumerable.Select(function(x) x(6).ToString))
function(x) is used instead of function(r)? If I use (function(r) r(6).ToString it will be not correct?

Thank you.
Simon.

[Anil_G (Anil Gorthi)]

@Simon1

mm is minutes MM is month

in place of x anything can be used no problem

cheers