<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Automate Outlook in VBA with the OutlookCreateItem Class

Published on
39,663 Points
18,963 Views
17 Endorsements
Last Modified:
Awarded
Community Pick

Introduction



It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example:

I would like to send a standardized email to each recipient of a query.  I assume the code should be something like:
          For each "recipient" of "query x"
          send email
Thanks!


I have a consolidated workbook that programmatically breaks many of its worksheets into stand alone workbooks for the purpose of distributing them via Outlook to separate locations.  I have a table of recipients that looks sort of like this:
          LOC      Workbook Name      Regional VP      Area VP      General Manager
          1      LOC1.XLS      Theresa Jones      Katie Martinez      John Smith
          2      LOC2.XLS      Theresa Jones      Katie Martinez      Patty Reiker
          3      LOC3.XLS      Theresa Jones      Tom Black      Sheryl Thomas
          4      LOC4.XLS      Theresa Jones      Tom Black      Paul Green
I want to use this range to create drafts of emails with attachments. I'd like to send each location file to the Regional VP, the Area VP & the General Manager.  I can write it to send the Regional VP 4 emails with 4 different attachments, but what I'd really like to figure out is how a way to send the Regional VP one email with 4 attachments.


In the past, whenever I answered such questions, or needed to automate Outlook for my projects at work, I would typically write the code for each question or project as if I were doing this for the first time.  AT some point, it hit me that this was not optimal:

By not making use of a common base of pre-packaged procedures, I was not making efficient use of my time;
Using a common code base would help later in any kind of troubleshooting, and also in future updates/upgrades;
With a common code base, I could add advanced features such as error handling and logging; and
Since I typically used late binding (because I often must accommodate multiple versions of Office), I did not have access to Intellisense, which I could enable with a sufficiently developed class

The result is what I am calling the OutlookCreateItem class.  Adding this class to your projects will allow for easy automation support in your non-Outlook projects for creating and/or sending a variety of Outlook items:

Appointments and Meeting Requests;
Contacts;
Emails;
Notes; and
Tasks and Task Requests

This article describes the OutlookCreateItem class, how to use it in your projects, and how to add it to your projects.  The end of the article also includes a sample Excel file and sample Access database that demonstrates some of the concepts described in the article.

Note: I wrote this article from a VBA perspective, and all of my testing was in VBA, automating Outlook from other Office applications.  However, in theory the class should work just as well in a Visual Basic 6 project.  I did not test the OutlookCreateItem class in VB6, and have no plans to.

For those who cannot wait to start working with the code, you can download the following sample files:
Outlook-CreateItem-class.xls
OutlookCreateItem-class.mdb
Otherwise, please continue reading.


OutlookCreateItem Object Model and Source Code



The OutlookCreateItem class exposes a single object; you access all of the properties and methods through that root object.  No matter how many items you need to create, you will create a single instance of this class, create (and possibly send) as many items as needed, and then destroy the class instance.

The OutlookCreateItem class object model is as follows

OutlookCreateItem Object Model
The source code for the class is as follows:

' Class by Patrick Matthews, Verint Systems

' Use this class for any non-Outlook application that needs to create emails, tasks / task requests,
' or appointments / meeting requests via Outlook automation.  Using the class allows for easy
' "plug-n-play" enabling of Outlook automation in almost any VBA/VB6 application

' Class includes error handling and logging capabilities

Option Explicit
Option Compare Text

' Outlook instance
Private olApp As Object 'Outlook.Application

' Used for error logging, if applicable
Private fso As Object
Private tsLog As Object

' Containers for info on error logging
Private TotalErrors As Long
Private xLogFilePath As String
Private xErrorLogging As Boolean

' Outlook constants

Public Enum OlItemType
    olAppointmentItem = 1
    olContactItem = 2
    olMailItem = 0
    olNoteItem = 5
    olTaskItem = 3
End Enum

Public Enum OlMailRecipientType
    olCC = 2
    olBCC = 3
    olTo = 1
End Enum

Public Enum OlImportance
    olImportanceHigh = 2
    olImportanceLow = 0
    olImportanceNormal = 1
End Enum

Public Enum OlSensitivity
    olConfidential = 3
    olNormal = 0
    olPersonal = 1
    olPrivate = 2
End Enum

Public Enum OlMeetingStatus
    olMeeting = 1
End Enum

Public Enum OlMeetingRecipientType
    olOptional = 2
    olOrganizer = 0
    olRequired = 1
    olResource = 3
End Enum

Public Enum OlBusyStatus
    olBusy = 2
    olFree = 0
    olOutOfOffice = 3
    olTentative = 1
End Enum

Public Enum OlInspectorClose
    olSave = 0
End Enum

Public Enum OlDefaultFolders
    olFolderCalendar = 9
    olFolderContacts = 10
    olFolderDrafts = 16
    olFolderInbox = 6
    olFolderJournal = 11
    olFolderNotes = 12
    olFolderOutbox = 4
    olFolderSentMail = 5
    olFolderTasks = 13
    olPublicFoldersAllPublicFolders = 18
End Enum

Private Sub Class_Initialize()
    
    Set olApp = CreateObject("Outlook.Application")
    
End Sub

Private Sub Class_Terminate()
    
    Me.LogErrors False
    Set olApp = Nothing
    
End Sub

Public Function AddFolder(Name As String, Optional ParentFolder As Object, _
    Optional FolderType As OlDefaultFolders = -999) As Object 'Outlook.Folder
    
    ' Creates a new Outlook folder, adding it as a subfolder to the designated ParentFolder.
    ' If a folder with the same name already exists at that node, this will throw an error.
    
    ' Note that not all OlDefaultFolders values are allowed here.  Per Outlook VBA help,
    ' only olFolderCalendar, olFolderContacts, olFolderDrafts, olFolderInbox, olFolderJournal,
    ' olFolderNotes, or olFolderTasks can be used
    
    ' If FolderType is omitted, subfolder is of same type as parent folder
    
    If FolderType = -999 Then
        ParentFolder.Folders.Add Name
    Else
        ParentFolder.Folders.Add Name, FolderType
    End If
    
End Function

Public Function AddFolderFromPath(PathString As String, _
    Optional FolderType As OlDefaultFolders = -999) As Object 'Outlook.Folder
    
    ' Creates a new Outlook folder placed according to the indicated path, and returns that new
    ' folder
    
    ' Along the way, if folders in the specified PathString do not exist, those folders are created
    
    ' If a folder with that path already exists, an error results
    
    ' Use two backslash characters to delimit nested folders.  Double backslash at the beginning
    ' for the top level folder is optional.  For example, these both get the same folder:
    '
    ' \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    ' Inbox - Joe Schmoe\\Customers\\Acme Corp
    
    ' Note that not all OlDefaultFolders values are allowed here.  Per Outlook VBA help,
    ' only olFolderCalendar, olFolderContacts, olFolderDrafts, olFolderInbox, olFolderJournal,
    ' olFolderNotes, or olFolderTasks can be used
    
    ' If FolderType is omitted, subfolder is of same type as parent folder
    
    Dim PathArray As Variant
    Dim FolderName As String
    Dim xFolder As Object 'Outlook.Folder
    Dim yFolder As Object 'Outlook.Folder
    Dim Counter As Long
    
    If Left(PathString, 2) = "\\" Then PathString = Mid(PathString, 3)
    PathArray = Split(PathString, "\\")
    
    FolderName = PathArray(Counter)
    Set xFolder = Me.GetFolderFromPath(FolderName)
    If xFolder Is Nothing Then
        If FolderType = -999 Then
            Set xFolder = Me.OutlookApplication.Session.Folders.Add(FolderName)
        Else
            Set xFolder = Me.OutlookApplication.Session.Folders.Add(FolderName, FolderType)
        End If
    End If
    
    For Counter = 1 To UBound(PathArray)
        FolderName = PathArray(Counter)
        Set yFolder = Me.GetSubFolder(xFolder, FolderName)
        If yFolder Is Nothing Then
            If FolderType = -999 Then
                Set yFolder = xFolder.Folders.Add(FolderName)
            Else
                Set xFolder = xFolder.Folders.Add(FolderName, FolderType)
            End If
        End If
        Set xFolder = yFolder
    Next
    
    Set AddFolderFromPath = xFolder
    
End Function

Property Get CountOfErrors() As Long
    
    ' Returns number of errors.  Read only
    
    CountOfErrors = TotalErrors
    
End Property

Property Get ErrorLoggingEnabled() As Boolean
    
    ' Returns whether error logging is currently enabled (True) or not.  Read only
    
    ErrorLoggingEnabled = xErrorLogging
    
End Property

Public Function GetDefaultFolder(DefaultFolderType As OlDefaultFolders) As Object 'Outlook.Folder
    
    ' Returns one of the default folders
    
    Set GetDefaultFolder = Me.OutlookApplication.GetNamespace("MAPI").GetDefaultFolder(DefaultFolderType)
    
End Function

Public Function GetFolderFromPath(PathString As String) As Object 'Outlook.Folder
    
    ' Returns an Outlook folder found by traversing the indicated path, or Nothing if no
    ' such folder exists
    
    ' Use two backslash characters to delimit nested folders.  Double backslash at the beginning
    ' for the top level folder is optional.  For example, these both get the same folder:
    '
    ' \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    ' Inbox - Joe Schmoe\\Customers\\Acme Corp
    
    Dim PathArray As Variant
    Dim xFolder As Object 'Outlook.Folder
    Dim Counter As Long
    
    On Error GoTo ErrHandler
    
    If Left(PathString, 2) = "\\" Then PathString = Mid(PathString, 3)
    PathArray = Split(PathString, "\\")
    
    Set xFolder = Me.OutlookApplication.Session.Folders.Item(PathArray(0))
    
    For Counter = 1 To UBound(PathArray)
        Set xFolder = xFolder.Folders.Item(PathArray(Counter))
    Next
    
    Set GetFolderFromPath = xFolder
    Exit Function
    
ErrHandler:
    
    Set GetFolderFromPath = Nothing
    
End Function

Public Function GetParentFolder(UsingFolder As Object) As Object 'Outlook.Folder
    
    ' Returns an Outlook folder, the parent of the folder passed in as UsingFolder
    
    Set GetParentFolder = UsingFolder.Parent
    
End Function

Public Function GetSubFolder(UsingFolder As Object, Index As Variant) As Object 'Outlook.Folder
    
    ' Returns an Outlook folder, the indicated subfolder of UsingFolder.  The Index may
    ' be a String (the name of the subfolder) or a Long
    
    ' If subfolder does not exist, returns Nothing
    
    On Error Resume Next
    
    Set GetSubFolder = UsingFolder.Folders(Index)
    
    If Err <> 0 Then
        Err.Clear
        Set GetSubFolder = Nothing
    End If
    
    On Error GoTo 0
    
End Function

