Using VBA to Manipulate Existing Outlook Data

In addition to using VBA to respond to application-level events, you can also use VBA to manipulate the data you've already stored in Outlook. For instance, if you have a large number of contacts who work for ABC Bakery and the company suddenly changes its name to Sweets and Treats, you don't have to manually update every ABC Bakery contact. You can use VBA to perform the update for you.

The following code sets a folder object variable to your Contacts folder, loops through all contacts items, and updates those with a company name of ABC Bakery. You could also adapt this code to change the email addresses, area codes, or File As fields of these contacts.

TIP

Be careful when working with items in the Contacts folder. The Contacts folder can store both contact items and distribution list items. Manipulating the properties of a contact item on a distribution list item will cause errors in your code. To avoid this, test each item to ensure that it isn't a distribution list item before you attempt to access the other properties of the item.

 Sub ChangeContactCompany() Dim objNS As Outlook.NameSpace Dim objFolder As Outlook.MAPIFolder Dim objCTItem As Object Dim txtOldCompany As String Dim txtNewCompany As String txtOldCompany = "ABC Bakery" txtNewCompany = "Sweets and Treats" Set objNS = Application.GetNamespace("MAPI") Set objFolder = objNS.GetDefaultFolder(olFolderContacts) Set objCTItem = objFolder.Items.GetFirst For Each objCTItem In objFolder.Items     If objCTItem.MessageClass = "IPM.Contact" Then         'Item is a contact item.         If objCTItem.CompanyName = txtOldCompany Then             objCTItem.CompanyName = txtNewCompany             objCTItem.Save         End If     End If Next Set objCTItem = Nothing Set objFolder = Nothing Set objNS = Nothing End Sub 

There are a few tricks to note in the previous code. First, instead of declaring objCTItem as a contact item, use the general Object variable type. This prevents a type mismatch error if you have any distribution list items stored in your Contacts folder. To test whether the item is a contact item, check the item's message class. A contact item will have a message class of IPM.Contact. A distribution list will have a message class of IPM.DistList. You must save your contact item after you make changes or the changes will be lost. At the end of the code, set all object variables equal to Nothing to free any memory the objects are currently using.