• [アーティクル]

title: Getting Started with VBA in Outlook 2010 TOCTitle: Getting Started with VBA in Outlook 2010 ms:assetid: cb24a276-640b-40b1-a4c3-f3cd2a7e587d ms:mtpsurl: https://msdn.microsoft.com/en-us/library/Ee814736(v=office.14) ms:contentKeyID: 27341428 ms.date: 07/24/2014 mtps_version: v=office.14 f1_keywords:

  • Outlook 2010, programming
  • Outlook 2010, VBA code
  • VBA [Outlook 2010]
  • Visual Basic for Applications [Outlook 2010]

Getting Started with VBA in Outlook 2010

Summary: This article introduces non-programmers to some ways to use Visual Basic for Applications (VBA) programming to extend Microsoft Outlook 2010. This topic includes an overview of the VBA language, instructions on how to access VBA in Outlook 2010, a detailed solution to a real-world Outlook VBA programming problem, and tips on programming and debugging. (13 printed pages)

Applies to: Office 2010 | Outlook 2010 | VBA

In this article
VBA Programming in Outlook 2010
VBA Programming 101
Macros and the Visual Basic Editor
Making Macros Accessible
A Contact Editing Macro
Distributing Your Code
Summary
Additional Resources

Published:  November 2009

Provided by:  Tim Burnett, Kingfisher Computer Consulting

Contents

  • VBA Programming in Outlook 2010

  • VBA Programming 101

  • Macros and the Visual Basic Editor

  • A Contact Editing Macro

  • Distributing Your Code

  • Summary

  • Additional Resources

VBA Programming in Outlook 2010

Are you facing a repetitive clean-up of multiple contacts in Outlook 2010? Are you overwhelmed by an influx of e-mail attachments that you must save, one-by-one, to your hard drive? Do you want to automatically create calendar items from certain e-mail messages? Are you having difficulty figuring out how to get your contacts from Microsoft Outlook 2010 into a Microsoft Excel 2010 spreadsheet efficiently?

You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications (VBA), a simple but powerful programming language that you can use to extend Office 2010 applications with new capabilities and automate repeated tasks.

This article is for experienced Outlook users who want to learn about VBA and who want some insight into how programming can help them to tailor Outlook 2010 to meet their needs.

Before you read this article you might want to read the introductory article, Getting Started with VBA in Office 2010, which goes into more detail about the fundamentals of VBA programming.

The great power of VBA programming in Office 2010 is that nearly every operation that you can perform with a mouse, keyboard, or a dialog box can also be done by using VBA. Further, if it can be done once with VBA, it can be done just as easily one hundred times or one thousand times. (In fact, the automation of repetitive tasks is one of the most common uses of VBA in Office.)

Beyond the power of scripting VBA to accelerate every-day tasks, you can use VBA to add new functionality to Office 2010 applications or to prompt and interact with the user of your documents in ways that are specific to your business needs. For example, in Outlook 2010, you can use VBA to sort, export and modify contacts, e-mails, calendar items, or tasks in ways that are not available in the standard user interface.

The tutorial in this article automates Outlook by using VBA. It is a real-world example that investigates programming, VBA, and the development tools that are available to you as an experienced Outlook user.

Choosing Your Approach

VBA programming is a powerful solution, but it is not always the optimal approach. Sometimes it makes sense to use other ways to achieve your aims.

The critical question to ask is whether there is an easier way. Before you begin a VBA project, consider the built-in tools and standard functionalities. For example, if you have a time-consuming operation in Outlook, consider solving with by using Rules. Can you perform the task once and then use CTRL+Y (Redo) to repeat it?

Outlook 2010 is a powerful application; the solution that you need might already be there. Take some time to learn more about Outlook 2010 before you jump into programming.

Before you begin a VBA project, ensure that you have the time to work with VBA. Programming requires focus and can be unpredictable. Especially as a beginner, never turn to programming unless you have time to work carefully. Trying to write a "quick script" to solve a problem when a deadline looms can result in a stressful situation. If you are in a rush, use more conventional methods, even if they are monotonous and repetitive.

