VBA Outlook

Outlook is a Microsoft application used to work with email. VBA is embdedded within the Outlook application and can be used to programmatically work with Outlook. The Outlook Object Library contains classes that make working with Outlook in VBA possible. The classes in the Outlook Object Library are referred to as the Outlook Object Model. When using VBA within Outlook, a reference is automatically included to the Outlook Object Library.

Outlook Object Model

The Outlook Object Model contains classes which make it possible to work with Outlook in VBA. The Outlook Object Model contains many classes but the central overview includes the top-level Application object which represents the Outlook application; the NameSpace object which is used to work with Accounts, Folders, and Stores; the Explorer and Inspector objects which represent the windows used to browse, search, and view Outlook items; and the various Outlook items such as MailItem, AppointmentItem, TaskItem, and ContactItem which represent emails, meetings, tasks, and contacts in Outlook.

Application
1. Namespace
  1. Accounts
    1. Account
  2. Folders
    1. Folder
  3. Stores
    1. Store
2. Explorers
  1. Explorer
3. Inspectors
  1. Inspector

Context

The place where VBA code is written determines the implied context when referring to classes. For example, when using VBA in Outlook and referring to the Application object, it is implied that Application and Outlook.Application are the same object. However, when using VBA in another application to work with Outlook, it is necessary to qualify references to avoid naming collisions with classes in the other application. Using qualified references requires that a reference is set to the library where the classes are defined. If a reference cannot be set, late-binding can be used instead.

Public Sub Example()
    
    '''In Outlook

    Debug.Print Application Is Outlook.Application 'Prints: True

End Sub

Public Sub Example()
    
    '''In Excel

    'Reference to Outlook Object Library is required

    Debug.Print Application Is Outlook.Application 'Prints: False
    
    Debug.Print Application Is Excel.Application 'Prints: True

End Sub

To retrieve the Outlook Application object from another application, attempt to retrieve the running application and if that fails start a new instance of the application.

Public Function GetOutlookApp() As Object

    On Error Resume Next
    Set GetOutlookApp = GetObject(, "Outlook.Application")
    On Error GoTo 0

    If GetOutlookApp Is Nothing Then
        Set GetOutlookApp = CreateObject("Outlook.Application")
    End If

End Function

Application

The Application object represents the Outlook Application itself. The Application object can be used to access other classes of the Outlook Object Model and the functionality and properties of the Outlook application.

Namespace

The NameSpace object is an abstract root object that has methods for accessing accounts, folders, and data stores in Outlook. A NameSpace object can be retrieved using Application.GetNamespace("MAPI") or Application.Session which both return a NameSpace object for the MAPI namespace.

Note: The MAPI namespace is the only available namespace.

Public Sub Example()

    Dim NS As Outlook.NameSpace

    Set NS = Outlook.Application.GetNamespace("MAPI")
    Set NS = Outlook.Application.Session

End Sub

Accounts

The Account object represents an account in Outlook. The Accounts collection is a property of the NameSpace object and contains all accounts available within the current profile.

Get Specific Account

To retrieve an Account object for a specific account, access the account through the Accounts collection by index or by name.

Public Sub Example()

    Dim A As Outlook.Account

   'Set A = Outlook.Application.Session.Accounts("My.Email@email.com")
    Set A = Outlook.Application.Session.Accounts(1)

End Sub

Get All Account Information

Iterate over each Account object in the Accounts collection and access properties of the Account object.

Public Sub Example()

    Dim A As Outlook.Account

    For Each A In Outlook.Application.Session.Accounts
        Debug.Print A.DisplayName, GetOlAccountType(A.AccountType)
    Next A

End Sub

Public Function GetOlAccountType(AccountType As OlAccountType) As String
    Select Case AccountType
        Case olExchange: GetOlAccountType = "olExchange"
        Case olImap: GetOlAccountType = "olImap"
        Case olPop3: GetOlAccountType = "olPop3"
        Case olHttp: GetOlAccountType = "olHttp"
        Case olEas: GetOlAccountType = "olEas"
        Case olOtherAccount: GetOlAccountType = "olOtherAccount"
    End Select
End Function

Send Email From Specific Account

To send an email from a specific account set the SendUsingAccount property of a MailItem object.