Public Sub LogErrors(Optional Enable As Boolean = True, Optional LogPath As String = "", _
    Optional Append As Boolean = True)
    
    ' Method for enabling/disabling error logging.  If nothing is passed for the LogPath,
    ' use a default path.  Error logging can either append to an existing file, if applicable,
    ' or write to a new file.  Using Append = False will force an over-write, if applicable
    
    Dim DefaultPath As String
    Dim SpecialFolders As Object
    
    Const ForAppending As Long = 8
    Const Headers As String = "DateTime,ItemType,Tag,Property,ErrDescr"
    
    If Not Enable Then
    
        ' Close the log if it's open, set objects to Nothing, and update private variables
        ' used for Property Gets
        
        If Not tsLog Is Nothing Then
            tsLog.Close
            Set tsLog = Nothing
            Set fso = Nothing
        End If
        xLogFilePath = ""
        xErrorLogging = False
    Else
        
        ' Update private variable
        
        xErrorLogging = True
        
        ' Resolve path name.  If path is not specified, then write to user's "My Documents"
        ' folder
        
        If Trim(LogPath) = "" Then
            Set SpecialFolders = CreateObject("WScript.Shell").SpecialFolders
            DefaultPath = SpecialFolders("mydocuments") & "\clsOutlookCreateItem Log.txt"
            Set SpecialFolders = Nothing
            xLogFilePath = DefaultPath
        Else
            xLogFilePath = LogPath
        End If
        
        ' Close existing log, if applicable
        
        If Not tsLog Is Nothing Then tsLog.Close
        If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
        If Append And fso.FileExists(xLogFilePath) Then
            
            ' If we want to append, and that file already exists, use OpenTextFile
            
            Set tsLog = fso.OpenTextFile(xLogFilePath, ForAppending)
        Else
        
            ' Otherwise, create new file, overwriting if need be
            
            Set tsLog = fso.CreateTextFile(xLogFilePath, True)
            tsLog.WriteLine Headers
        End If
    End If
        
End Sub

Property Get LogFilePath() As String
    
    ' Returns current path for log file.  Read only
    
    ' To set the path for the log file, use LogErrors
    
    LogFilePath = xLogFilePath
    
End Property

Public Function CreateContactItem(Optional LastName As String = "", Optional FirstName As String = "", _
    Optional MiddleName As String = "", Optional Title As String = "", _
    Optional Suffix As String = "", Optional FullName As String = "", _
    Optional FileAs As String = "", Optional Email1Address As String = "", _
    Optional Email1DisplayName As String = "", Optional Email1AddressType As String = "", _
    Optional CompanyName As String = "", Optional BusinessTelephoneNumber As String = "", _
    Optional BusinessFaxNumber As String = "", Optional BusinessAddressCity As String = "", _
    Optional BusinessAddressCountry As String, Optional BusinessAddressPostalCode As String = "", _
    Optional BusinessAddressPostOfficeBox As String = "", Optional BusinessAddressState As String, _
    Optional BusinessAddressStreet As String = "", Optional BusinessAddress As String = "", _
    Optional HomeTelephoneNumber As String = "", Optional HomeFaxNumber As String = "", _
    Optional HomeAddressCity As String = "", Optional HomeAddressCountry As String, _
    Optional HomeAddressPostalCode As String = "", Optional HomeAddressPostOfficeBox As String = "", _
    Optional HomeAddressState As String, Optional HomeAddressStreet As String = "", _
    Optional HomeAddress As String = "", Optional MobileTelephoneNumber As String = "", _
    Optional Categories As String = "", Optional AddPicture As String = "", _
    Optional Importance As OlImportance = olImportanceNormal, Optional Sensitivity As OlSensitivity = olNormal, _
    Optional Journal As Boolean = False, Optional Attachments As Variant = "", _
    Optional OtherProperties As Variant = "", Optional Tag As String = "", _
    Optional CloseRightAway As Boolean = True, Optional SaveToFolder As Variant = "") As Boolean
    
    ' Method for creating a contact item.  All arguments are optional
    
    ' Returns True for successful completion, and False otherwise.  If unsuccessful, no message is sent,
    ' and if logging is enabled at the class level, the procedure writes details to the log file
    
    ' Most arguments correspond to ContactItem properties in Outlook, so look in the Outlook VBA help
    ' file for documentation on them.  The exceptions:
    '
    ' - AddPicture:      AddPicture is a ContactItem method, not a property.  Adds a picture to the
    '                    ContactItem.  Argument value is a string specifying the path to the
    '                    picture file
    '
    ' - Attachments:     Can be either a 1-dimensional array of attachment paths, or a string.  If an
    '                    array, use each member of the array to list the file path.  If a string with
    '                    Len > 0, add the file given the path
    '
    ' - Tag:             Allows passing a user-defined value that is not used in the MailItem, but may
    '                    be helpful for troubleshooting errors.  For example, you might pass in a row
    '                    number from Excel or a primary key value from Access
    '
    ' - CloseRightAway:  Determines whether to send the item (True), or open the item in an Inspector
    '
    ' - OtherProperties: Allows user to set other item properties not already handled in the basic
    '                    class.  If used, pass as a two-dimensional array, with the first dimension
    '                    indicating the property name, and the second indicating the value to use
    '
    ' SaveToFolder:      If you wish to save the new item to a folder other than its default folder, use
    '                    this argument.  Argument can be an object representing the Outlook folder, or it
    '                    can be a string.  If a string, it identifies the folder by path.  Use two backslash
    '                    characters to delimit nested folders.  Double backslash at the beginning for the
    '                    top level folder is optional.  For example, these both get the same folder:
    '                    -------
    '                    \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    -------
    '                    If the folder does not already exist, the folder will be created (as will
    '                    any "missing" levels in the indicated path)
    
    Dim olContact As Object 'Outlook.ContactItem
    Dim Counter As Long
    Dim CurrentProperty As String
    Dim ItemProp As Object 'Outlook.ItemProperty
    Dim TestFolder As Object 'Outlook.Folder
    
    On Error GoTo ErrHandler
    
    CurrentProperty = "CreateItem"
    Set olContact = olApp.CreateItem(olContactItem)
    
    With olContact
    
        ' Standard properties
        
        CurrentProperty = "LastName"
        If LastName <> "" Then .LastName = LastName
        CurrentProperty = "FirstName"
        If FirstName <> "" Then .FirstName = FirstName
        CurrentProperty = "MiddleName"
        If MiddleName <> "" Then .MiddleName = MiddleName
        CurrentProperty = "Title"
        If Title <> "" Then .Title = Title
        CurrentProperty = "Suffix"
        If Suffix <> "" Then .Suffix = Suffix
        CurrentProperty = "FullName"
        If FullName <> "" Then .FullName = FullName
        CurrentProperty = "FileAs"
        If FileAs <> "" Then .FileAs = FileAs
        CurrentProperty = "Email1Address"
        If Email1Address <> "" Then .Email1Address = Email1Address
        CurrentProperty = "Email1DisplayName"
        If Email1DisplayName <> "" Then .Email1DisplayName = Email1DisplayName
        CurrentProperty = "Email1AddressType"
        If Email1AddressType <> "" Then .Email1AddressType = Email1AddressType
        CurrentProperty = "CompanyName"
        If CompanyName <> "" Then .CompanyName = CompanyName
        CurrentProperty = "BusinessTelephoneNumber"
        If BusinessTelephoneNumber <> "" Then .BusinessTelephoneNumber = BusinessTelephoneNumber
        CurrentProperty = "BusinessFaxNumber"
        If BusinessFaxNumber <> "" Then .BusinessFaxNumber = BusinessFaxNumber
        CurrentProperty = "BusinessAddressCity"
        If BusinessAddressCity <> "" Then .BusinessAddressCity = BusinessAddressCity
        CurrentProperty = "BusinessAddressCountry"
        If BusinessAddressCountry <> "" Then .BusinessAddressCountry = BusinessAddressCountry
        CurrentProperty = "BusinessAddressPostalCode"
        If BusinessAddressPostalCode <> "" Then .BusinessAddressPostalCode = BusinessAddressPostalCode
        CurrentProperty = "BusinessAddressPostOfficeBox"
        If BusinessAddressPostOfficeBox <> "" Then .BusinessAddressPostOfficeBox = BusinessAddressPostOfficeBox
        CurrentProperty = "BusinessAddressState"
        If BusinessAddressState <> "" Then .BusinessAddressState = BusinessAddressState
        CurrentProperty = "BusinessAddressStreet"
        If BusinessAddressStreet <> "" Then .BusinessAddressStreet = BusinessAddressStreet
        CurrentProperty = "BusinessAddress"
        If BusinessAddress <> "" Then .BusinessAddress = BusinessAddress
        CurrentProperty = "HomeTelephoneNumber"
        If HomeTelephoneNumber <> "" Then .HomeTelephoneNumber = HomeTelephoneNumber
        CurrentProperty = "HomeFaxNumber"
        If HomeFaxNumber <> "" Then .HomeFaxNumber = HomeFaxNumber
        CurrentProperty = "HomeAddressCity"
        If HomeAddressCity <> "" Then .HomeAddressCity = HomeAddressCity
        CurrentProperty = "HomeAddressCountry"
        If HomeAddressCountry <> "" Then .HomeAddressCountry = HomeAddressCountry
        CurrentProperty = "HomeAddressPostalCode"
        If HomeAddressPostalCode <> "" Then .HomeAddressPostalCode = HomeAddressPostalCode
        CurrentProperty = "HomeAddressPostOfficeBox"
        If HomeAddressPostOfficeBox <> "" Then .HomeAddressPostOfficeBox = HomeAddressPostOfficeBox
        CurrentProperty = "HomeAddressState"
        If HomeAddressState <> "" Then .HomeAddressState = HomeAddressState
        CurrentProperty = "HomeAddressStreet"
        If HomeAddressStreet <> "" Then .HomeAddressStreet = HomeAddressStreet
        CurrentProperty = "HomeAddress"
        If HomeAddress <> "" Then .HomeAddress = HomeAddress
        CurrentProperty = "MobileTelephoneNumber"
        If MobileTelephoneNumber <> "" Then .MobileTelephoneNumber = MobileTelephoneNumber
        CurrentProperty = "Categories"
        If Categories <> "" Then .Categories = Categories
        CurrentProperty = "Importance"
        .Importance = Importance
        CurrentProperty = "Sensitivity"
        .Sensitivity = Sensitivity
        CurrentProperty = "Journal"
        .Journal = Journal
        
        ' Add picture if applicable
        
        CurrentProperty = "AddPicture"
        If AddPicture <> "" Then .AddPicture AddPicture
        
        ' Process Attachments.  For multiple files, use an array.  For a single file, use a string
        
        CurrentProperty = "Attachments"
        If IsArray(Attachments) Then
            For Counter = LBound(Attachments) To UBound(Attachments)
                .Attachments.Add Attachments(Counter)
            Next
        Else
            If Not IsMissing(Attachments) Then
                If Attachments <> "" Then .Attachments.Add Attachments
            End If
        End If
        
        ' Process OtherProperties, if applicable.  If argument value is not an array, or is an array
        ' with just one dimension, this will throw an error
        
        CurrentProperty = "OtherProperties"
        If IsArray(OtherProperties) Then
            
            ' The code does not specify what the bounds have to be for the OtherProperties array, so
            ' to get the properties, use LBound/UBound to set the parameters for the loop.  Likewise,
            ' since we do not know ahead of time what the bounds will be for the second dimension
            ' (could be 0 To 1, could be 1 To 2), use LBound to get property name and UBound to get
            ' the property value.  Theoretically, the second dimension could have >= 3 elements, but
            ' the code will still work if the property name is in the first element and property
            ' value is in the second
            
            For Counter = LBound(OtherProperties, 1) To UBound(OtherProperties, 1)
                Set ItemProp = .ItemProperties.Item(OtherProperties(Counter, LBound(OtherProperties, 2)))
                ItemProp.Value = OtherProperties(Counter, UBound(OtherProperties, 2))
            Next
        End If
        
        ' Use this to save the item in a folder other than the default folder for its type.  If SaveToFolder
        ' is passed as an Outlook.Folder object, then use that.  If it is passed as a path string, then
        ' first try to get that folder, or if it does not exist, create it
        
        CurrentProperty = "SaveToFolder"
        If IsObject(SaveToFolder) Then
            If Not SaveToFolder Is Nothing Then
                .Save
                .Move SaveToFolder
            End If
        Else
            If SaveToFolder <> "" Then
                Set TestFolder = Me.GetFolderFromPath(CStr(SaveToFolder))
                If TestFolder Is Nothing Then Set TestFolder = Me.AddFolderFromPath(CStr(SaveToFolder))
                .Save
                .Move TestFolder
            End If
        End If
        
        ' Save/close or display the item in an Inspector
        
        If CloseRightAway Then
            CurrentProperty = "Close"
            .Close olSave
        Else
            CurrentProperty = "Display"
            .Display
        End If
        
    End With
    
    CreateContactItem = True
    
    GoTo Cleanup
    
