Solved

MS-Excel: How to check duplicate email addresess in the "To"  Box

Posted on 2013-11-18
12
769 Views
1 Endorsement
Last Modified: 2016-04-20
Hi Experts,

This is important when you want to send an email to too many recipients...

How to check duplicate email addresses in the recipients' email addresses or names in the To, Cc, or Bcc box before sending the email?

Any idea?

Note:
Maybe VBA script (macros) can help on this.
1
Comment
Question by:zakwithu2012
  • 6
  • 5
12 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39657237
Hi there,

can you describe how you enter the email addresses in the to/cc/bcc boxes?


Joop
0
 

Author Comment

by:zakwithu2012
ID: 39658137
Normal process like any email you want to send.
Part of them is manually copied from diffrent old emails. Part of them entered manually by typing in the TO box. Each email address separated by simi-comma (;) from the next email address.
0
 

Author Comment

by:zakwithu2012
ID: 39658149
The only diffrence is that I want to send an email to too many people... you can say more than 50 persons. Since I'm copying the email addresses from diffrent areas there might be a chance of duplicated email... I dont want these emails addresses to recieve my email twice or more than once.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 6

Expert Comment

by:Michael
ID: 39658640
You could to do this without any vba by copying all email addresses to Excel and use remove duplicates and copy the filtered list back to the outlook message.

Or you can add a macro to Outlook, add a button to the ribbon of the message window and assign the macro to the button.

Which do you prefer?

Joop
0
 

Author Comment

by:zakwithu2012
ID: 39659026
I was already using the first option. It is old fashioned option unfortunately. you can imagine  how diffi used to send such big emails (contain huge number of email addresses in the "TO" box of the outlook) it is too difficult to use excel every 2 hours. and by the way youe need to copy all addreses from outlook "TO" box and paste it into excel then you need to manually put each email address in one raw. after that you will be able to remove duplicates....


My taregt here is to use something within the outlook itself. the macro idea seems to be good.

tell me what code to be inserted in the macro? i didn't understood this:

Or you can add a macro to Outlook, add a button to the ribbon of the message window and assign the macro to the button.

Open in new window


what macro i need to assign?
0
 
LVL 6

Expert Comment

by:Michael
ID: 39659049
You could use the following macro:
Sub chkDuplicates()
    Dim i As Integer, j As Integer
    Dim olMail As MailItem
    Dim olRecip1 As Recipient, olRecip2 As Recipient
    Dim colRecipients As Recipients
    
    Set olMail = ActiveInspector.CurrentItem
    Set colRecipients = olMail.Recipients
    For i = colRecipients.Count To 1 Step -1
        Set olRecip1 = colRecipients.Item(i)
        For j = (i - 1) To 1 Step -1
            Set olRecip2 = colRecipients.Item(j)
            If olRecip1.Address = olRecip2.Address Then
                olRecip1.Delete
                Exit For
            End If
        Next
    Next
End Sub

Open in new window

Are you familiar with how to add macros to Outlook?
And how to add a button to the ribbon and assign a macro to it?

Joop
0
 

Author Comment

by:zakwithu2012
ID: 39659120
Hi Joop,

works like a charm :-)

can you just amend the above code to log (in a flat file or any where) the email addresses that have been deleted? i just need to double check it is working fine.


waiting for your amendment.
0
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
ID: 39659213
Sure. Then it would be something like this:
Sub chkDuplicates()
    Dim i As Integer, j As Integer
    Dim olMail As MailItem
    Dim olRecip1 As Recipient, olRecip2 As Recipient
    Dim colRecipients As Recipients
    Dim fs As Object, a As Object
    Dim myFile As String
    
    myFile = "C:\temp\duplicates " & format(now,"mm-dd-yyyy") & ".txt"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(myFile, True)
    
    Set olMail = ActiveInspector.CurrentItem
    Set colRecipients = olMail.Recipients
    For i = colRecipients.Count To 1 Step -1
        Set olRecip1 = colRecipients.Item(i)
        For j = (i - 1) To 1 Step -1
            Set olRecip2 = colRecipients.Item(j)
            If olRecip1.Address = olRecip2.Address Then
                a.WriteLine (olRecip1.Address)
                olRecip1.Delete
                Exit For
            End If
        Next
    Next
    a.Close
End Sub

Open in new window

Joop
1
 

Author Comment

by:zakwithu2012
ID: 39659288
it works like a charm 100/100

great many thanks big boss: Joop
0
 

Author Closing Comment

by:zakwithu2012
ID: 39659292
thanks Joop...

it works fine exactly as i was expecting.... you made my job easier ;-) i think i will make a show up to my management hopefully i will get bonus this year.

thanks experts-exchange.com

regards,
0
 
LVL 6

Expert Comment

by:Michael
ID: 39659321
Glad I could help!

Regards,
Joop

PS. Let me know about the bonus ;)
0
 

Expert Comment

by:iljonas
ID: 41558223
Hello All,

Im creating emails from ExcelVBA,

There is a way to run this from VBA directly? as... Call the Macros stored in Outlook from VBAExcel?

Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can't get rid of Update Links message in Excel 2013 8 32
SKip past fields with no data 6 16
NEED TRANSFER  DATA 59 22
Index Match Formula VBA 6 21
What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

828 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