Public Sub Example()

    Dim MI As Outlook.MailItem
    Set MI = Outlook.Application.CreateItem(olMailItem)

    Dim A As Outlook.Account
   'Set A = Outlook.Application.Session.Accounts("My.Email@email.com")
    Set A = Outlook.Application.Session.Accounts(1)

    With MI
        .To = A.SmtpAddress
        .Subject = "Test"
        .Body = "Hello, World!"
        .SendUsingAccount = A
       '.SentOnBehalfOfName = A.SmtpAddress
        .Send
    End With

End Sub

Folders

The Folder class represents an Outlook folder. The Folders class represents a collection of folder objects. A Folder object can be retrieved through the Folders collection or through the GetDefaultFolder method, the GetFolderFromID method, the PickFolder method, or by using a user-defined function to get a folder by path.

GetDefaultFolder

The GetDefaultFolder method is used to retrieve one of the default folders using the OlDefaultFolders enum.

Public Sub Example()

    Dim F As Outlook.Folder
    Set F = Outlook.Application.Session.GetDefaultFolder(olFolderInbox)

    Debug.Print F.FolderPath

End Sub

GetFolderFromID

The GetFolderFromID method uses the EntryID and StoreID to retrieve a folder.

Note: EntryID is not a safe method to retrieve an item in outlook because an EntryID can change.

Public Sub Example()

    Dim F As Outlook.Folder
    Set F = Outlook.Application.Session.GetDefaultFolder(olFolderInbox)

    Dim F1 As Outlook.Folder
    Set F1 = Outlook.Application.Session.GetFolderFromID(F.EntryID)

    Debug.Print F1.FolderPath

End Sub

PickFolder

The PickFolder method allows the user to manually select an outlook folder.

Public Sub Example()

    Dim F As Outlook.Folder
    Set F = Outlook.Application.Session.PickFolder()

    Debug.Print F.FolderPath

End Sub

GetFolderByPath

The user-defined GetFolderByPath function splits up the folder path and attempts to set each element to a Folder object. If the Folder object is set, the function will return the folder, otherwise it will return Nothing.

Public Sub Example()

    Dim F As Outlook.Folder
    Set F = GetFolderByPath("\\My.Email@email.com\Inbox")

    Debug.Print F.FolderPath

End Sub

