Automate Outlook in VBA with the OutlookCreateItem Class
Published:
Browse All Articles > Automate Outlook in VBA with the OutlookCreateItem Class
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!
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.
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
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 SubThe 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 SubThe 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 SubThe 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 SubThe 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:
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 SubNote: 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 = NothingOutlookCreateItem 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:
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:
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!
=-=-=-=-=-=-=-=-=-=-=-=-=-
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (11)
Commented:
Open in new window
Author
Commented: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
Commented:
Commented:
Cheers
Chris
Commented:
Great article. I'm looking forward to using this new class at my next opportunity!
I voted YES
Dave
View More