VBA Programming 101

Using Code to Make Applications Do Things

You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are quite accessible. The Office 2010 applications are created in such a way that they expose things called objects that can receive instructions, in much the same way that a phone is designed with buttons that you use to interact with the phone. When you press a button, the phone recognizes the instruction and includes the corresponding number in the sequence that you are dialing. In the programming world, you interact with the application by sending instructions to various objects in the application. These objects are expansive, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do.

For example, consider the user who creates an e-mail message in Outlook, selects a recipient, types a subject for the message, and then clicks Send. In the world of VBA programming, Outlook exposes a MailItem object. Using VBA code, you set the Subject and Body of the MailItem and tell it to Save or to Send. Read this article to learn more about these objects, how they are organized, and how they are described and manipulated.

Objects, Methods, and Properties

Developers organize programming objects in a hierarchy, and that hierarchy is called the object model of the application. The Inbox in Outlook, for example, is a Folder object that exists in the Outlook Namespace object. The Folder object contains MailItem objects, and so on. Object models roughly mirror what you see in the user interface. They are a conceptual map of the application and its capabilities. Programmatically, a Folder object can contain any type of item object but, typically, each of the few folders in the Outlook user interface contains predominantly a certain type of item object. For example, the Outlook user interface provides a Contacts folder that contains mainly ContactItem objects and a Calendar folder that contains mainly AppointmentItem and MeetingItem objects.

The definition of an object is called a class, so you might see these two terms used interchangeably. Technically a class is the description or template that is used to create, or instantiate, an object.

Once an object exists, you can manipulate it by setting its properties and calling its methods. If you think of the object as a noun, then the properties are the adjectives that describe the noun and the methods are the verbs that animate the noun. Changing a property changes some quality of appearance or behavior of the object. Calling one of the object's methods makes the object perform some action.

After you know the basic layout of the object model in Outlook and some key properties of Application, Explorer, and Folder objects that give you access to the current state, you can start to extend and manipulate Outlook with VBA.

Macros and the Visual Basic Editor

Now that you know about how Outlook 2010 applications expose their object models, you are probably eager to try calling object methods, setting object properties, and responding to object events. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although installed by default, many users do not know that it is even available until it is enabled on the ribbon.

Opening the Developer Tab

All Office 2010 applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office 2010 does not display the Developer tab by default, you must enable it by using the following procedure:

To enable the Developer tab

  1. On the File tab, choose Options to open the Outlook Options dialog box.

  2. Click Customize Ribbon on the left side of the dialog box.

  3. Under Choose commands from on the left side of the dialog box, select Popular Commands.

  4. Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.

  5. Click OK.

Note

In Office 2007, you displayed the Developer tab by clicking the Office button, clicking Options, and then selecting the Show Developer tab in Ribbon checkbox in the Popular category of the Options dialog box

After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons.

Figure 1. Developer tab in Outlook 2010

Developer tab in Outlook 2010

Security Issues

By default, VBA macros are disabled in Outlook to protect you against viruses and other malicious code. To enable macros, use the following procedure:

To enable macros

  1. On the File tab, choose Outlook Options to open the Outlook Options dialog box, and then click Trust Center.

  2. Click Trust Center Settings, and then the Macro Settings option on the left.

  3. Select Notifications for all macros and then click OK. The option allows macros to run in Outlook, but before the macro runs, Outlook prompts you to verify that you want to run the macro.

  4. Restart Outlook for the configuration change to take effect.

The Visual Basic Editor

After you display the Developer tab, you can open the Visual Basic Editor, which is the built-in tool that you use to write and edit VBA code for Outlook. Use the following procedure to open the Visual Basic Editor:

To open the Visual Basic Editor

  1. Click the Macros button on the Developer tab.

  2. In the Macro dialog box that appears, type Test under Macro Name.

  3. Click the Create button to open the Visual Basic Editor with the outlines of a new macro already typed in.