ErrHandler:
    
    ' Log error if applicable
    
    If Me.ErrorLoggingEnabled Then WriteToLog "ContactItem", Tag, CurrentProperty, Err.Description
    CreateContactItem = False
    
Cleanup:
    
    Set olContact = Nothing
    
End Function

Public Function CreateMailItem(SendTo As Variant, Optional CC As Variant = "", _
    Optional BCC As Variant = "", Optional Subject As String = "", _
    Optional Body As String = "", Optional HTMLBody As String = "", _
    Optional Attachments As Variant, Optional Importance As OlImportance = olImportanceNormal, _
    Optional Categories As String = "", Optional DeferredDeliveryTime As Date = #1/1/1950#, _
    Optional DeleteAfterSubmit As Boolean = False, Optional FlagRequest As String = "", _
    Optional ReadReceiptRequested As Boolean = False, Optional Sensitivity As OlSensitivity = olNormal, _
    Optional SaveSentMessageFolder As Variant = "", Optional CloseRightAway As Boolean = True, _
    Optional EnableReply As Boolean = True, Optional EnableReplyAll As Boolean, _
    Optional EnableForward As Boolean = True, Optional ReplyRecipients As Variant = "", _
    Optional OtherProperties As Variant = "", Optional Tag As String = "") As Boolean
    
    ' Method for creating (and sending, if desired) mail items.  SendTo is required; all other arguments
    ' are optional.
    
    ' Returns True for successful completion, and False otherwise.  If unsuccessful, no message is sent,
    ' and if logging is enabled at the class level, the procedure writes details to the log file
    
    ' Most arguments correspond to MailItem properties in Outlook, so look in the Outlook VBA help file
    ' for documentation on them.  The exceptions:
    '
    ' - SendTo, CC, BCC:     Can be either a 1-dimensional array of recipients, or a string.  If an
    '                        array, use each member of the array to list the recipients.  If a string
    '                        with Len > 0, use the To/CC/BCC properties to add via string.  For
    '                        multiple recipients using string, use semicolon as delimiter
    '
    ' - Attachments:         Can be either a 1-dimensional array of attachment paths, or a string.  If an
    '                        array, use each member of the array to list the file path.  If a string with
    '                        Len > 0, add the file given the path
    '
    ' - Tag:                 Allows passing a user-defined value that is not used in the MailItem,
    '                        but may be helpful for troubleshooting.  For example, you might pass in a
    '                        row number from Excel or a primary key value from Access
    '
    ' SaveSentMessageFolder: In Outlook this is always an Outlook folder.  Here, it can be an object
    '                        representing the Outlook folder, or it can be a string.  If a string, it
    '                        identifies the folder by path.  Use two backslash characters to delimit
    '                        nested folders.  Double backslash at the beginning for the top level folder
    '                        is optional.  For example, these both get the same folder:
    '                        -------
    '                        \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                        Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                        -------
    '                        If the folder does not already exist, the folder will be created (as will
    '                        any "missing" levels in the indicated path)
    '
    ' - CloseRightAway:      Determines whether to send the item (True), or open the item in an Inspector
    '
    ' - EnableReply:         Allow user to reply to the message
    '
    ' - EnableReplyAll:      Allow user to reply all to the message
    '
    ' - EnableForward:       Allow user to forward the message
    '
    ' - ReplyRecipients:     Can be either a 1-dimensional array of recipients, or a string.  If an
    '                        array, use each member of the array to list the recipients.  For multiple
    '                        recipients always use an array
    '
    ' - OtherProperties:     Allows user to set other item properties not already handled in the basic
    '                        class.  If used, pass as a two-dimensional array, with the first dimension
    '                        indicating the property name, and the second indicating the value to use
    
    ' Note on EnableReply/EnableReplyAll/EnableForward: These will only work on messages sent within
    ' your organization, and can be easily "undone" by someone who knows how to manipulate Actions
    
    ' At each step, CurrentProperty variable updates to reflect what we are doing.  This facilitates
    ' error logging
    
    Dim olMsg As Object 'Outlook.MailItem
    Dim olRecip As Object 'Outlook.Recipient
    Dim Counter As Long
    Dim CurrentProperty As String
    Dim ItemProp As Object 'Outlook.ItemProperty
    Dim SaveToFolder As Object 'Outlook.Folder
    
    On Error GoTo ErrHandler
    
    CurrentProperty = "CreateItem"
    Set olMsg = olApp.CreateItem(olMailItem)
    
    With olMsg
        
        ' For SendTo, CC, and BCC, if they are arrays, process each element of array through the Recipients
        ' collection.  If not, then if Len > 0 then pass in the string values via To, CC, and BCC
        
        CurrentProperty = "To"
        If IsArray(SendTo) Then
            For Counter = LBound(SendTo) To UBound(SendTo)
                Set olRecip = .Recipients.Add(SendTo(Counter))
                olRecip.Type = olTo
            Next
        Else
            If SendTo <> "" Then .To = SendTo
        End If
        CurrentProperty = "CC"
        If IsArray(CC) Then
            For Counter = LBound(CC) To UBound(CC)
                Set olRecip = .Recipients.Add(CC(Counter))
                olRecip.Type = olCC
            Next
        Else
            If CC <> "" Then .CC = CC
        End If
        CurrentProperty = "BCC"
        If IsArray(BCC) Then
            For Counter = LBound(BCC) To UBound(BCC)
                Set olRecip = .Recipients.Add(BCC(Counter))
                olRecip.Type = olBCC
            Next
        Else
            If BCC <> "" Then .BCC = BCC
        End If
        
        ' Set ReplyRecipients.  If a 1-D array, adds each element in array.  If string, adds
        ' from the string
        
        CurrentProperty = "ReplyRecipients"
        If IsArray(ReplyRecipients) Then
            For Counter = LBound(ReplyRecipients) To UBound(ReplyRecipients)
                Set olRecip = .ReplyRecipients.Add(ReplyRecipients(Counter))
            Next
        Else
            If ReplyRecipients <> "" Then .ReplyRecipients.Add ReplyRecipients
        End If
        
        ' Standard field
        
        CurrentProperty = "Subject"
        .Subject = Subject
        
        ' If both Body and HTMLBody are given, Body wins
        
        CurrentProperty = "Body"
        If Body <> "" Then .Body = Body
        CurrentProperty = "HTMLBody"
        If HTMLBody <> "" And Body = "" Then .HTMLBody = HTMLBody
        
        ' Process Attachments.  For multiple files, use an array.  For a single file, use a string
        
        CurrentProperty = "Attachments"
        If IsArray(Attachments) Then
            For Counter = LBound(Attachments) To UBound(Attachments)
                .Attachments.Add Attachments(Counter)
            Next
        Else
            If Not IsMissing(Attachments) Then
                If Attachments <> "" Then .Attachments.Add Attachments
            End If
        End If
        
        ' Standard
        
        CurrentProperty = "Importance"
        .Importance = Importance
        CurrentProperty = "Categories"
        If Categories <> "" Then .Categories = Categories
        CurrentProperty = "DeferredDeliveryTime"
        If DeferredDeliveryTime >= DateAdd("n", 2, Now) Then .DeferredDeliveryTime = DeferredDeliveryTime
        CurrentProperty = "DeleteAfterSubmit"
        .DeleteAfterSubmit = DeleteAfterSubmit
        
        ' Added in Outlook 2007.  By checking the Outlook version we avoid a potential error
        
        If Val(olApp.Version) >= 12 Then
            CurrentProperty = "FlagRequest"
            If FlagRequest <> "" Then .FlagRequest = FlagRequest
        End If
        
        ' Standard
        
        CurrentProperty = "ReadReceiptRequested"
        .ReadReceiptRequested = ReadReceiptRequested
        CurrentProperty = "Sensitivity"
        .Sensitivity = Sensitivity
        
        ' Argument can be passed as an Outlook folder or a string (indicating the folder to use by path.
        ' If a folder, then set the property directly.  If a string, first try to get the folder using
        ' GetFolderFromPath.  If folder does not already exist, use AddFolderFromPath to create the
        ' folder
        
        CurrentProperty = "SaveSentMessageFolder"
        If IsObject(SaveSentMessageFolder) Then
            If Not SaveSentMessageFolder Is Nothing Then Set .SaveSentMessageFolder = SaveSentMessageFolder
        Else
            If SaveSentMessageFolder <> "" Then
                Set SaveToFolder = Me.GetFolderFromPath(CStr(SaveSentMessageFolder))
                If SaveToFolder Is Nothing Then Set SaveToFolder = Me.AddFolderFromPath(CStr(SaveSentMessageFolder))
                Set .SaveSentMessageFolder = SaveToFolder
            End If
        End If
        
        ' Keep in mind that these do not apply outside your organization, and can be reversed!
        
        .Actions("Reply").Enabled = EnableReply
        .Actions("Reply to All").Enabled = EnableReplyAll
        .Actions("Forward").Enabled = EnableForward
        
        ' Process OtherProperties, if applicable.  If argument value is not an array, or is an array
        ' with just one dimension, this will throw an error
        
        CurrentProperty = "OtherProperties"
        If IsArray(OtherProperties) Then
            
            ' The code does not specify what the bounds have to be for the OtherProperties array, so
            ' to get the properties, use LBound/UBound to set the parameters for the loop.  Likewise,
            ' since we do not know ahead of time what the bounds will be for the second dimension
            ' (could be 0 To 1, could be 1 To 2), use LBound to get property name and UBound to get
            ' the property value.  Theoretically, the second dimension could have >= 3 elements, but
            ' the code will still work if the property name is in the first element and property
            ' value is in the second
            
            For Counter = LBound(OtherProperties, 1) To UBound(OtherProperties, 1)
                Set ItemProp = .ItemProperties.Item(OtherProperties(Counter, LBound(OtherProperties, 2)))
                ItemProp.Value = OtherProperties(Counter, UBound(OtherProperties, 2))
            Next
        End If
        
        ' Determine whether to send or display
        
        If CloseRightAway Then
            CurrentProperty = "Send"
            .Send
        Else
            CurrentProperty = "Display"
            .Display
        End If
    End With
    
    CreateMailItem = True
    
    GoTo Cleanup
    
