Get-ting Outlook items through VBA

A recent email exchange had me thinking about a way to access various Outlook items through VBA. I came up with some simple procedures for returning some of the more popular items. You can extend the samples below for your own needs in Excel or Outlook (or elsewhere).

According to the entry for Subject Property on MSDN, the Subject property is the default property for Outlook items.

That means when I use the Item property to set an object reference to a particular Outlook item, I'm really using the Subject property to identify that item.

Tasks

Function getTask(taskName As String) As Outlook.TaskItem

Dim olnameSpace As Outlook.NameSpace
Dim taskFolder As Outlook.MAPIFolder
Dim tasks As Outlook.Items

Set olnameSpace = Application.GetNamespace("MAPI")
Set taskFolder = olnameSpace.GetDefaultFolder(olFolderTasks)
Set tasks = taskFolder.Items

Set getTask = tasks.item(taskName)

End Function

This procedure uses the Subject property to identify a given task. It returns a reference to the TaskItem object itself, so you can extract other information from it in your code.

Usage:

Sub MyMacro()
Dim tsk As Outlook.TaskItem
Set tsk = getTask("Afternoon Meeting")
MsgBox tsk.StartDate
End Sub

This short macro uses the getTask function to look for a task in the default Tasks folder with the subject "Afternoon Meeting", then prints the task's start date in a messagebox.

Contacts

Function getContact(contactName As String) As Outlook.ContactItem

Dim olnameSpace As Outlook.NameSpace
Dim contactFolder As Outlook.MAPIFolder
Dim contacts As Outlook.Items

Set olnameSpace = Application.GetNamespace("MAPI")
Set contactFolder = olnameSpace.GetDefaultFolder(olFolderContacts)
Set contacts = contactFolder.Items

Set getContact = contacts.item(contactName)

End Function

Here's a procedure that returns a reference to a contact item. (Contrary what I wrote above, a contact item's default property is Name.) If you know your contact's name, and want to access stored information about that contact (for example, phone number or mailing address), use the procedure above to return a reference to that contact.

Outlook-anything

Function getItem(itemName As String, folderType As OlDefaultFolders) As Variant

Dim olnameSpace As Outlook.NameSpace
Dim genericFolder As Outlook.MAPIFolder
Dim genericItems As Outlook.Items

Set olnameSpace = Application.GetNamespace("MAPI")
Set genericFolder = olnameSpace.GetDefaultFolder(folderType)
Set genericItems = genericFolder.Items

Set getItem = genericItems.item(itemName)

End Function

Above is a generic routine that returns a reference to a lot of different Outlook items. Notice the enum used as one of the arguments, so when you call the function, you can choose one of Outlook's default folders directly (if you need another folder, you'll need to change the code). Since it can return many types of items, the function returns a Variant, so you'll need to declare your holding variable appropriately.

So if you want to look at a TaskItem sometimes, and a ContactItem or MailItem other times, the generic routine can do them all. For Tasks and Emails, you specify the Subject; for Contacts, the Name.

Excel versions

You'll probably want to read information about a Task or Contact in Excel. So here are Excel versions of the above functions. You'll also need to include a function that creates a reference to the Outlook.Application object.

Dim bWeStartedOutlook As Boolean

Function getTask(taskName As String) As Object

Dim olApp As Object ' Outlook.Application
Dim olnameSpace As Object ' Outlook.Namespace
Dim taskFolder As Object ' Outlook.MAPIFolder
Dim tasks As Object ' Outlook.Items

Set olApp = GetOutlookApp
Set olnameSpace = olApp.GetNamespace("MAPI")
Set taskFolder = olnameSpace.GetDefaultFolder(13)  ' olFolderTasks
Set tasks = taskFolder.Items

Set getTask = tasks.Item(taskName)

End Function

Function getContact(contactName As String) As Object

Dim olApp As Object ' Outlook.Application
Dim olnameSpace As Object ' Outlook.Namespace
Dim contactFolder As Object ' Outlook.MAPIFolder
Dim contacts As Object ' Outlook.Items

Set olApp = GetOutlookApp
Set olnameSpace = olApp.GetNamespace("MAPI")
Set contactFolder = olnameSpace.GetDefaultFolder(10)  ' olFolderContacts
Set contacts = contactFolder.Items

Set getContact = contacts.Item(contactName)

End Function

Function GetOutlookApp() As Object

On Error Resume Next
  Set GetOutlookApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Set GetOutlookApp = CreateObject("Outlook.Application")
    bWeStartedOutlook = True
  End If
On Error GoTo 0

End Function

Don't forget to quit any instance of Outlook, if it is created by your procedure. You'll need something like this in your code:

If bWeStartedOutlook Then
  olApp.Quit
End If

This code snippet checks the boolean variable we used above to see if Outlook was started by the procedure. If so, we'll need to call the Quit method to make sure Outlook is stopped when the code is finished.

Usage:

Sub MyMacro()

Dim task As Object
Set task = getTask("My task subject")

MsgBox task.StartDate

If bWeStartedOutlook Then
  olApp.Quit
End If

End Sub

Related Articles:

• April 30, 2009 -- Get Default Folder Items Collection