The Visual Basic Editor contains the following code.

    Sub Test()
    End Sub

Sub stands for "Subroutine" which you can define for now as "macro." Running the Test macro runs any code that is between Sub Test() and End Sub.

Now, edit the macro so that it looks similar to the following code.

    Sub Test()
       MsgBox("Hello world")
    End Sub

Figure 2. Test macro in the Visual Basic Editor

Test macro in the Visual Basic Editor

Be aware of the left panel in the Visual Basic Editor. The Test macro is in Module1 of Project1. A Project module contains multiple modules, and each Module contains a collection of VBA code that you can import or export as a .bas file.

Once an object exists, you can manipulate it by setting its properties and calling its methods. The subroutine that you edited contains a method, which is code that performs an action. The Test() method in the subroutine is now a member of the object Project1.

Note

Other Office applications might have multiple Projects listed in the Visual Basic Editor, but in Outlook VBA there is only one project listed, and it is always called Project1.

Go back to the Developer tab in Outlook and click the Macros button again. Select the Project1.Test macro in the list that appears and then click Run to display a small message box that contains the text, "Hello, world!"

Congratulations! You just created and implemented custom VBA code in Outlook! Click OK in the message box to close it and finish running the macro.

If the message box does not appear, check your macro security settings and restart Outlook.

Making Macros Accessible

If you use a macro frequently, you might find it more convenient to access it with a keyboard shortcut or a Quick Access Toolbar button.

To create a button for the Test macro on the Quick Access Toolbar, use the following procedure:

To create a button for a macro on the Quick Access Toolbar

  1. Click the File tab.

  2. Click Options to open the Outlook Options dialog box, and then click Quick Access Toolbar.

  3. In the list under Choose commands from:, choose Macros. Find the text that is similar to Project1.Module1.Test in the list that appears and select that text.

  4. Click the Add >> button to add the macro to the list on the right side, and then click the Modify… button to select a button image to associate with the macro.

  5. Click OK. You should see your new button on the Quick Access Toolbar above the File tab.

Now you can quickly run your macro at any time without using the Developer tab—give it a try.

Figure 3. Assigning a macro to the Quick Access Toolbar

Assigning a macro to the Quick Access Toolbar

A Contact Editing Macro

Suppose that you have multiple contacts in a given company called Example Systems, and that each contact has an e-mail address with the @example.com domain. Now suppose that Example Networks buys Example Systems and changes all of the employees' e-mail addresses to @example.net. If you have one or two contacts at Example Systems, it would be simple to change those manually. However, if you have 20, 50, or 100 contacts, automating this repetitive task is a common use of VBA in Office.

This article discusses how to use VBA in Outlook to modify those contacts and explains some VBA concepts.

Accessing the Contacts Folder

Follow the steps that you used to create the Test macro to create a new macro called CompanyChange and open it in the Visual Basic Editor.

The first task is to gain access to the Contacts folder object in Outlook, and then to store the reference to that object in a variable.

In programming, a variable is a temporary, named object that can hold a value or a reference to an object. The sample code that follows uses a variable called ContactsFolder. The code instructs VBA that it is going to put a Folder object in it and run the GetDefaultFolder method of the Session object to find the Contacts folder and store a reference to it in that variable.

The Visual Basic Editor includes helpful automatic code entry called Intellisense. Watch carefully when you type Session.GetDefaultFolder and then press the opening parenthesis for its parameter. As you do, the Visual Basic Editor shows you a list of options. Use the down arrow or the mouse to select olFolderContacts, one of the types of folders available, and press the Tab key to select it.

Figure 4. Using Intellisense to pick a folder type

Using Intellisense to pick a folder type

Although it is a good practice to use copy and paste when you work with code to avoid typing errors, type the code into the Visual Basic Editor for now so that you can see for yourself how the editor can help you.

    Sub CompanyChange()
        Dim ContactsFolder as Folder
        Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
    End Sub

