Before you can work with an object in your VBA code, you must have a reference to it. A reference is simply a variable name. Instead of holding data, however, an object reference lets you work with an object in your code.

There are two parts to obtaining an object reference. First, you must create a variable that is of the proper type to hold a reference to the specific kind of object you are dealing with. The preferred way to do this is as follows:

Din RefName As type

This looks like a regular Dim statement for declaring a variable—and in fact, it is the same except that type refers to the specific type of object and not to a data type. For example, in the Outlook Object Model the object type MailItem represents an email message. To declare a variable that can reference a message, you would write the following:

Dim MyMessage As Outlook.MailItem

Note that the Outlook prefix to the object name is used to ensure that you are referencing the Outlook Object Model, because some object names are duplicated between programs.

At this point you have a name that can refer to the specified type of object—Outlook.MailItem in this case—but it does not yet refer to an actual object. Your next step will take one of two paths depending on your needs.

First, you can create a new object of the specified type and set the variable name to refer to it. You do this with the Set and New keywords:

Set RefName = New type

Dim RefName As New Type

Set objRef = Nothing

If objRef Is Nothing Then
  ' Actions to take if the reference is not valid.
Else
  ' Actions to take if the reference points to a valid object.
End If

However, this technique cannot be used with many kinds of objects, including MailItem. Rather, you must get a reference to an instance of the object using the Outlook Object Model. For example, you cannot create a new MailItem object with the following code:

Set MyMessage = New Outlook.MailItem

You see exactly how to create references to Outlook objects throughout the chapter.

Collections are an important part of Outlook macro programming. Whenever the Object Model requires more than one of something, it uses a collection to keep track of them. For example, in Outlook each mail folder, such as the Inbox, is represented by an object. A folder can contain any number of email messages, and these messages are represented as a collection. Collections are designed with built-in tools that make is easy to find an individual object in the collection or to do something with all of the objects in the collection. Every collection has the Count property, which tells you the number of elements in the collection.

The most common use for collections is when you want to process every item in the collection. For example, suppose you want to write a macro that looks at every email message in the Inbox and moves those messages from a certain sender to another folder. By looping through the collection of email messages in this folder, the task is easily accomplished no matter how many or few messages there are in the folder. You use the For Each...Next loop, introduced in Chapter 23, for this purpose.

First, you must declare an object variable of the same type as the objects in the collection. Continuing with the email example:

Dim msg As Outlook.MailItem

Then create a reference to the object that contains the collection—in this case, an Outlook folder—specifically, the Inbox:

Dim f As Outlook.Folder
Dim MyOutlookNamespace As Outlook.NameSpace
Set MyOutlookNameSpace = GetNameSpace("MAPI")
Set f = MyOutlookNameSpace.GetDefaultFolder(olFolderInbox)

Finally, loop through the collection as follows:

For Each msg in f.Items
  ' With each repetition of the loop, msg refers to the   ' next
message in the collection.
Next

The previous code examples are missing a couple of details and will not actually run, but they serve to illustrate the principle of collections. You'll note that this is one of those exceptions where a collection—Items in this case—is not named as the plural of the kind of object it contains, MailItems.

If you examine the previous code snippet, you'll see this line of code:

Set f = GetDefaultFolder(olFolderInbox)

What is olFolderInbox? It's not a variable that you have declared. Rather it's one of the named constants in the Object Model. Some methods (a procedure that is part of an object) require an argument to tell them what to do. Rather than use hard-to-remember numbers, the Object Model provides these descriptive constants that are a lot easier to remember. What's more, the VBA Editor's IntelliSense feature will list the available constants as you are typing in your code, as shown in Figure 24.1.

Figure 24.1. The editor's IntelliSense feature lets you select from a list of named constants.

At the very top of the Outlook Object Model is the Application object. As you might have already guessed, this object represents the Outlook application itself. All other objects in the hierarchy are subsidiary to the Application object. Often, when you are trying to locate data in Outlook, you start with the Application object and work down to levels of greater detail.

The Application object is available as an implicit reference for VBA code in Outlook. This means that you do not have to use the term in your code—it is understood automatically. If you were writing VBA code to run in another Office application, such as Word, you would have to explicitly reference the Outlook Application object.

For example, the GetNamespace() method is part of the Outlook Application object. Full syntax for this would be

