Solved

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

Posted on 2013-11-18
12
968 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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