Solved

Removing duplicate email addresses in string

Posted on 2011-09-15
11
264 Views
Last Modified: 2012-05-12
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.
 
0
Comment
Question by:foxpc123
11 Comments
 
LVL 10

Expert Comment

by:CSIPComputing
ID: 36542454
Can you use a Select DISTICT command when extracting the email addresses from Access?

http://www.w3schools.com/sql/sql_distinct.asp
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36542501
foxpc123,

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.

Patrick
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36542551
You can use following SQL to get rid of Duplicates

Select Email From Tablename group by Email

using that you creat Another table
0
 
LVL 3

Author Comment

by:foxpc123
ID: 36542597
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.
0
 
LVL 10

Accepted Solution

by:
CSIPComputing earned 250 total points
ID: 36542681
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,";")
    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
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!

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Assisted Solution

by:foxpc123
foxpc123 earned 0 total points
ID: 36542786
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
Wend

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

Expert Comment

by:CSIPComputing
ID: 36542803
strEmails will come back empty.

strDeduped should contain your list of unique emails.

0
 
LVL 3

Author Comment

by:foxpc123
ID: 36542809
Ignore above comment. Realised the email list is now stored in strDeduped !

Just trying code
0
 
LVL 3

Author Closing Comment

by:foxpc123
ID: 36565461
Modified CSIPComputing solution and worked a treat.
0
 
LVL 3

Author Comment

by:foxpc123
ID: 36544488
Modified CSIP solution and now works a treat.
0
 
LVL 10

Expert Comment

by:CSIPComputing
ID: 36544517
Can you post the modified code for the benefit of others?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Exiting a Form/Subform does not work correctly 9 16
Access Date Query 28 29
Code editor Problem 8 16
Join vs where 2 0
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now