Pick out unique email addresses from 2 columns and paste the unique email addresses in the BCC field of send smtp activity

[ayushi_jain3 (ayushi jain)]

Hello,

1. There is one excel with 2 columns which have email addresses in it.
2. I need to pick unique email addresses from both the columns and paste those unique email addresses in the BCC field of send smtp activity.
3. Also, please let me know how can I test this in studio for my personal email account?

Thanks in advance ! :slight_sm Hello,

[Palaniyappan (Palaniyappan P)]

Hi

Hope the below steps would help you resolve this

1. Let’s take u have read that file and kept as datatable

2. Now use a assign activity and get the unique records from these two columns
   Using

newDT = DT.DefaultView.ToTable(True,“col1”,“col2”)

Where newDT is a variable of type datatable

3. Now use can use FOR EACH ROW loop and pass this datatable newDT as input and inside the loop use can use send mail activity and call each column value as
   CurrentRow(“yourcolumnname_1”).ToString+”;”+ CurrentRow(“yourcolumnname_2”).ToString

Cheers @ayushi_jain3

[ayushi_jain3 (ayushi jain)]

hello @Palaniyappan ,

Thanks for quick solution. Could you please breakdown step3. It’s a bit confusing.

Thanks in advance !

[Palaniyappan (Palaniyappan P)]

@ayushi_jain3

need to pick unique email addresses from both the columns and paste those unique email addresses in the BCC field of send smtp activity

That was related to this point

So once u have got the unique value in datatable if you want to get both the value the u can pass the datatable in loop and get the each column value and combine them with a delimiter ;
This we usually do by putting , or ; when we want to include multiple mail ids while sending email

U can include any delimiter that you need
I just put ; for an example

So that is

1. Pass the datatable to for each row loop
2. Inside the loop use a assign activity and get the mail id from two columns like this using a assign activity

Strmailid = CurrentRow(“yourcolumnname_1”).ToString+”;”+ CurrentRow(“yourcolumnname_2”).ToString

Cheers @ayushi_jain3

[ayushi_jain3 (ayushi jain)]

Encountering this error:

Do you mind sending xaml file for the solution please?

Thanks in advance !

[Palaniyappan (Palaniyappan P)]

Oh my bad
That’s a typo
Remove CopyToDatatable
Just put like this

newDT = DT.DefaultView.ToTable(True,“col1”,“col2”)

@ayushi_jain3

[ayushi_jain3 (ayushi jain)]

Still getting an error:

[postwick (Paul Ostwick)]

@Palaniyappan

newDT = DT.DefaultView.ToTable(True,“col1”,“col2”)

I am not sure this will provide the desired results. Should it be done once for each column then merged? Then done again for the final table in case there are duplicates across the columns?

[postwick (Paul Ostwick)]

You have to put a comma after true. They’re separate parameters.

[ayushi_jain3 (ayushi jain)]

Hey @postwick ,

2 columns need to be merged, find unique values(emails) and those unique emails need to be pasted in bcc field of send smtp activity

[postwick (Paul Ostwick)]

Then Read Range into dt1 with range set to the starting cell for the first column, Read Range into dt2 with the range set to the starting cell for the second column, then Merge Data Table into dt_final then assign dt_final = dt_final.DefaultView.ToTable(True,“column name”) then String.Join(“,”,dt_final.AsEnumerable.Select(Function (a) a.Field(of string)(“column name”).ToString).ToArray) to get it into a string for the email address field.

[ayushi_jain3 (ayushi jain)]

@Palaniyappan

CurrentRow(“yourcolumnname_1”).ToString+”;”+ CurrentRow(“yourcolumnname_2”).ToString

I have 2 questions…

1. What should be the “column name” in line 3rd you are referring to ?
2. for merging 2 datatables should I use merge datable activity?

Thanks

[postwick (Paul Ostwick)]

@ayushi_jain3

What should be the “column name” in line 3rd you are referring to ?

Whatever the resulting column name is in final_dt. I suggest the first two Read Range activities do NOT use the headers from the Excel files, to ensure both datatables just have a column name of Column0, to ensure the merge works correctly. Then you can use that in the DefaultView.ToTable expression.

Yes use Merge Data Table.

The goal here is to combine both columns into a new datatable with just one column of email addresses (using Read Range once for each column, then Merge Data Table). Then the DefaultView expression will give you unique email addresses. The final expression converts it to a comma delimited list for the send email activity.

Note that I’m used to using Send SMTP Mail Message and it wants a comma delimited list. If you’re using a different email activity it may want semicolon (;) delimited.

[Palaniyappan (Palaniyappan P)]

@ayushi_jain3

There is one excel with 2 columns which have email addresses in it.

Fine

This is the detailed steps

1. Hope u have an excel file with two columns say “columnA” and “columnB” which is read with read range activity named dt

2. Now let’s get the unique records from this datatable dt

Use a assign activity like this

dt_1 = dt.DefaultView.ToTable(True, “columnA”, “columnA”)

Where dt_1 is a variable of type datatable

Use another assign like this

dt_2 = dt.DefaultView.ToTable(True, “columnA”, “columnB”)

3. Use add datacolumn activity where pass datatable as dt_1 and in datacolumn property mention as dt_2.Columns(“your columnname”)

So now the datatable dt has both the columns with it unique values

4. Now use a For each row for datatable activity and pass dt_1 as input

5.To get the mail id together from
Both the columns which has unique value now, use a assign activity like this

Strmailid = CurrentRow(“ColumnA”).ToString+”;”+ CurrentRow(“ColumnB”).ToString

If u want to get them as separate value then in assign activity

Strmailid_1 = CurrentRow(“ColumnA”).ToString

Strmailid_2 = CurrentRow(“ColumnB”).ToString

Here ColumnA and columnB are column header names
Id header name is not there then use column index like this

Strmailid_1 = CurrentRow(column index).ToString

Where column index start from 0 for first column so it would be like

Strmailid_1 = CurrentRow(0).ToString

Strmailid_2 = CurrentRow(1).ToString

Hope this helps
Cheers @ayushi_jain3

[postwick (Paul Ostwick)]

The goal is a comma delimited list of unique email addresses to use in the BCC field of the Send SMTP Mail Message activity, not to loop through the email addresses and send separate emails.

[ayushi_jain3 (ayushi jain)]

merge datatable option isn’t helping. can join datatable help?

[postwick (Paul Ostwick)]

No. What do you mean Merge isn’t helping? If both datatables have the same column name for their one column, Merge will give you back one datatable with all the data from both in one column.

[ayushi_jain3 (ayushi jain)]

So, the column names are different for both the datatables(assuming datatble1: column1 and datatble 2 as column2) . I am attaching the sample file for ref.
SampleFile Email.xlsx (9.6 KB)

[postwick (Paul Ostwick)]

As I said, uncheck the “use headers” box in your Read Range and it won’t pick up the column names from the Excel file. It’ll assign standard names, and since both datatables just have one column they’ll both have the same column name Column0

[ppr (Peter Preuss)]

We understand that picking up all Mails from all rows from 2 columns AND returning only the distinct MailIds as a flat comma separated string is targeted

Can do it within a single Assign Activity:

strFlatMailIDs =

String.Join(",", {"ColA","ColB"}.Select(Function (x) dtData.AsEnumerable().Select(Function (r) r(x).toString.Trim)).Distinct() )