ErrHandler:
    
    ' Log error if applicable
    
    If Me.ErrorLoggingEnabled Then WriteToLog "MailItem", Tag, CurrentProperty, Err.Description
    CreateMailItem = False
    
Cleanup:
    
    Set olMsg = Nothing
    
End Function
    
Function CreateAppointmentItem(StartAt As Date, Optional Duration As Long = 30, _
    Optional EndAt As Date = #1/1/1950#, Optional RequiredAttendees As Variant = "", _
    Optional OptionalAttendees As Variant = "", Optional Subject As String = "", _
    Optional Body As String = "", Optional Location As String = "", _
    Optional AllDayEvent As Boolean = False, Optional Attachments As Variant = "", _
    Optional BusyStatus As OlBusyStatus = olBusy, Optional Categories As String = "", _
    Optional Importance As OlImportance = olImportanceNormal, Optional Organizer As Variant = "", _
    Optional ReminderMinutesBeforeStart As Long = 15, Optional ReminderSet As Boolean = True, _
    Optional Resources As Variant = "", Optional Sensitivity As OlSensitivity = olNormal, _
    Optional Tag As String = "", Optional CloseRightAway As Boolean = True, _
    Optional OtherProperties As Variant = "", Optional SaveToFolder As Variant = "") As Boolean
    
    ' Method for creating appointments / sending meeting requests.  StartAt is required; all other
    ' arguments are optional.
    
    ' Due to the complexity involved, this class does not set recurrence patterns
    
    ' If there are no attendees, then it is simply an appointment.  If there are, then a meeting
    ' request is sent
    
    ' Returns True for successful completion, and False otherwise.  If unsuccessful, no message is sent,
    ' and if logging is enabled at the class level, the procedure writes details to the log file
    
    ' Most arguments correspond to AppointmentItem properties in Outlook, so look in the Outlook VBA
    ' help file for documentation on them.  The exceptions:
    '
    ' - RequiredAttendees,  Each can be a 1-dimensional array of the various recipient types, or can
    ' OptionalAttendees,    be a string.  Only use a string when you have one of that recipient type
    ' Resources,
    ' Organizer:            NOTE! In Outlook 2007, if you set CloseRightAway = False -- i.e., display
    '                       first rather than sending, all attendees get forced to Required!  Not
    '                       tested on other versions, so I cannot say whether or not that behavior
    '                       happens in other Outlook versions...
    '
    ' - Attachments:        Can be either a 1-dimensional array of attachment paths, or a string.  If
    '                       an array, use each member of the array to list the file path.  If a string,
    '                       add the file given the path
    '
    ' - Tag:                Allows passing a user-defined value that is not used in the MailItem, but
    '                       may be helpful for troubleshooting errors.  For example, you might pass in
    '                       a row number from Excel or a primary key value from Access
    '
    ' - CloseRightAway:      Determines whether to send the item (True), or open the item in an Inspector
    '
    ' - OtherProperties: Allows user to set other item properties not already handled in the basic
    '                    class.  If used, pass as a two-dimensional array, with the first dimension
    '                    indicating the property name, and the second indicating the value to use
    '
    ' SaveToFolder:      If you wish to save the new item to a folder other than its default folder, use
    '                    this argument.  Argument can be an object representing the Outlook folder, or it
    '                    can be a string.  If a string, it identifies the folder by path.  Use two backslash
    '                    characters to delimit nested folders.  Double backslash at the beginning for the
    '                    top level folder is optional.  For example, these both get the same folder:
    '                    -------
    '                    \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    -------
    '                    If the folder does not already exist, the folder will be created (as will
    '                    any "missing" levels in the indicated path)
    
    ' At each step, CurrentProperty variable updates to reflect what we are doing.  This facilitates
    ' error logging
    
    Dim olAppt As Object 'Outlook.AppointmentItem
    Dim Counter As Long
    Dim CurrentProperty As String
    Dim EndFromDuration As Date
    Dim olRecip As Object 'Outlook.Recipient
    Dim ItemProp As Object 'Outlook.ItemProperty
    Dim TestFolder As Object 'Outlook.Folder
    
    On Error GoTo ErrHandler
    
    CurrentProperty = "CreateItem"
    Set olAppt = olApp.CreateItem(olAppointmentItem)
    With olAppt
        
        ' If there are attendees, make this a meeting
        
        CurrentProperty = "MeetingStatus"
        If IsArray(RequiredAttendees) Then
            .MeetingStatus = olMeeting
        ElseIf RequiredAttendees <> "" Then
            .MeetingStatus = olMeeting
        ElseIf IsArray(OptionalAttendees) Then
            .MeetingStatus = olMeeting
        ElseIf OptionalAttendees <> "" Then
            .MeetingStatus = olMeeting
        ElseIf IsArray(Organizer) Then
            .MeetingStatus = olMeeting
        ElseIf Organizer <> "" Then
            .MeetingStatus = olMeeting
        ElseIf IsArray(Resources) Then
            .MeetingStatus = olMeeting
        ElseIf Resources <> "" Then
            .MeetingStatus = olMeeting
        End If
        
        ' Standard field
        
        CurrentProperty = "Start"
        If StartAt >= Date Then .Start = StartAt
        
        ' There are two ways to indicate the end of the meeting: End and Duration.  Method uses
        ' whichever argument would lead to the longer meeting.  Thus, for:
        '
        '         StartAt = #2010-12-01 08:00
        '         EndAt = #2010-12-01 08:30
        '         Duration = 60
        '
        ' we would set the ending at #2010-12-01 09:00 and for:
        '
        '         StartAt = #2010-12-01 08:00
        '         EndAt = #2010-12-01 10:00
        '         Duration = 30
        '
        ' we would set the ending at #2010-12-01 10:00
        
        CurrentProperty = "End"
        EndFromDuration = DateAdd("n", Duration, StartAt)
        If EndFromDuration >= EndAt Then
            .Duration = Duration
        Else
            .End = EndAt
        End If
        
        ' Add RequiredAttendees, OptionalAttendees, Resources, and Organizer.  May come
        ' in as arrays or strings
        
        CurrentProperty = "RequiredAttendees"
        If IsArray(RequiredAttendees) Then
            For Counter = LBound(RequiredAttendees) To UBound(RequiredAttendees)
                Set olRecip = .Recipients.Add(RequiredAttendees(Counter))
                olRecip.Type = olRequired
            Next
        Else
            If RequiredAttendees <> "" Then
                Set olRecip = .Recipients.Add(RequiredAttendees)
                olRecip.Type = olRequired
            End If
        End If
        CurrentProperty = "OptionalAttendees"
        If IsArray(OptionalAttendees) Then
            For Counter = LBound(OptionalAttendees) To UBound(OptionalAttendees)
                Set olRecip = .Recipients.Add(OptionalAttendees(Counter))
                olRecip.Type = olOptional
            Next
        Else
            If OptionalAttendees <> "" Then
                Set olRecip = .Recipients.Add(OptionalAttendees)
                olRecip.Type = olOptional
            End If
        End If
        CurrentProperty = "Resources"
        If IsArray(Resources) Then
            For Counter = LBound(Resources) To UBound(Resources)
                Set olRecip = .Recipients.Add(Resources(Counter))
                olRecip.Type = olResource
            Next
        Else
            If Resources <> "" Then
                Set olRecip = .Recipients.Add(Resources)
                olRecip.Type = olResource
            End If
        End If
        CurrentProperty = "Organizer"
        If IsArray(Organizer) Then
            For Counter = LBound(Organizer) To UBound(Organizer)
                Set olRecip = .Recipients.Add(Organizer(Counter))
                olRecip.Type = olOrganizer
            Next
        Else
            If Organizer <> "" Then
                Set olRecip = .Recipients.Add(Organizer)
                olRecip.Type = olOrganizer
            End If
        End If
        
        ' Standard fields
        
        CurrentProperty = "Subject"
        .Subject = Subject
        CurrentProperty = "Body"
        If Body <> "" Then .Body = Body
        CurrentProperty = "Location"
        If Location <> "" Then .Location = Location
        CurrentProperty = "AllDayEvent"
        .AllDayEvent = AllDayEvent
        
        ' Process Attachments.  For multiple files, use an array.  For a single file, use a string
        
        CurrentProperty = "Attachments"
        If IsArray(Attachments) Then
            For Counter = LBound(Attachments) To UBound(Attachments)
                .Attachments.Add Attachments(Counter)
            Next
        Else
            If Attachments <> "" Then .Attachments.Add Attachments
        End If
        
        ' Standard fields
        
        CurrentProperty = "BusyStatus"
        .BusyStatus = BusyStatus
        CurrentProperty = "Categories"
        If Categories <> "" Then .Categories = Categories
        CurrentProperty = "Importance"
        .Importance = Importance
        CurrentProperty = "ReminderSet"
        If ReminderSet Then
            .ReminderSet = True
            If ReminderMinutesBeforeStart < 0 Then ReminderMinutesBeforeStart = 0
            .ReminderMinutesBeforeStart = ReminderMinutesBeforeStart
        Else
            .ReminderSet = False
            .ReminderMinutesBeforeStart = 0
        End If
        CurrentProperty = "Sensitivity"
        .Sensitivity = Sensitivity
        
        ' Process OtherProperties, if applicable.  If argument value is not an array, or is an array
        ' with just one dimension, this will throw an error
        
        CurrentProperty = "OtherProperties"
        If IsArray(OtherProperties) Then
            
            ' The code does not specify what the bounds have to be for the OtherProperties array, so
            ' to get the properties, use LBound/UBound to set the parameters for the loop.  Likewise,
            ' since we do not know ahead of time what the bounds will be for the second dimension
            ' (could be 0 To 1, could be 1 To 2), use LBound to get property name and UBound to get
            ' the property value.  Theoretically, the second dimension could have >= 3 elements, but
            ' the code will still work if the property name is in the first element and property
            ' value is in the second
            
            For Counter = LBound(OtherProperties, 1) To UBound(OtherProperties, 1)
                Set ItemProp = .ItemProperties.Item(OtherProperties(Counter, LBound(OtherProperties, 2)))
                ItemProp.Value = OtherProperties(Counter, UBound(OtherProperties, 2))
            Next
        End If
        
        ' Use this to save the item in a folder other than the default folder for its type.  If SaveToFolder
        ' is passed as an Outlook.Folder object, then use that.  If it is passed as a path string, then
        ' first try to get that folder, or if it does not exist, create it
        
        CurrentProperty = "SaveToFolder"
        If IsObject(SaveToFolder) Then
            If Not SaveToFolder Is Nothing Then
                .Save
                .Move SaveToFolder
            End If
        Else
            If SaveToFolder <> "" Then
                Set TestFolder = Me.GetFolderFromPath(CStr(SaveToFolder))
                If TestFolder Is Nothing Then Set TestFolder = Me.AddFolderFromPath(CStr(SaveToFolder))
                .Save
                .Move TestFolder
            End If
        End If
        
        ' If there are no attendees, then it is just an appointment, and the choice is Close/Display.
        ' If there are attendees, then it is a meeting request, and the choice is Send/Display.
        
        If .Recipients.Count > 0 Then
            If CloseRightAway Then
                CurrentProperty = "Send"
                .Send
            Else
                CurrentProperty = "Display"
                .Display
            End If
        Else
            If CloseRightAway Then
                CurrentProperty = "Close"
                .Close olSave
            Else
                CurrentProperty = "Display"
                .Display
            End If
        End If
    End With
    
    CreateAppointmentItem = True
    
    GoTo Cleanup
    