Add one more line so that you can get some feedback and test your macro when it is in progress. (Also, take a minute to create a Quick Access Toolbar button for CompanyChange, as we did in the Making Macros Accessible section.)

    Sub CompanyChange()
        Dim ContactsFolder as Folder
        Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
        MsgBox ("Contacts Found: " & ContactsFolder.Items.Count)
    End Sub

Items is a property of the Folder object and Count is a property of the Items property.

Run the macro; the number of Contacts that you have in Outlook should appear in a message box.

If you have not done so already, open your Contacts folder and create some sample contacts who work for the fictional Example Systems company. Give each sample contact the company name Example Systems and create a different e-mail address for each that is similar to someone@example.com.

Collections of Objects

Collections are one of the most powerful features of modern programming languages such as VBA. A collection is a set of objects that can be stored in a property and easily "walked through" to access each one. In this case, the code must loop through the collection of contacts to find the people from Example Systems.

As mentioned earlier, the ContactsFolder variable provides access to the Items collection of the Folder. The Items collection of a contacts Folder returns objects of type ContactItem. Create a variable to hold a reference to each ContactItem.

    Dim Contact As ContactItem

Be aware that it is also possible to use the general purpose Object type if you do not know what type of object is returned from a collection. However, if you know the type of object, specifying it more exactly has many advantages.

    Dim Contact As Object

After you have the ContactItem, you must check the name of the company for each person. Since the ContactItem object stores information about a contact, there must be a property for the company name, but since you don't know the name of that property, you must use one of the following two ways to find that name.

Determining Property Names

To make full use of an object in VBA, you must know its properties and methods. Although it is possible to perform many tasks by working from example code that you find on the Web, you increase your options, reduce debugging, and save time by systematically examining the objects that you use.

For example, you can find information about ContactItem in the Outlook Developer Reference in the Help by selecting ContactItem in your code and pressing F1. You can also search on ContactItem in Help. Ensure to investigate the links in the various topics to explore related objects.

The Developer Reference is a powerful tool for VBA Programming, and one way to find the property that you need for your code. After you read the general information about the ContactItem object, click the "ContactItem Object Members" link to see the list of methods and properties (properties and methods collectively are known as Members).

If you read down the list, you will see that there is a CompanyName property. That sounds like something that might work.

Aside from using the Outlook Developer Reference to find a property or a method, you can use Intellisense in the Visual Basic Editor. After you define your variable Contact as a ContactItem,you can type Contact and a period to display the same list of members that are listed in the Help. To select one of the Intellisense choices, use the up and down arrows or the mouse to highlight it, and then use the Tab key to select it.

Intellisense does not provide descriptions of the various members, so using it this way is most helpful when the context makes your choice obvious, or you already know the name of the member but cannot remember the exact spelling. Misspelled member names result in errors that Visual Basic Editor does not flag until you run your macro.

Looping Through a Collection

You now have a collection, you know which type of object is contained within it, and you have a property to access on the objects found. You are ready to loop through the collection and manipulate the objects that it contains by using a For Each loop.

In pseudo-code, you are ready to do the following.

    For Each [object variable] in [a collection that contains that type of object]
    [do something with] [object variable].Property
    Next

In actual code, that translates into the following code, which concerns the line that has the MsgBox statement.

    For Each Contact In ContactsFolder.Items
       Debug.Print Contact.CompanyName
    Next

Before you run the macro again, open the Immediate window in the Visual Basic Editor. To do so, click View on the menu bar and then click Immediate Window. Earlier, you used MsgBox earlier to get feedback from your script. Although MsgBox is useful, it always waits for you to click OK before it shows the next message. In contrast, Debug.Print sends output a line a time to the Immediate window without waiting. You should see a list of company names in the Immediate window after you run this script. Be aware that this is useful to you as a developer, but users who share this macro with you will not see it.

Making Decisions with the If Statement

In your completed solution, you want the macro to change only those contacts who work for Example Systems and leave your other contacts unchanged. Conditional logic of that sort is where programming really shines. Here, you can use the If statement to tell VBA to run code only if the condition provided is true; otherwise, it should skip to the code after the End If statement.

    If [some condition is true] Then
    ' do things
    End If
    ' continue on whether or not the condition was true

