Export (0) Print
Expand All
1 out of 3 rated this helpful - Rate this topic

Programmatically Updating Company Names in Outlook 2007

Office 2007

Summary: Learn how to use Visual Basic for Applications in Microsoft Office Outlook 2007 to change the company name for contacts without manually opening each contact. (4 printed pages)

Microsoft Corporation

April 2009

Applies to:  Microsoft Office Outlook 2007

Contents

Overview

Microsoft Office Outlook 2007 provides many fields for storing information, such as company name or job title, about each contact. Typically, you must manually open and update each contact if the value changes for a group of contacts. To regularly edit this information for multiple contacts, use a custom Outlook form.

You can use Visual Basic for Applications (VBA) in Outlook 2007 to automatically change the value of a field for contacts without manually opening each contact. This article describes how a custom post form can iterate through and set the business address as the default mailing address for all the contacts contained in a folder in Outlook 2007.

Programmatically Updating Company Names

The VBA macro uses the Outlook 2007 object model to programmatically perform many actions. The macro first asks for the values of the old and new company name. Once entered, the macro then examines each contact in the default Contacts folder, comparing the value of the Company Name field against the old company name. If the values match, the macro sets the value of the Company Name field for that contact to the new company name and saves the contact. Finally, the macro displays the number of contacts updated.

To create the Visual Basic for Applications macro

  1. On the Tools menu, point to Macro, and then click Macros.

  2. In the Macro Name box, type ChangeCompanyName (without spaces), and then click Create. This starts the Visual Basic Editor and automatically creates a subroutine for you.

  3. Type the VBA procedure shown in the following code example.

    Sub ChangeCompanyName()
    
       Dim objContactsFolder As Outlook.MAPIFolder
       Dim objContacts As Outlook.Items
       Dim strOldCo As String
       Dim strNewCo As String
       Dim objContact As Object
       Dim iCount As Integer
    
       ' Specify with which contact folder to work
       Set objContactsFolder = _
          Session.GetDefaultFolder(olFolderContacts)
       Set objContacts = objContactsFolder.Items
    
       ' Prompt for old and new company names
       strOldCo = InputBox("Enter the old company name.", _
          "Old Company Name")
       strNewCo = InputBox("Enter the new company name.", _
          "New Company Name")
       iCount = 0
    
       ' Process the changes
       For Each objContact In objContacts
          If TypeName(objContact) = "ContactItem" Then
             If objContact.CompanyName = strOldCo Then
                objContact.CompanyName = strNewCo
                objContact.Save
                iCount = iCount + 1
             End If
          End If
       Next
    
       ' Display the results
       MsgBox "Number of contacts updated:" & Str$(iCount), , _
          "ChangeCompanyName Finished"
    
       ' Clean up
       Set objContact = Nothing
       Set objContacts = Nothing
       Set objContactsFolder = Nothing
    
    End Sub
    
  4. On the File menu, click Close and Return to Microsoft Outlook.

Running the Macro

The default macro security setting is Warnings for signed macros; all unsigned macros are disabled. This setting allows a macro to run without any security prompts if the macro is digitally signed by a trusted publisher. All unsigned macros are disabled without notification. Therefore, unless your code sample is signed by a trusted publisher, you will be unable to run the code sample with this security setting. For more information about trusted publishers, see Add, remove, or view a trusted publisher.

The code sample in this article is not digitally signed. Set macro security to the Warnings for all macros option to run only this code sample. After changing to this option, restart Outlook. The Warnings for all macros option initially disables all macros. The first time that you attempt to run a macro or start the Visual Basic Editor, the Microsoft Office Outlook Security Notice dialog box appears. Click Enable Macros.

Dd673657.Important(en-us,office.12).gifImportant:

Because the Warnings for signed macros; all unsigned macros are disabled security setting provides better security for Outlook than the Warnings for all macros setting, set your macro security to Warnings for signed macros; all unsigned macros are disabled after you run this code sample. Restart Outlook for this setting to take effect.

To change the macro security setting

  1. On the Tools menu, click Trust Center.

  2. Click Macro Security.

  3. Select Warnings for all macros.

  4. Click OK.

  5. Restart Outlook.

To run the macro

  1. On the Tools menu, point to Macro, and then click Macros.

  2. Click the ChangeCompanyName macro, and then click Run.

  3. Type the old company name in the Old Company Name window.

  4. Type the new company name in the New Company Name window.

  5. Wait until the ChangeCompanyName Finished window appears that states how many contacts have been updated.

To reset macro security after running this sample

  1. On the Tools menu, click Trust Center.

  2. Click Macro Settings.

  3. Select Warnings for signed macros; all unsigned macros are disabled.

  4. Click OK.

  5. Restart Outlook.

Additional Considerations

Consider the following issues when running the sample macro:

  • It may take some time to process the items in the folder. As this happens, the mouse pointer does not change to an hourglass indicating Outlook is running the macro. The Outlook object model does not support changing the mouse pointer in this manner.

  • You can assign the macro to a toolbar button if you use this functionality often.

  • The macro only works with the default Contacts folder. To have it work with any Contacts folder you have selected, change the following code line.

    Set objContactsFolder = Session.GetDefaultFolder(olFolderContacts)
    

    to

    Set objContactsFolder = Outlook.ActiveExplorer.CurrentFolder
    

Conclusion

You can easily automate repetitive tasks in Outlook 2007 with Visual Basic for Applications (VBA) and the Outlook object model. By using the techniques shown in this sample Outlook VBA macro, you can programmatically update both built-in and custom fields in other Outlook objects.

Additional Resources

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.