ErrHandler:
    
    ' Log error if applicable
    
    If Me.ErrorLoggingEnabled Then WriteToLog "AppointmentItem", Tag, CurrentProperty, Err.Description
    CreateAppointmentItem = False
    
Cleanup:
    
    Set olAppt = Nothing
    
End Function

Function CreateNoteItem(Body As String, Optional Categories As String = "", _
    Optional OtherProperties As Variant = "", Optional Tag As String = "", _
    Optional CloseRightAway As Boolean = True, Optional SaveToFolder As Variant = "") As Boolean

    ' Method for creating Notes.  Body is required; all other arguments are optional.
    
    ' Most arguments correspond to TaskItem properties in Outlook, so look in the Outlook VBA
    ' help file for documentation on them.  The exceptions:
    '
    ' - Tag:             Allows passing a user-defined value that is not used in the MailItem, but may
    '                    be helpful for troubleshooting errors.  For example, you might pass in a row
    '                    number from Excel or a primary key value from Access
    '
    ' - CloseRightAway:  Determines whether to send the item (True), or open the item in an Inspector
    '
    ' - OtherProperties: Allows user to set other item properties not already handled in the basic
    '                    class.  If used, pass as a two-dimensional array, with the first dimension
    '                    indicating the property name, and the second indicating the value to use
    '
    ' SaveToFolder:      If you wish to save the new item to a folder other than its default folder, use
    '                    this argument.  Argument can be an object representing the Outlook folder, or it
    '                    can be a string.  If a string, it identifies the folder by path.  Use two backslash
    '                    characters to delimit nested folders.  Double backslash at the beginning for the
    '                    top level folder is optional.  For example, these both get the same folder:
    '                    -------
    '                    \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    -------
    '                    If the folder does not already exist, the folder will be created (as will
    '                    any "missing" levels in the indicated path)
    
    Dim olNote As Object 'Outlook.NoteItem
    Dim Counter As Long
    Dim CurrentProperty As String
    Dim ItemProp As Object 'Outlook.ItemProperty
    Dim TestFolder As Object 'Outlook.Folder
    
    On Error GoTo ErrHandler
    
    CurrentProperty = "CreateItem"
    Set olNote = olApp.CreateItem(olNoteItem)
    With olNote
        
        ' Standard
        
        .Body = Body
        If Categories <> "" Then .Categories = Categories
        
        ' Process OtherProperties
        
        CurrentProperty = "OtherProperties"
        If IsArray(OtherProperties) Then
            
            ' The code does not specify what the bounds have to be for the OtherProperties array, so
            ' to get the properties, use LBound/UBound to set the parameters for the loop.  Likewise,
            ' since we do not know ahead of time what the bounds will be for the second dimension
            ' (could be 0 To 1, could be 1 To 2), use LBound to get property name and UBound to get
            ' the property value.  Theoretically, the second dimension could have >= 3 elements, but
            ' the code will still work if the property name is in the first element and property
            ' value is in the second
            
            For Counter = LBound(OtherProperties, 1) To UBound(OtherProperties, 1)
                Set ItemProp = .ItemProperties.Item(OtherProperties(Counter, LBound(OtherProperties, 2)))
                ItemProp.Value = OtherProperties(Counter, UBound(OtherProperties, 2))
            Next
        End If
        
        ' Use this to save the item in a folder other than the default folder for its type.  If SaveToFolder
        ' is passed as an Outlook.Folder object, then use that.  If it is passed as a path string, then
        ' first try to get that folder, or if it does not exist, create it
        
        CurrentProperty = "SaveToFolder"
        If IsObject(SaveToFolder) Then
            If Not SaveToFolder Is Nothing Then
                .Save
                .Move SaveToFolder
            End If
        Else
            If SaveToFolder <> "" Then
                Set TestFolder = Me.GetFolderFromPath(CStr(SaveToFolder))
                If TestFolder Is Nothing Then Set TestFolder = Me.AddFolderFromPath(CStr(SaveToFolder))
                .Save
                .Move TestFolder
            End If
        End If
        
        ' Save/close or display the item in an Inspector
        
        If CloseRightAway Then
            CurrentProperty = "Close"
            .Close olSave
        Else
            CurrentProperty = "Display"
            .Display
        End If
    
    End With
    
    CreateNoteItem = True
    
    GoTo Cleanup
    
ErrHandler:
    
    ' Log error if applicable
    
    If Me.ErrorLoggingEnabled Then WriteToLog "NoteItem", Tag, CurrentProperty, Err.Description
    CreateNoteItem = False
    
Cleanup:
    
    Set olNote = Nothing
    
End Function

Function CreateTaskItem(Subject As String, Optional AssignTo As Variant = "", _
    Optional DueDate As Date = 0, Optional Body As String = "", _
    Optional Importance As OlImportance = olImportanceNormal, Optional ReminderSet As Boolean = True, _
    Optional ReminderTime As Date = 0, Optional Attachments As Variant, _
    Optional Categories As String = "", Optional Sensitivity As OlSensitivity = olNormal, _
    Optional Tag As String = "", Optional CloseRightAway As Boolean = True, _
    Optional OtherProperties As Variant = "", Optional SaveToFolder As Variant = "") As Boolean
    
    ' Method for creating tasks / sending task requests.  StartAt is required; all other arguments
    ' are optional.
    
    ' Due to the complexity involved, this class does not set recurrence patterns
    
    ' If the task is not assigned to someone, then it is simply a task.  If there are, then a task
    ' request is sent
    
    ' Returns True for successful completion, and False otherwise.  If unsuccessful, no message is sent,
    ' and if logging is enabled at the class level, the procedure writes details to the log file
    
    ' Most arguments correspond to TaskItem properties in Outlook, so look in the Outlook VBA
    ' help file for documentation on them.  The exceptions:
    '
    ' - AssignTo:       Can be either a 1-dimensional array of recipients, or a string.  If an
    '                   array, use each member of the array to list the recipients.  For multiple
    '                   recipients always use an array.  Passing a value here makes the TaskItem a
    '                   Task Request
    '
    ' - Attachments:    Can be either a 1-dimensional array of attachment paths, or a string.  If an
    '                   array, use each member of the array to list the file path.  If a string with
    '                   Len > 0, add the file given the path
    ' - Tag:            Allows passing a user-defined value that is not used in the MailItem, but may
    '                   be helpful for troubleshooting errors.  For example, you might pass in a row
    '                   number from Excel or a primary key value from Access
    ' - CloseRightAway: Determines whether to send the item (True), or open the item in an Inspector
    '
    ' - OtherProperties: Allows user to set other item properties not already handled in the basic
    '                    class.  If used, pass as a two-dimensional array, with the first dimension
    '                    indicating the property name, and the second indicating the value to use
    '
    ' SaveToFolder:      If you wish to save the new item to a folder other than its default folder, use
    '                    this argument.  Argument can be an object representing the Outlook folder, or it
    '                    can be a string.  If a string, it identifies the folder by path.  Use two backslash
    '                    characters to delimit nested folders.  Double backslash at the beginning for the
    '                    top level folder is optional.  For example, these both get the same folder:
    '                    -------
    '                    \\Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    Inbox - Joe Schmoe\\Customers\\Acme Corp
    '                    -------
    '                    If the folder does not already exist, the folder will be created (as will
    '                    any "missing" levels in the indicated path)
    
    ' At each step, CurrentProperty variable updates to reflect what we are doing.  This facilitates
    ' error logging
    
    Dim olTask As Object 'Outlook.TaskItem
    Dim olRecip As Object 'Outlook.Recipient
    Dim Counter As Long
    Dim CurrentProperty As String
    Dim ItemProp As Object 'Outlook.ItemProperty
    Dim TestFolder As Object 'Outlook.Folder
    
    On Error GoTo ErrHandler
    
    CurrentProperty = "CreateItem"
    Set olTask = olApp.CreateItem(olTaskItem)
    With olTask
        
        ' Standard fields
        
        CurrentProperty = "Subject"
        .Subject = Subject
        CurrentProperty = "DueDate"
        If DueDate >= 0 Then .DueDate = DueDate
        CurrentProperty = "Body"
        If Body <> "" Then .Body = Body
        CurrentProperty = "Importance"
        .Importance = Importance
        
        ' Set reminder, if applicable.  If ReminderSet = True but no ReminderTime is specified,
        ' then use the DueDate
        
        CurrentProperty = "ReminderSet"
        If ReminderSet Then
            .ReminderSet = True
            If ReminderTime > 0 Then
                .ReminderTime = ReminderTime
            Else
                .ReminderTime = .DueDate
            End If
        Else
            .ReminderSet = False
        End If
        
        ' Process Attachments.  For multiple files, use an array.  For a single file, use a string
        
        CurrentProperty = "Attachments"
        If IsArray(Attachments) Then
            For Counter = LBound(Attachments) To UBound(Attachments)
                .Attachments.Add Attachments(Counter)
            Next
        Else
            If Not IsMissing(Attachments) Then
                If Attachments <> "" Then .Attachments.Add Attachments
            End If
        End If
        
        ' Standard fields
        
        CurrentProperty = "Categories"
        If Categories <> "" Then .Categories = Categories
        CurrentProperty = "Sensitivity"
        .Sensitivity = Sensitivity
        
        CurrentProperty = "AssignTo"
        If IsArray(AssignTo) Then
            .Assign
            For Counter = LBound(AssignTo) To UBound(AssignTo)
                .Recipients.Add AssignTo(Counter)
            Next
        Else
            If AssignTo <> "" Then
                .Assign
                .Recipients.Add AssignTo(Counter)
            End If
        End If
        
        ' Process OtherProperties, if applicable.  If argument value is not an array, or is an array
        ' with just one dimension, this will throw an error
        
        CurrentProperty = "OtherProperties"
        If IsArray(OtherProperties) Then
            
            ' The code does not specify what the bounds have to be for the OtherProperties array, so
            ' to get the properties, use LBound/UBound to set the parameters for the loop.  Likewise,
            ' since we do not know ahead of time what the bounds will be for the second dimension
            ' (could be 0 To 1, could be 1 To 2), use LBound to get property name and UBound to get
            ' the property value.  Theoretically, the second dimension could have >= 3 elements, but
            ' the code will still work if the property name is in the first element and property
            ' value is in the second
            
            For Counter = LBound(OtherProperties, 1) To UBound(OtherProperties, 1)
                Set ItemProp = .ItemProperties.Item(OtherProperties(Counter, LBound(OtherProperties, 2)))
                ItemProp.Value = OtherProperties(Counter, UBound(OtherProperties, 2))
            Next
        End If
        
        ' Use this to save the item in a folder other than the default folder for its type.  If SaveToFolder
        ' is passed as an Outlook.Folder object, then use that.  If it is passed as a path string, then
        ' first try to get that folder, or if it does not exist, create it
        
        CurrentProperty = "SaveToFolder"
        If IsObject(SaveToFolder) Then
            If Not SaveToFolder Is Nothing Then
                .Save
                .Move SaveToFolder
            End If
        Else
            If SaveToFolder <> "" Then
                Set TestFolder = Me.GetFolderFromPath(CStr(SaveToFolder))
                If TestFolder Is Nothing Then Set TestFolder = Me.AddFolderFromPath(CStr(SaveToFolder))
                .Save
                .Move TestFolder
            End If
        End If
        
        ' If there are no recipients, then it is just a task, and the choice is Close/Display.
        ' If there are recipients, then it is a task request, and the choice is Send/Display.
        
        CurrentProperty = "Send/Display"
        If .Recipients.Count > 0 Then
            If CloseRightAway Then
                CurrentProperty = "Send"
                .Send
            Else
                CurrentProperty = "Display"
                .Display
            End If
        Else
            If CloseRightAway Then
                CurrentProperty = "Save"
                .Close olSave
            Else
                CurrentProperty = "Display"
                .Display
            End If
        End If
    End With
    
    CreateTaskItem = True
    
    GoTo Cleanup
    
