Solved

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

Posted on 2013-11-18
12
600 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

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

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
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…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

744 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