Solved

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

Posted on 2013-11-18
12
658 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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Resolve DNS query failed errors for Exchange
Read this checklist to learn more about the 15 things you should never include in an email signature.
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

919 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

13 Experts available now in Live!

Get 1:1 Help Now