ErrHandler:
    
    ' Log error if applicable
    
    If Me.ErrorLoggingEnabled Then WriteToLog "TaskRequestItem", Tag, CurrentProperty, Err.Description
    CreateTaskItem = False
    
Cleanup:
    
    Set olTask = Nothing
    
End Function

Property Get OutlookApplication() As Object 'Outlook.Application
    
    ' Exposes the Outlook.Application object
    
    Set OutlookApplication = olApp
    
End Property

Private Sub WriteToLog(ItemType As String, Tag As String, CurrentProperty As String, ErrDescr As String)
    
    ' Increment error count, and write record to log file
    
    TotalErrors = TotalErrors + 1
    
    tsLog.WriteLine Join(Array(Format(Now, "yyyy-mm-dd hh:nn:ss"), ItemType, Tag, CurrentProperty, Err.Description), ",")
    
End Sub

Open in new window


The Outlook CreateItem class has five principal methods, one each for creating appointments, contacts, email messages, notes, and tasks.  Each of those methods has its own section below outlining how to use it.

Following the descriptions of the “Create Item” methods, this article has additional sections outlining error handling and logging, and the other class members, which exist primarily to support the functioning of the “Create Item methods.


Creating Appointments and Meeting Requests



To create an appointment or a meeting request, use the class’s CreateAppointmentItem method.  If there are no attendees specified, then the method creates a simple appointment.  If attendees are specified, then the method creates a meeting request.

Note: Due to the great complexity involved, the CreateAppointmentItem method does not support recurrence patterns.

For example, the sample Excel file provided along with this article uses the following code to generate appointments:

Sub TestAppointment()
    
    ' Tests creation of apppointments as detailed on worksheet Appointment Test.  Where required/optional
    ' attendees are indicated, this will be treated as a meeting request
    
    Dim SendItems As clsOutlookCreateItem
    Dim LastR As Long
    Dim arr As Variant
    Dim Counter As Long
    
    ' Instantiate class, and turn on error logging
    
    Set SendItems = New clsOutlookCreateItem
    SendItems.LogErrors True, "", False
    
    ' Dump worksheet contents into array for processing
    
    With ThisWorkbook.Worksheets("Appointment Test")
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        arr = .Range(.[a2], .Cells(LastR, "g")).Value
    End With
    
    With SendItems
        
        ' Loop through contents, and create appointments/meeting requests for each row.  If there
        ' are to be multiple required/optional attendees, they are entered as a semicolon delimited
        ' list.  Test the required/optional attendee columns, and if they are populated, convert
        ' to arrays for the function call
        
        For Counter = 1 To UBound(arr, 1)
            .CreateAppointmentItem StartAt:=CDate(arr(Counter, 4)), _
                RequiredAttendees:=IIf(arr(Counter, 1) <> "", Split(arr(Counter, 1), ";"), arr(Counter, 1)), _
                OptionalAttendees:=IIf(arr(Counter, 2) <> "", Split(arr(Counter, 2), ";"), arr(Counter, 2)), _
                Duration:=CLng(arr(Counter, 5)), _
                Subject:=CStr(arr(Counter, 3)), _
                ReminderSet:=CBool(arr(Counter, 6)), _
                ReminderMinutesBeforeStart:=CLng(arr(Counter, 7)), _
                Tag:="Row " & (Counter + 1)
        Next
        
        ' Check to see if there are errors, and if so alert the user
        
        If .CountOfErrors Then
            MsgBox "There were " & .CountOfErrors & " errors.  Please review the log file:" & vbCrLf & _
                vbCrLf & .LogFilePath, vbCritical, "Errors"
        Else
            MsgBox "Complete without errors"
        End If
    End With
    
    Set SendItems = Nothing
    
End Sub

Open in new window


The CreateAppointmentItem method has one required argument, StartAt, which indicates the date and time the appointment/meeting begins.

The following optional arguments are analogous to AppointmentItem properties defined in the Outlook object model; for more information on them please see the VBA help file for Outlook:

AllDayEvent
Body
BusyStatus
Categories
Duration
EndAt (corresponding Outlook property: End)
Importance
Location
ReminderMinutesBeforeStart
ReminderSet
Sensitivity
Subject

The following optional arguments have analogous properties in the Outlook object model, but their class implementations require some additional explanation:

Attachments: Can either be a string that indicates the full path and name for a single file, or a one-dimensional array of full file paths and names
OptionalAttendees: Can either be a string indicating a single optional attendee, or a one-dimensional array of optional attendees
Organizer: Similar to OptionalAttendees
RequiredAttendees: Similar to OptionalAttendees
Resources: Similar to OptionalAttendees

The following optional arguments have no corresponding properties or methods in the Outlook object model:

CloseRightAway: Boolean; indicates whether to close/send the item right away (True), or open in a new Inspector (False).  True by default.  Note: During testing, if CloseRightAway is False, all attendees were created as “required”, even if they were added using the OptionalAttendees argument
OtherProperties: Provides access to other AppointmentItem properties not otherwise implemented in the class.  Pass as a two-dimensional array, with the first dimension specifying the property name, and the second dimension the property value
SaveToFolder: Allows you to specify the folder the new item should be saved to.  Omit to save to the item type’s default folder.  Pass either as an object (i.e., the Outlook folder the item should be saved to), or as a string indicating a folder path.  If you specify a folder path, use two backslash characters to delimit folder/subfolder.  If the folder indicated by the path does not already exist, the class will create that folder
Tag: Optional string that may be useful for tracking/troubleshooting purposes.  On a handled error, the class includes the item’s Tag value in the log file


Creating Contacts



To create a contact, use the class’s CreateContactItem method.

There is no example provided for this method in the sample files.  In most cases, it is probably easiest to use Outlook’s native import facility to create contact items based on data held in Access, Excel, or other data sources.

The CreateContactItem method has no required arguments.

The following optional arguments are analogous to ContactItem properties defined in the Outlook object model; for more information on them please see the VBA help file for Outlook:

AddPicture (actually a method in the Outlook object model)
BusinessAddress
BusinessAddressCity
BusinessAddressCountry
BusinessAddressPostalCode
BusinessAddressPostOfficeBox
BusinessAddressState
BusinessAddressStreet
BusinessFaxNumber
BusinessTelephoneNumber
Categories
CompanyName
Email1Address
Email1AddressType
Email1DisplayName
FileAs
FirstName
FullName
HomeAddress
HomeAddressCity
HomeAddressCountry
HomeAddressPostalCode
HomeAddressPostOfficeBox
HomeAddressState
HomeAddressStreet
HomeFaxNumber
HomeTelephoneNumber
Importance
Journal
LastName
MiddleName
MobileTelephoneNumber
Sensitivity
Suffix
Title

The following optional arguments have analogous properties in the Outlook object model, but their class implementations require some additional explanation:

Attachments: Can either be a string that indicates the full path and name for a single file, or a one-dimensional array of full file paths and names

The following optional arguments have no corresponding properties or methods in the Outlook object model:

CloseRightAway: Boolean; indicates whether to close/send the item right away (True), or open in a new Inspector (False).  True by default
OtherProperties: Provides access to other NoteItem properties not otherwise implemented in the class.  Pass as a two-dimensional array, with the first dimension specifying the property name, and the second dimension the property value
SaveToFolder: Allows you to specify the folder the new item should be saved to.  Omit to save to the item type’s default folder.  Pass either as an object (i.e., the Outlook folder the item should be saved to), or as a string indicating a folder path.  If you specify a folder path, use two backslash characters to delimit folder/subfolder.  If the folder indicated by the path does not already exist, the class will create that folder
Tag: Optional string that may be useful for tracking/troubleshooting purposes.  On a handled error, the class includes the item’s Tag value in the log file


Creating Emails



To create an email message, use the class’s CreateMailItem method.

For example, the sample Excel file provided along with this article uses the following code to generate email messages:


Sub TestEmail()
    
    ' Tests creation and sending of Outlook email messages
    
    Dim SendItems As clsOutlookCreateItem
    Dim LastR As Long
    Dim arr As Variant
    Dim Counter As Long
    Dim FolderPath As String
    
    ' Instantiate class, and turn on error logging
    
    Set SendItems = New clsOutlookCreateItem
    SendItems.LogErrors True, "", False
    
    ' Dump worksheet contents into array for processing
    
    With ThisWorkbook.Worksheets("Email Test")
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        arr = .Range(.[a2], .Cells(LastR, "e")).Value
    End With
    
    With SendItems
        
        ' Get the correct path for the folder to save the emails to.  The parent of the Inbox is
        ' a Mailbox; within that Mailbox add the new folder structure.  Use *two* backslahes as
        ' a path delimiter
        
        FolderPath = .GetDefaultFolder(olFolderInbox).Parent.Name & "\\NewFolder\\Testing\\1\\2\\3"
        
        ' Loop through contents, and create emails for each row.  If there are to be multiple
        ' recipients, they are entered as a semicolon delimited list.  Set up the emails to be
        ' delivered 60 minutes from now, and have replies sent to the listed addresses
        
        For Counter = 1 To UBound(arr, 1)
            .CreateMailItem SendTo:=arr(Counter, 1), _
                CC:=arr(Counter, 2), _
                BCC:=arr(Counter, 3), _
                Subject:=CStr(arr(Counter, 4)), _
                Sensitivity:=CLng(arr(Counter, 5)), _
                Tag:="Row #" & (Counter + 1), _
                DeferredDeliveryTime:=DateAdd("n", 60, Now), _
                ReplyRecipients:=Array("foo@blah.com", "hee@blah.com"), _
                SaveSentMessageFolder:=FolderPath
        Next
        
        ' Check to see if there are errors, and if so alert the user
        
        If .CountOfErrors Then
            MsgBox "There were " & .CountOfErrors & " errors.  Please review the log file:" & vbCrLf & _
                vbCrLf & .LogFilePath, vbCritical, "Errors"
        Else
            MsgBox "Complete without errors"
        End If
    End With
    
    Set SendItems = Nothing
    
End Sub

Open in new window



The CreateMailItem method has one required argument, SendTo.  SendTo can be either:

A string identifying a single “To” recipient;
A string identifying multiple “To” recipients, delimited by semicolon; or
An array of “To” recipients

The following optional arguments are analogous to MailItem properties defined in the Outlook object model; for more information on them please see the VBA help file for Outlook:

Body
Categories
DeferredDeliveryTime
DeleteAfterSubmit
FlagRequest (ignored if Outlook version is Outlook 2003 or earlier)
HTMLBody
Importance
ReadReceiptRequested
Sensitivity
Subject

The following optional arguments have analogous properties in the Outlook object model, but their class implementations require some additional explanation:

Attachments: Can either be a string that indicates the full path and name for a single file, or a one-dimensional array of full file paths and names
BCC: Similar to SendTo, may be a string with a single recipient, a semicolon-delimited list of recipients, or an array of recipients
CC: Similar to SendTo, may be a string with a single recipient, a semicolon-delimited list of recipients, or an array of recipients
ReplyRecipients: May be a string with a single recipient, or an array of recipients
SaveSentMessageFolder: Allows you to specify the folder the new item should be saved to.  Omit to save to the item type’s default folder.  Pass either as an object (i.e., the Outlook folder the item should be saved to), or as a string indicating a folder path.  If you specify a folder path, use two backslash characters to delimit folder/subfolder.  If the folder indicated by the path does not already exist, the class will create that folder

The following optional arguments have no corresponding properties or methods in the Outlook object model:

CloseRightAway: Boolean; indicates whether to close/send the item right away (True), or open in a new Inspector (False).  True by default
EnableForward: Boolean, indicates whether message can be forwarded (True by default).  Applies only within one’s own organization, and can be reversed by the recipient
EnableReply: Boolean, indicates whether recipient can reply to message (True by default).  Applies only within one’s own organization, and can be reversed by the recipient
EnableReplyAll: Boolean, indicates whether recipient can use “Reply All” to reply to the message (True by default).  Applies only within one’s own organization, and can be reversed by the recipient
OtherProperties: Provides access to other MailItem properties not otherwise implemented in the class.  Pass as a two-dimensional array, with the first dimension specifying the property name, and the second dimension the property value
Tag: Optional string that may be useful for tracking/troubleshooting purposes.  On a handled error, the class includes the item’s Tag value in the log file


Creating Notes



To create a note, use the class’s CreateNoteItem method.  (Due to the simplicity of this method, there is no example from the sample files.)

The CreateNoteItem method has one required argument, Body.

The following optional arguments are analogous to NoteItem properties defined in the Outlook object model; for more information on them please see the VBA help file for Outlook:

Categories

The following optional arguments have no corresponding properties or methods in the Outlook object model:

CloseRightAway: Boolean; indicates whether to close/send the item right away (True), or open in a new Inspector (False).  True by default
OtherProperties: Provides access to other NoteItem properties not otherwise implemented in the class.  Pass as a two-dimensional array, with the first dimension specifying the property name, and the second dimension the property value
SaveToFolder: Allows you to specify the folder the new item should be saved to.  Omit to save to the item type’s default folder.  Pass either as an object (i.e., the Outlook folder the item should be saved to), or as a string indicating a folder path.  If you specify a folder path, use two backslash characters to delimit folder/subfolder.  If the folder indicated by the path does not already exist, the class will create that folder
Tag: Optional string that may be useful for tracking/troubleshooting purposes.  On a handled error, the class includes the item’s Tag value in the log file


Creating Tasks and Task Requests



To create a task or a task request, use the class’s CreateTaskItem method.  If there are no recipients specified, then the method creates a simple task.  If recipients are specified, then the method creates a task request.

Note: Due to the great complexity involved, the CreateTaskItem method does not support recurrence patterns.

For example, the sample Excel file provided along with this article uses the following code to generate tasks:


Sub TestTask()
    
    ' Tests creation of tasks as detailed on worksheet Task Test.  Where an "AssignTo" is indicated,
    ' this will be treated as a task request
    
    Dim SendItems As clsOutlookCreateItem
    Dim LastR As Long
    Dim arr As Variant
    Dim Counter As Long
    
    ' Instantiate class and turn on error logging
    
    Set SendItems = New clsOutlookCreateItem
    SendItems.LogErrors True, "", False
    
    ' Dump worksheet contents into array for processing
    
    With ThisWorkbook.Worksheets("Task Test")
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        arr = .Range(.[a2], .Cells(LastR, "e")).Value
    End With
    
    With SendItems
        
        ' Loop through contents, and create tasks/task requests for each row.  If there are to be
        ' multiple "AssignTo" values, they are entered as a semicolon delimited; if they are
        ' populated, convert to arrays for the function call.  Also, if there are to be multiple
        ' attachments, they are entered as a pipe-delimited list.
        
        For Counter = 1 To UBound(arr, 1)
            .CreateTaskItem Subject:=CStr(arr(Counter, 4)), _
                AssignTo:=IIf(arr(Counter, 1) <> "", Split(arr(Counter, 1), ";"), ""), _
                DueDate:=CDate(arr(Counter, 2)), _
                Importance:=CLng(arr(Counter, 3)), _
                Tag:=CStr(arr(Counter, 4)), _
                Attachments:=IIf(arr(Counter, 5) <> "", Split(arr(Counter, 5), "|"), ""), _
                ReminderSet:=True
        Next
        
        ' Check to see if there are errors, and if so alert the user
        
        If .CountOfErrors Then
            MsgBox "There were " & .CountOfErrors & " errors.  Please review the log file:" & vbCrLf & _
                vbCrLf & .LogFilePath, vbCritical, "Errors"
        Else
            MsgBox "Complete without errors"
        End If
    End With
    
    Set SendItems = Nothing
    
End Sub

Open in new window



The CreateTaskItem method has one required argument, Subject.

The following optional arguments are analogous to TaskItem properties defined in the Outlook object model; for more information on them please see the VBA help file for Outlook:

Body
DueDate
Categories
Importance
ReminderSet
ReminderTime
Sensitivity

The following optional arguments have analogous properties in the Outlook object model, but their class implementations require some additional explanation:

Attachments: Can either be a string that indicates the full path and name for a single file, or a one-dimensional array of full file paths and names

The following optional arguments have no corresponding properties or methods in the Outlook object model:

AssignTo: Replaces the Recipients property.  If this is omitted, then the method creates a simple task.  If it is included, then the method creates a task request.  Can either be a string specifying a single recipients, or a one-dimensional array of recipients
CloseRightAway: Boolean; indicates whether to close/send the item right away (True), or open in a new Inspector (False).  True by default
OtherProperties: Provides access to other TaskItem properties not otherwise implemented in the class.  Pass as a two-dimensional array, with the first dimension specifying the property name, and the second dimension the property value
SaveToFolder: Allows you to specify the folder the new item should be saved to.  Omit to save to the item type’s default folder.  Pass either as an object (i.e., the Outlook folder the item should be saved to), or as a string indicating a folder path.  If you specify a folder path, use two backslash characters to delimit folder/subfolder.  If the folder indicated by the path does not already exist, the class will create that folder
Tag: Optional string that may be useful for tracking/troubleshooting purposes.  On a handled error, the class includes the item’s Tag value in the log file


Error Handling and Logging



The five main methods of the OutlookCreateItem class (CreateAppointmentItem, CreateContactItem, CreateMailItem, CreateNoteItem, and CreateTaskItem) all have error handling enabled that should catch nearly all runtime errors.  Should the invoked method encounter such a handled error:

The code will jump immediately to the error handler;
The indicated item will not be created;
The method will return False; and
If error logging is enabled, a description of the error will be written to the indicated log file.

For example, see the screenshot below:

Log File
I generated that log by running the macro TestTask from the attached sample Excel file (please see Sample Files below).  In this test run, none of the attachments I indicated should be included in the task actually exist on my computer.  Thus, for every row that included attachments, there was no task created, and the code wrote the details above to the log file.

The log file always includes the following data elements:
DateTime: Date and time the error occurred
ItemType: Indicates what kind of Outlook item the code was trying to create.  This will help you identify which method generated the error
Tag: The “Tag” is a user-definable string you may use as an aid in debugging.  (Each of the “Create Item” methods uses Tag as an optional argument.)  In the example above, I passed in the same value as the subject for the Tag, which would thus allow me to determine which row from my source data caused the error
Property: An internal indicator of which point within the method threw the error.  At various points in the code for the various “Create Item” methods, I update a variable, CurrentProperty, that maintains that state and makes it available for error logging
ErrDescr: The error description associated with the runtime error

To enable error logging in your class instance, use the LogErrors method.  LogErrors takes the following three arguments:

Enable: Optional Boolean.  Indicates whether error logging is enabled.  Defaults to True if omitted.  If error logging is enabled, calling LogErrors with False for Enable will disable logging and close the current log file
LogPath: Optional String.  Indicates the path and name for the log file to be used.  Defaults to “c:\clsOutlookCreateItem Log.txt” if omitted
Append: Optional Boolean.  Indicates whether, if the indicated log file already exists, to append to the end of that file (True or omitted), or always overwrite with a new file (False).  If the indicated file does not exist, then a new file is always created.