Function GetFolderByPath(FolderPath As String) As Outlook.Folder

    Dim FolderPath1    As String
    Dim TestFolder     As Outlook.Folder
    Dim FoldersArray() As String
    Dim i              As Long

    FolderPath1 = FolderPath

    On Error GoTo Failed

    If Left$(FolderPath1, 2) = "\\" Then
        FolderPath1 = Right$(FolderPath1, Len(FolderPath1) - 2)
    End If

    FoldersArray = Split(FolderPath1, "\")

    Set TestFolder = Outlook.Application.Session.Folders.Item(FoldersArray(0))

    If Not TestFolder Is Nothing Then
        For i = 1 To UBound(FoldersArray)
            Set TestFolder = TestFolder.Folders.Item(FoldersArray(i))
        Next
    End If

    Set GetFolderByPath = TestFolder

    Exit Function

Failed:
    Set GetFolderByPath = Nothing

End Function

Stores

The Store class represents a file that stores emails and other items for an account. The Stores collection contains all the available Store objects for the current profile.

Public Sub Example()

    Dim S As Outlook.Store

    For Each S In Outlook.Application.Session.Stores
        Debug.Print S.DisplayName, S.StoreID
    Next S

End Sub

Explorers

The Explorer object represents the window in the Outlook application used to view the contents of a folder. The Explorers collection object represents a collection of all Explorer objects. The Explorers collection can be accessed through the Application.Explorers property.

Public Sub Example()

    Dim E As Outlook.Explorer

    For Each E In Outlook.Application.Explorers
        Debug.Print E.Caption
    Next E

End Sub

Inspectors

The Inspector object represents the window where an Outlook item is displayed. The Inspectors collection object is a collection containing all Inspector objects. The Inspectors collection can be accessed through the Application.Inspectors property.

Public Sub Example()

    Dim I As Outlook.Inspector

    For Each I In Outlook.Application.Inspectors
        Debug.Print I.Caption
    Next I

End Sub

Create Outlook Items

Various types of items can be created in Outlook using VBA. These include emails, meetings, SMS messages, and contacts. To create items in Outlook use the Application.CreateItem method with a member of the OlItemType enum.

Create Email

To create and send emails instantiate a MailItem object using the Application.CreateItem method with olMailItem.

Public Sub Example()

    Dim MI As Outlook.MailItem
    Set MI = Outlook.Application.CreateItem(olMailItem)

    With MI
        .To = "" 'Email Address Here
        .Subject = "Test"
        .Body = "Hello, World!"
        .Send
    End With

End Sub

Create Meeting

To create a meeting, create an AppointmentItem object using the Application.CreateItem method with olAppointmentItem and set the MeetingStatus property of the AppointmentItem object to olMeeting. Add recipients and set the Type property of each Recipient object.

Public Sub ExampleMail()

    Dim MI As Outlook.AppointmentItem
    Set MI = Outlook.Application.CreateItem(olAppointmentItem)

    With MI

        .MeetingStatus = olMeeting
        .Subject = "Meeting to discuss stuff"
        .Location = "The board room"
        .Start = Now()
        .Duration = 60

        Dim ReqAttendee As Recipient
        Set ReqAttendee = .Recipients.Add("Person 1")
        ReqAttendee.Type = olRequired

        Dim OptAttendee As Recipient
        Set OptAttendee = .Recipients.Add("Person 2")
        OptAttendee.Type = olOptional

        Dim ResourceAttendee As Recipient
        Set ResourceAttendee = .Recipients.Add("Board Room")
        ResourceAttendee.Type = olResource

        .Send

    End With

End Sub

Create SMS

To create an SMS message use the Application.CreateItem method with olMobileItemSMS to return a MailItem object. In the To field add the phone number followed by an @ sign followed by the SMS gateway.

Public Sub Example()

    Dim MI As Outlook.MailItem
    Set MI = Outlook.Application.CreateItem(olMobileItemSMS)

    With MI
        .To = "5165550100@vtext.com"
        .Body = "Hello, World!"
        .Send
    End With

End Sub

Create Contact

To create a new contact use the Application.CreateItem method with olContactItem to return a ContactItem object. Call the ContactItem.Save method or the ContactItem.SaveAs method to save the contact.

Public Sub CreateContact()

    Dim CI As Outlook.ContactItem
    Set CI = Application.CreateItem(olContactItem)

    CI.FirstName = "Person"
    CI.LastName = "Personson"
    CI.Email1Address = "PPersonson@email.com"

    CI.Save

End Sub

Attachments

Use the Attachments property of an Outlook item to access the Attachments collection object containing all the Attachment objects for that item.

Add Attachments to Email

To add an attachment to an email use the Attachments.Add method.

Public Sub Example()

    Dim MI As Outlook.MailItem
    Set MI = Outlook.Application.CreateItem(olMailItem)

    With MI
        .To = "" 'Add email address
        .Subject = "Test Attachment"
        .Body = "Please see attached."
        .Attachments.Add "C:\Example.xlsx"
        .Send
    End With

End Sub

Harvest Attachments From All Emails in a Folder

To harvest all the attachments from emails in a folder, select a folder, loop over all Items in the folder, and loop over all attachments in the items' Attachments collection, saving each attachment to a destination folder.

Public Sub HarvestEmails()

    Dim NS As Outlook.NameSpace
    Set NS = Outlook.Application.Session

    Dim TargetFolder As Outlook.Folder
    Set TargetFolder = NS.PickFolder

    If TargetFolder Is Nothing Then
        Exit Sub
    End If

    Dim DestinationFolderPath As String
    DestinationFolderPath = Environ$("USERPROFILE") & _
    "\Desktop\HarvestOutlookAttachments" & Format$(Now, "mmddyyyyhhmmss") & "\"

    MkDir DestinationFolderPath

    Dim Email As Outlook.MailItem
    For Each Email In TargetFolder.Items
        Dim A As Outlook.Attachment
        For Each A In Email.Attachments
            A.SaveAsFile DestinationFolderPath & A.FileName
        Next A
    Next Email

    MsgBox "Done"

End Sub