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
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 8 Comment(s) on Get-ting Outlook items through VBA:

  1. huvanile writes:

    Thanks for the code.

    How might I return a specific Outlook contact's office as setup within a global (organization-wide) address book? I've been trying to figure out how to get the code to look to a separate address listing without success. I'm also having trouble figuring out how to call the office (officeLocation?) property of the contact.

    Thanks!

      • huvanile writes:

        Yep, that post helped. I came up with the following sub to return the office value. The only problem with it so far is that it seems Outlook has to be open in order for it to work. Do you think there's any way around this?

        'reaches out the outlook address book to figure out what city the specified user belongs to
        Private Function userDetail(theName As String, theParam As Variant)
        
            Dim olkDL As Outlook.DistListItem
            Dim olkEntry As Outlook.Recipient
            Dim olkContact As Outlook.ContactItem
            Dim intCount As Integer
            Dim strAddress As String
            Dim strName As String
            Dim oApp As Outlook.Application
            Dim oNS As Outlook.Namespace
            Dim oALs As Outlook.AddressLists
            Dim oGal As Outlook.AddressList
            Dim oEntries As Outlook.AddressEntries
            Dim oEntry As Outlook.AddressEntry
        
            On Error GoTo outlookClosed
                Set oApp = Outlook.Application
            On Error GoTo 0
            Set oNS = oApp.Session ' Get the MAPI namespace.
            Set oALs = oNS.AddressLists ' Get the Global Address List.
            Set oGal = oALs.Item("Global Address List") ' Get all the entries.
            Set oEntries = oGal.AddressEntries
            Set oEntry = oEntries(theName)
        
            Select Case theParam
                Case "City"
                    userDetail = oEntry.GetExchangeUser.City
                Case "JobTitle"
                    userDetail = oEntry.GetExchangeUser.JobTitle
            End Select
        
            Exit Function
        
        outlookClosed:
            userDetail = False
            Exit Function
        End Function
        • "The only problem with it so far is that it seems Outlook has to be open in order for it to work. Do you think there's any way around this?"

          No.

        • I appreciate the information presented. The question I have is how to extract the Subject as a string to use for trigering a rule execution?

        • When you run a script from a rule, the item is passed in as a parameter. Just use Item.Subject to get the subject line.

  2. Baidya Nath Kumar writes:

    Hi,

    I tried using the below to get the city name of my outllok contact but i get a run time error 91 saying that object varibale or with block variable not set.

    am I missing something? do i need to do something else? Actually I need a piece of excel macro code which can get me the Firstname, last Name, City and Mobile number of outlook contact from Global Address List.

    Can anyone help please??

    'reaches out the outlook address book to figure out what city the specified user belongs to

    Private Function userDetail(theName As String, theParam As Variant)
    
        Dim olkDL As Outlook.DistListItem
        Dim olkEntry As Outlook.Recipient
        Dim olkContact As Outlook.ContactItem
        Dim intCount As Integer
        Dim strAddress As String
        Dim strName As String
        Dim oApp As Outlook.Application
        Dim oNS As Outlook.Namespace
        Dim oALs As Outlook.AddressLists
        Dim oGal As Outlook.AddressList
        Dim oEntries As Outlook.AddressEntries
        Dim oEntry As Outlook.AddressEntry
    
        On Error GoTo outlookClosed
            Set oApp = Outlook.Application
        On Error GoTo 0
        Set oNS = oApp.Session ' Get the MAPI namespace.
       Set oALs = oNS.AddressLists ' Get the Global Address List.
       Set oGal = oALs.Item("Global Address List") ' Get all the entries.
       Set oEntries = oGal.AddressEntries
        Set oEntry = oEntries(theName)
    
        Select Case theParam
            Case "City"
                userDetail = oEntry.GetExchangeUser.City
            Case "JobTitle"
                userDetail = oEntry.GetExchangeUser.JobTitle
        End Select
    
        Exit Function
    
    outlookClosed:
        userDetail = False
        Exit Function
    End Function
  3. Sidharth writes:

    I used your excel version of code to automate email task for me.

    Thanks a lot for your help buddy :)

This article is closed to any future comments.
learn excel dashboards