Loop through record set and delete duplicates items.

[jsdba]

If there a way i can remove duplicates from an email list? I pull these emails from a recordset. But i email may appear more than once and it doesn't look good e.g johndoe@mail.com;johndoe@mail.com;johndoe@mail.com; marydoe@mail.com;johndoe@mail.com. I'd like to remove the duplicated email, if its possible. Code below.

Set rs = CurrentDb.OpenRecordset("select * from query")
    With objMailItem
          If rs.RecordCount > 0 Then
              rs.MoveFirst
              Do
                  
                 If Not IsNull(rs![email]) Then
                      vRecipientList = vRecipientList & rs![email] & ";" 
                      rs.MoveNext
                 Else
                      rs.MoveNext
                 End If

            Loop Until rs.EOF

.To = vRecipientList

 

[CJ_London]

why not use a select distinct when you pull the email addresses from the query?

Set rs = CurrentDb.OpenRecordset("select DISTINCT Email from query")

 

[jsdba]

why not use a select distinct when you pull the email addresses from the query?

DISTINCT will definitely help in filtering out most duplicates, but i'm actually pulling two emails from each record. (which i did not explicitly show in my first post) Then there's a matter of some updates statement where i'm using the same query. CJ let me run with this and see where it goes. Ideally i would've like a way to weed out duplicates from vRecipientList. Its probably more hassle than its worth.

 

[CJ_London]

'weeding out' of a recordset is going to be harder than providing the right recordset in the first place

 

[jsdba]

'weeding out' of a recordset is going to be harder than providing the right recordset in the first place

Each record has two email. email1 and email2. Both emails can potentially be identical. Example. An account manager (email1) can be the sales rep as well (email2). Therefore my record can have johndoe@mail.com, johndoe@mail.com. See attached picture.

If there's a way i can find exact matches and remove them from my email list that would be nice.

 

[CJ_London]

Sounds like your data is not normalised which is why you are having problems

suggest modify your query to something like

select distinct email1 as E1, iif(email2<>email1,email2,null) As E2 from myemailfile

then you'll need to modify your

vRecipientList = vRecipientList & rs! & ";" [/QUOTE]

line to ignore the null

 

[jsdba]

Sounds like your data is not normalised which is why you are having problems

suggest modify your query to something like

select distinct email1 as E1, iif(email2<>email1,email2,null) As E2 from myemailfile

then you'll need to modify your

line to ignore the null

I like this idea. Much appreciated. And you're right this table could be further normalized but all the while i was only tracking one entity (sales rep), i didn't see the need for normalization. I think now i don't have a choice. FML