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

[davidgoderre]

I am looking to some how filter the below datable table so that i only send emails to the email identified in column d, but also send emails to all the people in column c. See table below and compose emails to understand my goal.

ID Task Name Assigned Created
1 Testing Task 1 ryanATyahoo; oliverATyahoo johnATyahoo
2 Testing Task 2 jasonATyahoo; karenATyahoo karenATyahoo
3 Testing Task 3 harryATyahoo; jakeATyahoo karenATyahoo
4 Testing Task 4 paulATyahoo; brianATyahoo johnATyahoo
5 Testing Task 5 TaraATyahoo;kellyATyahoo shaneATyahoo

Id like to limit the number of emails to as few as possible. So I’d like to send emails to the unique people in the created column. But id like to CC all the people in the rows where John is the creator, and include the Task Name in the body of the email. So something like the below email

To: JohnATyahoo
CC: ryanATyahoo; oliverATyahoo; paulATyahoo; brianATyahoo
Subject: Tasks Have Been Processed
Body:
ID Task Name Assigned Created
1 Testing Task 1 ryanATyahoo; oliverATyahoo johnATyahoo
4 Testing Task 4 paulATyahoo; brianATyahoo johnATyahoo

Then Karen’s email would be like the below

To: karenATyahoo.
CC: jasonATyahoo; karenATyahoo; harryATyahoo; jakeATyahoo
Subject: Tasks Have Been Processed
Body:
ID Task Name Assigned Created
2 Testing Task 2 jasonATyahoo; karen@yahoo karenATyahoo.com
3 Testing Task 3 harryATyahoo; jake@yahoo karenATyahoo.com

Finally Shane’s email would look like the below.

To: shaneATyahoo
CC: TaraATyahoo;kellyATyahoo
Subject: Tasks Have Been Processed
Body:
ID Task Name Assigned Created
5 Testing Task 5 TaraATyahoo;kellyATyahoo shaneATyahoo

My issue is figuring out how to filter the datatable and loop through to send out the emails like I described above. The names in the created column are unknown to me so i can’t hard code any of the To: emails. I need to retrieve it from the table at run time.

Any help would be greatly appreciated!

[jack.chan]

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 = String.Join(";", dt.AsEnumerable.Where(function(row) row("Created").ToString.Equals(currentCreator)).Select(function(row) 
row("Assigned").ToString))
'this means get all assigned email where creator = current creator

c) use Convert_DataTable_X_HTML.XAML to convert 
dt.AsEnumerable.Where(function(row) row("Created").ToString.Equals(currentCreator)).CopyToDataTable
to string dtHTML
'this means get table where creator = current creator
body = dtHTML

input (test.xlsx)

Result
1.

[davidgoderre]

this was perfect thank you so much for the help!!!