In the problem that you are working on, the determining condition is whether the CompanyName property for the current ContactItem is set to the string "Example Systems".

In the code that follows, be aware of the double quotes. String values (that is, text values), as opposed to numeric values, are always contained by double quotes. VBA ignores single quotes, which are for comments to yourself and other developers.

Add the condition in your code so that your full script looks similar to the following code.

    Sub CompanyChange()
        Dim ContactsFolder As Folder
        Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
        MsgBox ("Contacts Found:" & ContactsFolder.Items.Count)
        
        Dim Contact As ContactItem
    
        For Each Contact In ContactsFolder.Items
            If Contact.CompanyName = "Example Systems" Then
                Debug.Print "Found: " & Contact.FullName
            End If
        Next
    End Sub

After you run the code, you should see the names of your Example Systems contacts in the Immediate window.

Figure 6. Using the Immediate Window for Output

Using the Immediate Window for Output

Be aware of the way that both the Debug.Print and the earlier MsgBox combine fixed strings for clarity such as "Found: " followed by the value returned from a property. For this to work, you must combine the two text strings by using the string concatenation operator (&).

Important

Important: Use caution as you continue this tutorial. You are working on your actual list of Outlook contacts along with a few fictional contacts from Example Systems. Modifying the company name in each of your contact to "Example Networks" would not be a good thing.

Changing the Company Name

You are almost done. You have loaded your contacts, you can loop through them, and you can distinguish your contacts from Example Systems. To make the change to Example Networks, you change the CompanyName property and call the Save method to commit the change.

        If Contact.CompanyName = "Example Systems" Then
            Contact.CompanyName = "Example Networks"
            Contact.Save
            Debug.Print "Changed: " & Contact.FullName
        End If

Run the macro and then look at your contacts for Example Systems; the company name in those contacts should now be Example Networks instead. Be aware that for further testing of your script you must change the company name back to Example Systems either by hand or by creating another macro that changes it for you.

Keep the Debug.Print statement in even though it is not essential for the script to work. The more feedback that you and your users get from a macro, the better.

Changing the E-mail Address

Changing the CompanyName property was a simple change of an entire string. To change the domain of the e-mail address from @example.com to @example.net requires a string operation. Each string has a built in numbering system where every character has an index number starting at 1. You can use a variety of VBA functions to manipulate strings in part or whole. Learning these string operations increases your options as a VBA programmer a great deal because there are many programming situations that require you to check for a substring or to change part of a string. (Searching the Web for "VBA string functions" provides many useful lists and examples.)

You can use the Replace function to perform a simple replacement of one substring for another, but you might often face situations that require more complex coding to manipulate strings. Add the following line immediately before the code invokes Contact.Save.

    Contact.Email1Address = Replace(Contact.Email1Address, "example.com", "example.net")

Read the code carefully from left to right. It says to set the Contact's Email1Address property (the first e-mail address) to a new value. The new value is provided by asking the Replace function to look in the current Contact.Email1Address property for the string "example.com" and replace it with "example.net".

Ensure that the Company Name in your fictional contacts is set to Example Systems, and then run the script. The e-mail addresses should all change to @example.net and the Company Names should all change to Example Networks.

Generalizing Your Code

You now have a powerful macro, but it is limited because the only way to specify the new Company Name is to edit the code. To make your code more generally useful, you can use an InputBox function to prompt the user for input.

