Categorize Contacts with bad addresses

Last reviewed on August 4, 2014   —  No comments yet

A user in our Outlook Forums wanted to know how to filter and delete contacts that contain addresses that bounce. While Outlook doesn't have an automated method for deleting contacts that contain bad addresses, you can use VBA to add a category to the contacts. I recommend adding a category and manually deleting the contacts (or move them to a folder, if you want to keep a history). If you want to delete contacts using a macro, you need to loop backwards, otherwise you'll skip some matches.

Note: the code as written replaces the categories on a contact. To add a category while keeping the existing categories, use myItem.Categories = myItem.Categories & ";Bounced"

If you use Outlook to manage mailing list, you can use the macro to unsubscribe contacts by changing it to use the sender's address.

Categorize Contacts from non-delivery reports

This code sample grabs addresses from non-delivery reports (NDR). It gets the first email address in a message body, such as you'd find in an NDR then looks up the contact and adds a color category.

NDR in message body

To use it, select the NDR reports and run the macro.

Sub GetValueUsingRegEx3()
    Dim obj As Object
    Dim Selection As Selection
    Dim olMail As Object 'Outlook.MailItem
    Dim Reg1 As Object
    Dim M1 As Object
    Dim M As Object
    Dim strAddress As String
    Dim myContacts As Items
    Dim myItem As ContactItem

      
    Set Selection = Application.ActiveExplorer.Selection
    Set myContacts = Session.GetDefaultFolder(olFolderContacts).Items

  For Each obj In Selection
    Set olMail = obj

   Set Reg1 = CreateObject("VBScript.RegExp")
     With Reg1
       .Pattern = "(([\w-\.]*\@[\w-\.]*)\s*)"
       .IgnoreCase = True
       .Global = False
    End With
    
    If Reg1.test(olMail.Body) Then
     
        Set M1 = Reg1.Execute(olMail.Body)
        For Each M In M1
             strAddress = M.SubMatches(1)
            Debug.Print strAddress
            
        Set myItem = myContacts.Find("[Email1Address]=" & strAddress)
         
        If TypeName(myItem) = "ContactItem" Then
          If Not TypeName(myItem) = "Nothing" Then
            myItem.Categories = myItem.Categories & ";Delete"
                Debug.Print strAddress & " Delete"
            myItem.Save
         End If
        End If
    
       Next
       
    End If
Next
End Sub

Categorize Sender's Contacts

This version of the macro gets the Sender's email address, finds sender's contact and adds a category to it. Use this to process unsubscription requests.

To use select messages and run the macro.

Public Sub FindCaontactChange()
    Dim Selection As Selection
    Dim currentItem As Object
    Dim objMail As Object
    Dim strAddress As String
    Dim myContacts As Items
    Dim myItem As ContactItem


    Set Selection = Application.ActiveExplorer.Selection
    Set myContacts = Session.GetDefaultFolder(olFolderContacts).Items

    On Error Resume Next

    For Each objMail In Selection
     
        strAddress = objMail.SenderEmailAddress
    Debug.Print strAddress
    
    Set myItem = myContacts.Find("[Email1Address]=" & strAddress)
    If TypeName(myItem) = "ContactItem" Then
      If Not TypeName(myItem) = "Nothing" Then
  ' Match found
        myItem.Categories = "Unsubscribe"
            Debug.Print strAddress & " Unsubscribe"
        myItem.Save
     End If
    End If
           
        Err.Clear
    Next

    Set myItem = Nothing
    Set myContacts = Nothing
    Set objMail = Nothing
    Set currentItem = Nothing
    Set Selection = Nothing

End Sub

Working with a string of addresses

To use this macro, you need a comma separated list of addresses. This can be a list of addresses in Excel or sender's addresses in Outlook (use a custom view containing only the email address field).

Sub DeleteaNDRContact()
   Dim myContacts As Items
   Dim myItem As ContactItem

   Set myContacts = Session.GetDefaultFolder(olFolderContacts).Items
 
For Each myItem In myContacts
  Debug.Print myItem.Email1Address

Select Case myItem.Email1Address
Case "AccountStatus@GoDaddy.com", "poremsky@hotmail.com", "poremsky@live.com", "tips@outlooktips.net"
   myItem.Categories = "Bounced"
   myItem.Save
End Select

Next
End Sub

How to use macros

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

  1. Right click on Project1 and choose Insert > Module
  2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

Leave a Reply

If the Post Coment button disappears, press your Tab key.