Application.GetNamespace()

but because of the implicit availability of the Application reference, you can write it like this:

GetNamespace()

Another high-level object in the Outlook Object Model is the Namespace object. It exists because Outlook was designed to be usable with different kinds of data, each of which would be identified by its own namespace. So far, only one kind of data is supported in Outlook, MAPI data (MAPI stands for Messaging Application Programming Interface). Therefore there is only one namespace. Though it seems like unnecessary effort, you must use this namespace when accessing email and other items in Outlook. In many of the code examples in this and later chapters, you'll see a few lines of code like this:

Dim MyOutlookNamespace As Outlook.Namespace
Set MyOutlookNamespace = GetNamespace("MAPI")

Then, the Namespace object is used when accessing mail and other folders:

Set f = MyOutlookNS.GetDefaultFolder(olFolderInbox)

Outlook items are all stored in folders, and before you can work with items you need to get a reference to the containing folder. Outlook has two types of folders: the default folders and user-created folders.

Dim folder As Outlook.Folder
Set folder = MyOutlookNamespace.GetDefaultFolder(foldertype)

Referencing User-Created Folders

Folders that the user has created are not default Outlook folders and references to them are obtained from the Folders collection. Because folders can themselves be organized in a hierarchy, things are arranged as follows:

• Each top-level folder is a member of the NameSpace object's Folders collection.

• Each of these top-level folders has its own Folders collection, which contains any subfolders that the folder contains.

• Each folder in turn has its own Folders collection.

User-created folders are identified by name, which is obtained from the Folder object's name property. To get a reference to a folder, assuming that you do not know exactly where it is located, you must look through all folders until you either find it or run out of places to look. This technique can locate the default folders as well, based on their name, but the technique using the GetDefaultFolder() method, described in the previous section, is easier to use.

In the folder hierarchy, what the user considers a top-level folder is in fact a second-level folder. Thus, for example, in the folder structure shown in Figure 24.2, Clients is considered a second-level folder and Acme, Consolidated, and National are third-level folders. The top-level folders are Personal Folders, Archive Folders, Internet Calendar, and similar items.

To get a reference to a specific folder, you can use the function in Listing 24.1. This function is passed the name of the desired folder and returns a reference to the desired folder, if it exists, or else the value Nothing. Be aware that folder names in Outlook are case-sensitive. If the folder does not exist, the value Nothing is returned. You can check for the Nothing value using Is Nothing as explained earlier in this chapter.

This function looks at second- and third-level folders but does not go any deeper. Thus, looking at the folder structure in Figure 24.2 the function would find Clients, and it would find Acme, Consolidated, and National, but it would not find any folders nested deeper than that. It could easily be modified to do so, however, and that would be a good programming exercise for you.

Figure 24.2. Understanding Outlook folder levels.

Example 24.1. A Function to Obtain a Reference to a User-Created Folder

Public Function FindFolder(FolderName As String) _
    As Outlook.Folder

Dim folder1 As Outlook.Folder
Dim folder2 As Outlook.Folder
Dim folder3 As Outlook.Folder
Dim FoundFolder As Outlook.Folder
Dim MyOutlookNamespace As Outlook.NameSpace

Set MyOutlookNamespace = GetNamespace("MAPI")
Set FindFolder = Nothing

For Each folder1 In MyOutlookNamespace.Folders
    ' We know that top-level folders will not match
    ' so no comparison is needed here.
    For Each folder2 In folder1.Folders
        If folder2.Name = FolderName Then
            Set FindFolder = folder2
            Exit Function
        End If
        For Each folder3 In folder2.Folders
            If folder3.Name = FolderName Then
                Set FindFolder = folder3
                Exit Function
            End If
        Next
    Next
Next

End Function

Every folder can contain items, and the purpose for getting a reference to an Outlook folder is almost always to do something with the items that it contains. With user-defined folders, you specify the type of Outlook item the folder will contain when you create the folder. When you are working with a folder in VBA, you may want to determine its default type of item. For example, perhaps you are writing a macro that will look for certain information in all email messages in all folders. You can iterate through all Outlook folders using the techniques described earlier in this chapter, and then process the folder items only if its default item type is email.

To determine a folder's default item type, look at its DefaultItemType property. The values that can be returned are represented by named constants as described in Table 24.2. This property is read-only, which means that you cannot change it.