The following code snippet demonstrates how to use the LogErrors method:

Sub Foo()

    Dim MyClass As clsOutlookCreateItem

    Set MyClass = New clsOutlookCreateItem

    ‘ Enable logging with default values:
    MyClass.LogErrors

    ‘ Enable logging specifying file location, append:
    ‘MyClass.LogErrors True, “c:\My Log File.txt”, True

    ‘ Enable logging specifying file location, force new file:
    ‘MyClass.LogErrors True, “c:\My Log File.txt”, False

    ‘ Real code goes here

    ‘ Stop error logging
    MyClass.LogErrors False

    Set MyClass = Nothing

End Sub

Open in new window


Note: The terminate event for the OutlookCreateItem class automatically disables error logging.  Thus there is no need to explicitly disable error logging in your code, unless for some reason you want to stop logging without destroying the class instance.  For example, if you wanted to start logging in one log file, and then at some point in your code switch to another log file, you would have to stop error logging to close off the first log file, and then re-enable error logging for the second log file.


Using Other Properties, Methods, and Enumerations



In addition to the methods for creating new items and enabling error logging, the OutlookCreateItem class exposes many additional members.  This section describes the remaining class properties, methods, events, and enumerations.  As these class members are ancillary to the “Create Item” methods and are thoroughly documented in the source code, they will not receive thorough treatments here.

OutlookCreateItem Properties

The OutlookCreateItem class exposes the following properties:

CountOfErrors: Returns total number (Long) of handled errors for the current class instance.  Read-only.
ErrorLoggingEnabled: Returns Boolean True or False indicating whether error logging is in use.  Read-only.
LogFilePath: Returns the path and file name (String) of the current log file, if applicable
OutlookApplication: Returns a reference to the Outlook.Application object used by the class instance.  Read-only

OutlookCreateItem Methods

The OutlookCreateItem class exposes the following additional methods:

AddFolder: Creates a new Outlook folder, as a subfolder to the indicated parent folder.  Returns a reference to the newly-created folder
AddFolderFromPath: Creates a new Outlook folder, placed according to the indicated path.  Returns a reference to the newly-created folder
GetDefaultFolder: Returns reference to one of the current mailbox’s default folders (Inbox, Contacts, Calendar, etc.).  Equivalent to Outlook method of the same name
GetFolderFromPath: Returns reference to the Outlook folder indicated by the given path
GetParentFolder: Returns a reference to the parent folder of the indicated Outlook folder
GetSubFolder: Returns a reference to the indicated subfolder of the indicated Outlook folder
WriteToLog: A private method, available only from within the class, that writes a record to the current log file

Note that you can “chain” these methods to create complex expressions.  For example, to create a new subfolder under the default Tasks folder to contain the tasks to be created, you could use a code snippet such as:

Dim MyClass As clsOutlookCreateItem
Dim MyFolder As Object

Set MyClass = New clsOutlookCreateItem
With MyClass
    Set MyFolder = .AddFolder("New Tasks", .GetDefaultFolder(olFolderTasks), olFolderTasks)
    .CreateTaskItem Subject:=”Some Task”, DueDate:=Date + 7, _
        ReminderSet:=True, SaveToFolder:=MyFolder
End With

Set MyFolder = Nothing
Set MyClass = Nothing

Open in new window


OutlookCreateItem Events

Initialize: Creates an instance of the Outlook.Application object for use by calls to class methods
Terminate: Destroys reference to the Outlook.Application object, and disables error logging if it had been in use

OutlookCreateItem Enumerations

The OutlookCreateItem class includes several enumerations.  These enumerations exist to enable Intellisense despite the fact that the class is late-bound, and thus eliminate the need to memorize or look up the values of the various native Outlook enumerations that they mirror.

OlBusyStatus: Indicates how the duration of the appointment should be treated with regard to attendees’ free/busy status
OlDefaultFolders: Indicates the desired default folder
OlImportance: High, normal, or low importance
olInspectorClose: Close Inspector with or without saving the Inspector’s current item
OlItemType: Indicates the class type for an Outlook item (appointment, contact, mail, etc.)
OlMailRecipientType: To, CC, or BCC
OlMeetingRecipientType: Required, optional, organizer, or resource
OlMeetingStatus: Indicates whether an appoint is also a meeting
OlSensitivity: Normal, confidential, personal, or private


Adding the OutlookCreateItem Class to Your Projects



There are several ways that you can add the OutlookCreateItem class to your VBA projects.

If you simply need the code for a “one-off” implementation, then you can go to the VBA Editor, insert a new class module into your project, and then copy and paste the source code here into that class module.

If you find that you are reusing this class with some frequency, then a simpler way may be to save a copy of the code someplace easily accessible to you, and then import it into each project as needed:

Copy the source code from this article to a text editor, and then save the resulting file with a .cls extension
Whenever you want to add the class to a VBA project, go to the VBA Editor, select your project, and use the Import File function to import the class from the *.cls file

Finally, if you are using the surpassingly wonderful MZ Tools add-in for the VBA Editor, then you can save the class source code as a template:

From the VBA Editor, select the MZ Tools Options from the menu or from the MZ Tools toolbar
Select Code Templates on the Options form.  Create a new template, using the source code from this article
To add the class to a project, insert a new class module, and then use the MZ Tools Add Code Template function to paste in the source code
Rename the class module to give the class instance in your project a meaningful name (I suggest clsOutlookCreateItem)


Managing Outlook Security



To prevent malicious code from exploiting Outlook, Microsoft added new security features to Outlook 2000, beginning with Service Pack 2.  In that release of Office 2000, and continuing with Office 2002 (XP) and Office 2003, if another program tries to automate Outlook to send email messages and/or gain access to Contacts, the action is blocked unless the user allows it:

Outlook Security Warning
Having to respond to this form significantly impairs one’s ability to automate Outlook functions, including the automation I am seeking to create with the OutlookCreateItem class.  To mitigate the impact in Outlook 2000 – Outlook 2003 I recommend three products:

Advanced Outlook Security, a free COM add-in developed by MAPILab.  It will preempt the security warnings
Express Click-Yes, an application that runs in the background and intercepts the warning message (there is a free version, and a “professional” version with additional features at modest cost)
Outlook Redemption, a DLL that provides full access to the Outlook object model while preempting the warning messages.  Developers can download it for free, but you must pay a modest license fee to be able to distribute it with your applications.  (The OutlookCreateItem class as written does not support Redemption; I mention Redemption merely to note that it may be useful in other contexts.)

In Outlook 2007, Microsoft modified this security setting by allowing the end user the ability to control “programmatic access settings.  To do so:

Select Tools / Trust Center from the menu
Choose the appropriate setting from the Programmatic Access group:

Trust Center
Using “Always warn me…” will emulate the behavior of Outlook 2000 – Outlook 2003.  If you use Microsoft’s recommended setting, then no warning appears as long as you appear to have a functioning and up-to-date antivirus program running.

This same functionality is available in Outlook 2010.  To access the Trust Center in Outlook 2010:

Click the File menu button
Select Options
In the Trust Center, choose the appropriate setting from the Programmatic Access group


Sample Files



To illustrate the capabilities of the OutlookCreateItem class, I have included two sample files, one for Excel, and one for Access:

Outlook-CreateItem-class.xls
OutlookCreateItem-class.mdb

To run the sample code in Excel, please do the following:

Save the sample Excel workbook to your computer
Open the file
Hit Ctrl+F8 to access the Macro dialog
Run the TestAppointment, TestEmail, or TestTask macros as desired
Each procedure will create new Outlook items using parameters supplied in the corresponding worksheets from the workbook

To run the sample code in Access, please do the following:

Save the sample Access database to your computer
Open the file
On the default form, click the Test Appointment, Test Email, or Test Task buttons as desired
Each procedure will create new Outlook items using parameters supplied in the corresponding queries from the database
To see the query data, click on the Appointment Data, Email Data, or Task Data buttons


Acknowledgements



This was a very long article, with a lot of code and examples to sort through, and as such I want to recognize the efforts of the Page Editors who helped me with this: BlueDevilFan, mark_wills, and Qlemo.  I greatly appreciated their efforts, and the several suggestions they made significantly improved aspects of the code.  Thanks!


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
17
Comment
  • 4
  • 3
  • 2
  • +2
11 Comments

Expert Comment

by:mun_84
Hi PAtrick,

Isit possible to create new public folders using your code?

Regards,
Hussain
0
LVL 93

Author Comment

by:Patrick Matthews
Hussain,

>>Is it possible to create new public folders using your code?

I'm not sure--haven't tried it yet.  I do not have an Outlook/Exchange environment where I have permission to create new public folders.

:)

Patrick
0
LVL 76

Expert Comment

by:David Lee
Hussain,

Assuming that you have permission to add a folder, then "yes" the AddFolder and/or AddFolderFromPath functions should be able to add a folder.  
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Expert Comment

by:mun_84
Hi,

I tried but i get error on the line ***. I'm not a VBA experts but can you help me sort this out.


Private Sub CreatePublicFolder_Click()
Dim MyClass As clsOutlookAutomation
Dim MyFolder As Object

Set MyClass = New clsOutlookAutomation
With MyClass
***    Set MyFolder = .AddFolderFromPath("\\Public Folders\\All Public Fodlers\\2011\\New Folder")
   
 
End With

Set MyFolder = Nothing
Set MyClass = Nothing

End Sub
0
LVL 76

Expert Comment

by:David Lee
Assuming that you copied and pasted, then you have a typo in the folder name.  "All Public Fodlers" should be "All Public Folders".  If that's not the problem, then please tell us what the error is.
0

Expert Comment

by:mun_84
Tried that tried the code below all doesnt work.

Set MyFolder = .AddFolder("New Folder", .GetDefaultFolder(olPublicFoldersAllPublicFolders), olPublicFoldersAllPublicFolders)

Do you have a working solution?
0
LVL 76

Expert Comment

by:David Lee
Sure.  This creates a mail folder.  
Sub Test()
    Dim SendItems As New clsOutlookCreateItem, olkFld As Object
    Set olkFld = SendItems.AddFolderFromPath("\\Public Folders\\All Public Folders\\2011\\New Folder", olFolderInbox)
    Set olkFld = Nothing
End Sub

Open in new window

0
LVL 93

Author Comment

by:Patrick Matthews
BDF,

Thanks so much for posting that!  I wish I could test it myself, but I am not authorized to create new public folders in my employer's environment :)

Patrick
0
LVL 76

Expert Comment

by:David Lee
You're welcome, Patrick.
0
LVL 13

Expert Comment

by:Chris Raisin
Great article, Patrick!

Cheers
Chris
0
LVL 42

Expert Comment

by:dlmille
Patrick,

Great article.  I'm looking forward to using this new class at my next opportunity!

I voted YES

Dave
0

Featured Post

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Join & Write a Comment

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Learn how to collaborate with office 365 Office Online

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month