• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Removing duplicate email addresses in string

Using access 2007 vba, I have a string of email addresses which have been extracted from access query. This query uses two tables, each of which contain email addresses.

I have copied the email addresses into a string (strRecipients), each email address seperated by semi-colon (;).

My problem is this string can contain duplicate email addresses.

Any ideas on how I would remove any duplicate email addresses, so the recipient will only receive one email.
2 Solutions
Can you use a Select DISTICT command when extracting the email addresses from Access?

Patrick MatthewsCommented:

Please give us some more background on what you need to do and what your source data are like.

For example, if any given value in the source data is always a single email address, then CSIPComputing's suggestion of DISTINCT (or potentially GROUP BY) may work.  If any given value may itself contain >1 email address, different techniques are called for.

You can use following SQL to get rid of Duplicates

Select Email From Tablename group by Email

using that you creat Another table
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

foxpc123Author Commented:
The query extracts company names and email address from one table 'Companies' and Contact names and email addresses from table 'company contacts'. Some company contacts may have the same email addresses (in table company contacts) as the email address from the table Companies.

I cycle throuth the query data and extract the email address (based on certain criteria) from 'companies.email' and from 'Company Contacts.email'. This is were there could be duplications.

I really need a way to sort the data and remove the duplicate emails withing the string.
OK, excuse the pseudo code here, I'm not a developer, but assuming, as you say, the email addresses are separated by semi-colons(;), and assuming your list of emails is in strEmails, try something along these lines:

DIM strDeduped as String = ""
DIM intLocation as Integer=0
DIM strCurrentEmail as String = ""

Do While instr(strEmails,";")

    If Instr(strDeduped,strCurrentEmail)=0 then
        strDeduped=strDeduped + strCurrentEmail
    End If
    strEmails=right(strEmails,len(strEmails)-intLocation) ' Trim the email from the front of the string    
End Do
You should now have a de duplicated list of emails in strDeduped.

It may take a bit of fine-tuning, and putting into the correct code format, but the theory is there!

foxpc123Author Commented:
Thanks for reply. Tried following code:

Dim strDeduped As String
Dim intLocation As Integer
Dim strCurrentEmail As String
intLocation = 0
strEmails = strRecipients
While InStr(strEmails, ";")
    intLocation = InStr(strEmails, ";")
    strCurrentEmail = Left$(strEmails, intLocation - 1)

    If InStr(strDeduped, strCurrentEmail) = 0 Then
        strDeduped = strDeduped + strCurrentEmail
    End If
    strEmails = Right(strEmails, Len(strEmails) - intLocation) ' Trim the email from the front of the string
    strCurrentEmail = ""
    intLocation = 0

But strEmails comes back as empty. What am I doing wrong.
strReceipients is my list of email addresses which I copy into areEmails
strEmails will come back empty.

strDeduped should contain your list of unique emails.

foxpc123Author Commented:
Ignore above comment. Realised the email list is now stored in strDeduped !

Just trying code
foxpc123Author Commented:
Modified CSIPComputing solution and worked a treat.
foxpc123Author Commented:
Modified CSIP solution and now works a treat.
Can you post the modified code for the benefit of others?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now