The following code is the final script. It is a useful and complete Outlook solution.

    Sub CompanyChange()
        Dim ContactsFolder As Folder
        Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
        Dim OldCompanyName As String
        Dim NewCompanyName As String
        Dim OldEmailDomain As String
        Dim NewEmailDomain As String
        Dim ContactsChangedCount As Integer
        
        ' Ask user for inputs
        MsgBox ("This script will change all of your contacts from one company to another.")
        OldCompanyName = InputBox("Under what name are the contacts listed in Outlook now?")
        NewCompanyName = InputBox("What is the new company name to set them to?")
        OldEmailDomain = InputBox("What is the e-mail domain name currently listed after the @ sign? e.g. mycompany.com")
        NewEmailDomain = InputBox("What should the e-mail domain be set to? Leave blank and click OK if no change")
        ContactsChangedCount = 0
        
        Dim Contact As ContactItem
     
        ' loop through Contacts and set those who need it
        For Each Contact In ContactsFolder.Items
            If Contact.CompanyName = OldCompanyName Then
                Contact.CompanyName = NewCompanyName
                If NewEmailDomain <> "" Then
                    Contact.Email1Address = Replace(Contact.Email1Address, OldEmailDomain, NewEmailDomain)
                End If
                Contact.Save
                ContactsChangedCount = ContactsChangedCount + 1
                Debug.Print "Changed: " & Contact.FullName
            End If
        Next
        ' confirm and clean up
        MsgBox (ContactsChangedCount & " contacts were changed from '" & OldCompanyName & "' to '" & NewCompanyName)
        Set Contact = Nothing
        Set ContactsFolder = Nothing
    End Sub

Distributing Your Code

Moving code from the computer where it was developed to other computers is called deployment and it can be a significant part of a programming project. This section examines how you can move your new macro to other computers.

First a little background. Outlook Visual Basic for Applications code was designed to be a personal macro development environment; it was not designed to be deployed or distributed. Thus, there is no way to save a macro inside a document the way that you can with other Office applications like Excel or Word.

To move Outlook VBA macros from one computer to another, you can export code modules from the first computer and import them to the second computer. You can also copy and paste the source code of the project to Project1 on the second computer by using the Visual Basic Editor.

If you develop a solution that you intend to distribute to more than a few people, you should convert your Visual Basic for Applications code into an Outlook COM Add-in. However, developing a COM Add-in typically requires considerably more programming knowledge than creating a short macro, so if your Visual Basic for Applications project is relatively simple, and there are not too many people who need to use it, send them the code with instructions that explain how to open the Visual Basic Editor and import your code module manually.

Importing and Exporting Modules

Try to export and import your new macro in Module1. Select Module1 in the Project pane, and then click the File menu in the Visual Basic Editor and select Export File…. Visual Basic Editor should prompt you to save the file as a Basic File (.bas). Save the file as Contacts.bas. (If the default file extension is some other type, verify that you have selected Module1 and then reopen the Export File dialog box.)

Open Contacts.bas in Notepad to see your code with a header line that the Visual Basic Editor will use to name the module when you import the code.

    Attribute VB_Name = "Module1"

Module1 is used for local scripts and experiments, so change the name to something that describes the macros in the module. For example, you could use Contacts as the module name to parallel the name of the .bas file. Later on, if you wrote other macros to automate different aspects of your contacts, you could add the code here and have a logical group of macros in a single module.

    Attribute VB_Name = "Contacts"

Now return to the Visual Basic Editor. Click File, click Import File, and then select Contacts.bas to import it.

Double-click the new Contacts module that appears in the Modules list of the Project pane to see your code.

In Outlook, click the Macros button on the Developer tab. You should see an entry for …Project1.Contacts.CompanyChange.

That completes the simplest way to distribute macro code to other members of your organization. Of course, to import Contacts.bas and to add the Contacts module in Visual Basic Editor, they must still enable the Developer tab in Outlook 2010. After they import Contacts.bas, they can run your macro from the Developer tab or open Outlook Options and assign the macro to a Quick Access Toolbar button or to the ribbon itself. For more information about more complex deployment options, see the Additional References section.

Summary

This article examined the power of Visual Basic for Applications (VBA) in Microsoft Outlook. VBA can automate repetitive tasks and extend Outlook 2010. Using a real-world example of VBA programming this article explained how to open the Developer toolbar, how to create macros, and then run, edit, and debug them. Also, this topic discussed VBA variables, the object model, and function such as If and For Each.

Additional Resources

For more information, see the following resources: