Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-11-18
12
Medium Priority
?
1,092 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

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.

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

886 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