Sort a datatable and send a mail according to the sorted data

Hello Friend,

I need your help for an Excel automation via datatable.

I had attached a sample of the Excel file.

i had to send mail based on the value of a colomn:

image

for each reference cell i had to send an email like this:

"to: EMAIL PRO ( for this Reference Cell)
subject: “refrence cell”.
Body:
Hello,
Please find the files for “code gestion” (for this reference cell) :
-name1 surname1 (for this reference cell)
-name2 surname2 (for this reference cell)
"

i think i had to sort the datatable by reference cell, do some other tricks, have you any idea to share with me?
sample.xlsx (23.9 KB)

Hello @abdel ,

Have a look to sort the datatable.

1 Like

Hello @abdel ,

To Sort “Reference cell” column in ascending order :
InputDt = (InputDt.AsEnumerable.OrderBy(Function(x) x(“Reference cell”).ToString)).CopyToDataTable

To Sort “Reference cell” column in descending order :
InputDt = (InputDt.AsEnumerable.OrderByDescending(Function(x) x(“Reference cell”).ToString)).CopyToDataTable

Please refer to the following thread which will help you to understand how to sort the data in table based on 1 column or multiple columns and in different orders.

Regards,
Rohith Prabhu

1 Like

@rohith.prabhu, it is easy to do the sort, but it how to send email by “Reference cell” ?

hello @abdel ,

I’m not clear with your requirement. Can you please elaborate more on how do you want to send the email, what data needs to be extracted or where you’re facing issue.

@rohith.prabhu in the Excel sample there is a column named “Reference cell”.

i need to send 1 mail per group of “Reference cell”, eg (in the sample)

i will send 1 mail for “141” , in subject of the mail i put this Reference cell", and in the boy i will write all the “Pays de naissance” related to this “cell reference” here:

in the body of the mail :
“Non trouvé”,
“Non trouvé”,
“France”,
“France”

For 420, in the body of the mail : :

“France”,
“Tunisie”.

for 230, in the body of the mail :
“France”,
“France”

…and so on

@abdel

To group the “Reference cell” and sort in ascending order :

InputDt= InputDT.AsEnumerable().GroupBy(Function(i) i(“ColumnName”).ToString).Select(Function(x)x.OrderBy(Function(y)y(“ColumnName”).ToString).Last).CopyToDataTable()

This will give you grouped result (single value) in ascending order.

Now you can loop over the InputDT and send email.