Many of the things you'll do with email messages that you have received involve message properties that provide information about the subject, recipients, sender, and other aspects of the message. The MailItem properties used most often when working with received messages are described in Table 24.3.

Next look at some examples of writing macros to perform useful, everyday tasks in Outlook.

For Each m In fInbox.Items
    If InStr(m.Subject, Subject) > 0 Then
        ' Move the message here.
    End If
Next

m.Move fDestination

Dim at As Outlook.Attachment
For Each at in MyMailItem.Attachments
  ' Deal with each attachment here.
Next

Dim at As Outlook.Attachment

For Each at in MyMailItem.Attachments
    If at.Type = olByValue Then
        at.SaveAsFile("c:data" & at.FileName)
    End If
Next

In addition to processing received messages, you can write macros that create and send email messages. Outlook's Object Model makes the task relatively simple. The MailItem object has some properties mostly relevant when you are creating a new message to send. These properties are described in Table 24.4.

The general procedure for creating and sending an email message is as follows:

The following sections look at some details.

Dim msg As Outlook.MailItem
Set msg = CreateItem(olMailItem)

Dim recip As Outlook.Recipient
Set recip = msg.Recipients.Add("[email protected]")

recip.Type = olCC

Dim recip As Outlook.Recipient
Set recip = msg.Recipients.Add("Jane Austin")

Dim recip As Outlook.Recipient
Set recip = msg.Recipients.Add("Jane Austin")
If recip.Resolve Then
  ' Resolved OK - safe to send.
Else
  ' Resolution failed - take steps to correct.
End If

MailItem.Attachments.Add(Source, Type, Position, DisplayName)

MyMailMessage.Save
With MyMailMessage.Attachments
    .Add "c:dataTechnologyWhitePaper.doc", olByValue, , _
        "White Paper"
    .Add "c:dataMedicalExpenses.xls"
End With

To create a new appointment, you use the CreateItem() method. The syntax is

Dim NewAppt As Outlook.AppointmentItem
Set NewAppt = CreateItem(olAppointmentItem)

The new appointment is, of course, blank—you must fill in the various details such as subject, start/stop times, and whether a reminder will be displayed. A new appointment created in code is not visible on the screen. There are two approaches to filling in the details of a new appointment.

You can do it all in code without ever making the appointment visible to the user. In this case you must save the appointment by calling the Save() method:

Dim NewAppt As Outlook.AppointmentItem
Set NewAppt = CreateItem(olAppointmentItem)
NewAppt.Subject = "Sales review meeting"
NewAppt.Location = "Conference Room B"
NewAppt.Start = #9/26/2006 1:00:00 PM#
NewAppt.End = #9/26/2006 3:00:00 PM#
NewAppt.Body = "Make sure to bring the worksheet."
NewAppt.Save

The other approach is to display the appointment to the user by calling its Display() method. The user fills in the appointment details and saves it by clicking the Save & Close button, as usual.

You access existing appointments using the Calendar folder's Items collection. You can retrieve an appointment based on its subject. Here's how to retrieve an appointment by its subject, in this case "New Employee Training" (assume that CalendarFolder is a reference to Outlook's Calendar folder):

Dim MyAppt As Outlook.AppointmentItem
Set MyAppt = CalendarFolder.Items("New Employee Training")

The problem with this approach is that if there are multiple appointments with the same subject, it retrieves only the first one. It is preferable to loop through all the appointments in the folder with a For Each...Next statement, looking for one or more appointments of interest. Here's an example that sets the variable MyAppt to point at the first appointment that has "New Employee Training" as its subject. If there is no such appointment, a message is displayed alerting the user.

Dim MyAppt As Outlook.AppointmentItem
Dim Appt As Outlook.AppointmentItem
Dim Found As Boolean

Found = False
For Each Appt In CalendarFolder.Items
    If Appt.Subject = "New Employee Training" Then
        Found = True
        Set MyAppt = Appt

End If
Next

If Found Then
    ' Process the matching appointment here.
Else
    MsgBox "Appointment 'New Employee Training' not found."
End If

Chapter 25 presents a complete calendar application that creates and prints a document listing all the appointments for the upcoming week. This demonstration also shows you how an Outlook VBA program can control other Office applications.