Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to update email addresses in contacts list?

    My company reciently merged 2 devisions and the email addresses will be different.
    I need to change many contacts from @abc.com or @def.com to @xyz.com.
    Using VBA.

  2. #2
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,072
    Thanks
    0
    Thanked 259 Times in 248 Posts
    I would wonder if it would work to Export the Contacts as a .csv/Comma Separated Value/Variable file, open it in Excel, QuattroPro, Lotus 1-2-3 or other spreadsheet program, use find and replace on each of the @abc.com and @def.com extensions with the new one of @xyz.com. Then Import that file into Contacts. Always back up the Contacts so you don't risk losing them. And I'd delete all the invalid Contacts so as not to have duplicates. It'll get more complicated if having Groups.

  3. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Did some coding but need more help.

    Thank you, Berton.
    I came up with a Sub from bits and peices i found, plus some VBA coding of my own, but it only changed some of the Email1Address matches. I am having trouble determining why its missing others. Any help will be appreciated.

    Code:
    Sub subABCtoXYZ()
    '
        Dim ns As NameSpace
        Dim olContacts As Folder
        Dim itemContact As ContactItem
        Dim colItems As Outlook.Items
    '
        Dim eMailAddress As Variant
        Dim varCngFrom As Variant
        Dim varCngTo As Variant
        Dim intAt As Integer
        Dim strMessage, strTitle, strDefault, strMyValue
    '
    '    strMessage = "Enter Email Address Book to Change"    ' Set prompt.
    '    strTitle = "Change Email Addresses"    ' Set title.
    '    strDefault = "Contacts"    ' Set default.
    ' Display message, title, and default value.
    '    alContacts = InputBox(strMessage, strTitle, strDefault)
    '
        strMessage = "Enter Email Address to Change From"    ' Set prompt.
        strTitle = "Change Email Addresses"    ' Set title.
        strDefault = "@ABC.com"    ' Set default.
    ' Display message, title, and default value.
        varCngFrom = InputBox(strMessage, strTitle, strDefault)
    '
        strMessage = "Enter Email Address to Change To"    ' Set prompt.
    '    strTitle = "Change Email Addresses"    ' Set title.
        strDefault = "@XYZ.com"    ' Set default.
    ' Display message, title, and default value.
        varCngTo = InputBox(strMessage, strTitle, strDefault)
    '
        Set ns = Application.GetNamespace("MAPI")
        Set olContacts = ns.GetDefaultFolder(olFolderContacts)
        Set colItems = olContacts.Items.Restrict("[MessageClass]='IPM.Contact'")
    '
        For Each itemContact In colItems
    '
            intAt = InStr(1, UCase(itemContact.Email1Address), UCase(varCngFrom))
    '
            If intAt > 0 Then
                eMailAddress = Left(itemContact.Email1Address, intAt - 1) & varCngTo
                strMyValue = MsgBox("Do you wish to change " & itemContact.Email1Address _
                & " to " & eMailAddress, vbYesNo)
                
                If strMyValue = vbYes Then
                    itemContact.Email1Address = Left(itemContact.Email1Address, intAt - 1) & varCngTo
                    itemContact.Save
                End If ' strMyValue = vbYes
            End If ' intAt > 0 Then
    '
            Debug.Print itemContact.Email1Address
    '
        Next ' itemContact
    '
    '
    End Sub

  4. #4
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Just a thought but doesn't Outlook store multiple addresses for an individual contact?

    Perhaps exporting the list to .csv as Berton suggested and examining that may shed further info as to why your VBA isn't picking up all address's expected.

  5. #5
    New Lounger
    Join Date
    Apr 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Browni, I'll take a look.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •