If you ever needed to change a property of an item in Outlook from Excel, the following VBA code should assist. By specifying the type of item you are looking for, the Subject of the item, the property you want to update, and the new value of that property, you can update nearly any Outlook item's writable property.
For example, if you wanted to update the start time of a meeting, the body of a task, and the fax number of a contact, you can do all of these from this one subroutine. It's a fully encapsulated routine that should be placed in its own standard module in Excel, where it can be called as needed. It can also be used as a UDF.
The code works by passing in arguments detailing which item you want to update, the property to be updated, and the new value of that property. It really just blasts brute-force like through each writable property to see which one you passed in; In the future I might try rewriting this as a class to see if the code can be made shorter.
- itemType = A string literal indicating the type of item you want to update; should be either "Mail", "Appointment",
"Meeting", "Contact", "Deleted" (for Deleted Items Folder), "SentMail" (for Sent Items Folder), "Journal", "Note", "Draft" or "Task" (I know
there are others, but these are the most common). Case is insensitive, as the code assumes all uppercase when making comparisons. - strSubject = A string literal containing the subject line of the item you want to update; all of the above Outlook items
have a Subject Property which should uniquely identify each item. As I wrote in
Update Outlook Task Reminder
Dates from Excel, if you use the same Subject line for multiple items, you may not be able to use this code, or may need to adapt it
to filter on a different property. - propertyToBeUpdated = A string literal indicating the name of the property to be updated for the specified item.
The string should be the actual name of the property, for example, of you wanted to update the Subject line of a given task, the
propertyToBeUpdated value should be "Subject". - newVal = A string literal containing the new value of the property being updated.
Dim bWeStartedOutlook As Boolean
Function ChangeOLProperty(itemType As String, strSubject As String, propertyToBeUpdated As String, newVal As String) As Boolean
Dim olApp As Object
Dim olNS As Object
Dim olItems As Object
Dim olItemToChange As Object
Dim FolderType As Integer
On Error Resume Next
Set olApp = GetOutlookApp
On Error GoTo 0
If Not olApp Is Nothing Then
' figure out what type of item to update, and find it
Select Case UCase$(itemType)
Case "DELETED"
FolderType = 3 'olFolderDeletedItems
Case "SENTMAIL"
FolderType = 5 'olFolderSentMail
Case "MAIL"
FolderType = 6 'olFolderInbox
Case "APPOINTMENT", "MEETING"
FolderType = 9 'olFolderCalendar
Case "CONTACT"
FolderType = 10 'olFolderContacts
Case "JOURNAL"
FolderType = 11 'olFolderJournal
Case "NOTE"
FolderType = 12 'olFolderNotes
Case "TASK"
FolderType = 13 'olFolderTasks
Case "DRAFT"
FolderType = 16 'olFolderDrafts
End Select
Set olNS = olApp.GetNamespace("MAPI")
Set olItems = olNS.GetDefaultFolder(FolderType).Items
Set olItemToChange = olItems.Find("[Subject] = " & strSubject)
'or use default Subject Property: Set olItemToChange = olItems.Item(strSubject)
If olItemToChange Is Nothing Then
ChangeOLProperty = False
GoTo ExitProc
End If
' figure out which read/write properties are being changed, and change them accordingly
On Error GoTo ErrorHandler
Select Case FolderType
Case 3, 5, 6, 16 ' mailitems in each of these folders
If olItemToChange.Class = 43 Then 'olMail
Select Case UCase$(propertyToBeUpdated)
Case "ALTERNATERECIPIENTALLOWED"
olItemToChange.AlternateRecipientAllowed = CBool(newVal)
Case "AUTOFORWARDED"
olItemToChange.AutoForwarded = CBool(newVal)
Case "BCC"
olItemToChange.BCC = newVal
Case "BILLINGINFORMATION"
olItemToChange.BillingInformation = newVal
Case "BODY"
olItemToChange.body = newVal
Case "CATEGORIES"
olItemToChange.Categories = newVal
Case "CC"
olItemToChange.CC = newVal
Case "COMPANIES"
olItemToChange.Companies = newVal
Case "DEFERREDDELIVERYTIME"
olItemToChange.DeferredDeliveryTime = CDate(newVal)
Case "DELETEAFTERSUBMIT"
olItemToChange.DeleteAfterSubmit = CBool(newVal)
Case "EXPIRYTIME"
olItemToChange.ExpiryTime = CBool(newVal)
Case "FLAGDUEBY"
olItemToChange.FlagDueBy = CDate(newVal)
Case "FLAGREQUEST"
olItemToChange.FlagRequest = newVal
Case "FLAGSTATUS"
olItemToChange.FlagStatus = newVal
Case "HTMLBODY"
olItemToChange.HTMLBody = newVal
Case "IMPORTANCE"
olItemToChange.Importance = newVal
Case "MILEAGE"
olItemToChange.Mileage = newVal
Case "NOAGING"
olItemToChange.NoAging = newVal
Case "ORIGINATORDELIVERYREPORTREQUESTED"
olItemToChange.OriginatorDeliveryReportRequested = CBool(newVal)
Case "READRECEIPTREQUESTED"
olItemToChange.ReadReceiptRequested = CBool(newVal)
Case "RECIPIENTREASSIGNMENTPROHIBITED"
olItemToChange.RecipientReassignmentProhibited = CBool(newVal)
Case "REMINDEROVERRIDEDEFAULT"
olItemToChange.ReminderOverrideDefault = CBool(newVal)
Case "REMINDERPLAYSOUND"
olItemToChange.ReminderPlaySound = CBool(newVal)
Case "REMINDERSET"
olItemToChange.ReminderSet = CBool(newVal)
Case "REMINDERSOUNDFILE"
olItemToChange.ReminderSoundFile = newVal
Case "REMINDERTIME"
olItemToChange.ReminderTime = CDate(newVal)
Case "REMOTESTATUS"
olItemToChange.RemoteStatus = newVal
Case "SAVESENTMESSAGEFOLDER"
olItemToChange.SaveSentMessageFolder = newVal
Case "SENSITIVITY"
olItemToChange.Sensitivity = newVal
Case "SENTONBEHALFOFNAME"
olItemToChange.SentOnBehalfOfName = newVal
Case "SUBJECT"
olItemToChange.subject = newVal
Case "TO"
olItemToChange.To = newVal
Case "UNREAD"
olItemToChange.UnRead = CBool(newVal)
Case "VOTINGOPTIONS"
olItemToChange.VotingOptions = newVal
Case "VOTINGRESPONSE"
olItemToChange.VotingResponse = newVal
End Select
Else
ChangeOLProperty = False
GoTo ExitProc
End If
Case 9 ' meeting or appointment
If (olItemToChange.Class = 26) Or ((olItemToChange.Class > 52) And (olItemToChange.Class < 58)) Then
'AppointmentItem or MeetingItem Object
Select Case UCase$(propertyToBeUpdated)
Case "ALLDAYEVENT"
olItemToChange.AllDayEvent = newVal
Case "AUTOFORWARDED"
olItemToChange.AutoForwarded = CBool(newVal)
Case "BILLINGINFORMATION"
olItemToChange.BillingInformation = newVal
Case "BODY"
olItemToChange.body = newVal
Case "BUSYSTATUS"
olItemToChange.BusyStatus = newVal
Case "CATEGORIES"
olItemToChange.Categories = newVal
Case "COMPANIES"
olItemToChange.Companies = newVal
Case "DEFERREDDELIVERYTIME"
olItemToChange.DeferredDeliveryTime = CDate(newVal)
Case "DELETEAFTERSUBMIT"
olItemToChange.DeleteAfterSubmit = CBool(newVal)
Case "DURATION"
olItemToChange.Duration = newVal
Case "END"
olItemToChange.End = newVal
Case "EXPIRYTIME"
olItemToChange.ExpiryTime = CDate(newVal)
Case "FLAGDUEBY"
olItemToChange.FlagDueBy = CDate(newVal)
Case "FLAGREQUEST"
olItemToChange.FlagRequest = newVal
Case "FLAGSTATUS"
olItemToChange.FlagStatus = newVal
Case "IMPORTANCE"
olItemToChange.Importance = newVal
Case "ISONLINEMEETING"
olItemToChange.IsOnlineMeeting = CBool(newVal)
Case "LOCATION"
olItemToChange.Location = newVal
Case "MEETINGSTATUS"
olItemToChange.MeetingStatus = newVal
Case "MILEAGE"
olItemToChange.Mileage = newVal
Case "NETMEETINGAUTOSTART"
olItemToChange.NetMeetingAutoStart = newVal
Case "NETMEETINGDOCPATHNAME"
olItemToChange.NetMeetingDocPathName = newVal
Case "NETMEETINGORGANIZERALIAS"
olItemToChange.NetMeetingOrganizerAlias = newVal
Case "NETMEETINGSERVER"
olItemToChange.NetMeetingServer = newVal
Case "NETMEETINGTYPE"
olItemToChange.NetMeetingType = newVal
Case "NETSHOWURL"
olItemToChange.NetShowURL = newVal
Case "NOAGING"
olItemToChange.NoAging = CBool(newVal)
Case "ORIGINATORDELIVERYREPORTREQUESTED"
olItemToChange.OriginatorDeliveryReportRequested = CBool(newVal)
Case "REMINDERMINUTESBEFORESTART"
olItemToChange.ReminderMinutesBeforeStart = CLng(newVal)
Case "REMINDEROVERRIDEDEFAULT"
olItemToChange.ReminderOverrideDefault = CBool(newVal)
Case "REMINDERPLAYSOUND"
olItemToChange.ReminderPlaySound = CBool(newVal)
Case "REMINDERSET"
olItemToChange.ReminderSet = CBool(newVal)
Case "REMINDERSOUNDFILE"
olItemToChange.ReminderSoundFile = newVal
Case "REMINDERTIME"
olItemToChange.ReminderTime = CDate(newVal)
Case "REPLYTIME"
olItemToChange.ReplyTime = CDate(newVal)
Case "REQUIREDATTENDEES"
olItemToChange.RequiredAttendees = newVal
Case "RESOURCES"
olItemToChange.Resources = newVal
Case "REPONSEREQUESTED"
olItemToChange.ResponseRequested = CBool(newVal)
Case "SAVESENTMESSAGEFOLDER"
olItemToChange.SaveSentMessageFolder = newVal
Case "SENSITIVITY"
olItemToChange.Sensitivity = newVal
Case "START"
olItemToChange.Start = newVal
Case "SUBJECT"
olItemToChange.subject = newVal
Case "UNREAD"
olItemToChange.UnRead = CBool(newVal)
End Select
Else
ChangeOLProperty = False
GoTo ExitProc
End If
Case 10 ' contact
If olItemToChange.Class = 40 Then 'olContact
Select Case UCase$(propertyToBeUpdated)
Case "ACCOUNT"
olItemToChange.Account = newVal
Case "ANNIVERSARY"
olItemToChange.Anniversary = newVal
Case "ASSISTANTNAME"
olItemToChange.AssistantName = newVal
Case "ASSISTANTTELEPHONENUMBER"
olItemToChange.AssistantTelephoneNumber = newVal
Case "BILLINGINFORMATION"
olItemToChange.BillingInformation = newVal
Case "BIRTHDAY"
olItemToChange.Birthday = CDate(newVal)
Case "BODY"
olItemToChange.body = newVal
Case "BUSINESS2TELEPHONENUMBER"
olItemToChange.Business2TelephoneNumber = newVal
Case "BUSINESSADDRESS"
olItemToChange.BusinessAddress = newVal
Case "BUSINESSADDRESSCITY"
olItemToChange.BusinessAddressCity = newVal
Case "BUSINESSADDRESSCOUNTRY"
olItemToChange.BusinessAddressCountry = newVal
Case "BUSINESSADDRESSPOSTALCODE"
olItemToChange.BusinessAddressPostalCode = newVal
Case "BUSINESSADDRESSPOSTOFFICEBOX"
olItemToChange.BusinessAddressPostOfficeBox = newVal
Case "BUSINESSADDRESSSTATE"
olItemToChange.BusinessAddressState = newVal
Case "BUSINESSADDRESSSTREET"
olItemToChange.BusinessAddressStreet = newVal
Case "BUSINESSFAXNUMBER"
olItemToChange.BusinessFaxNumber = newVal
Case "BUSINESSHOMEPAGE"
olItemToChange.BusinessHomePage = newVal
Case "BUSINESSTELEPHONENUMBER"
olItemToChange.BusinessTelephoneNumber = newVal
Case "CALLBACKTELEPHONENUMBER"
olItemToChange.CallbackTelephoneNumber = newVal
Case "CARTELEPHONENUMBER"
olItemToChange.CarTelephoneNumber = newVal
Case "CATEGORIES"
olItemToChange.Categories = newVal
Case "CHILDREN"
olItemToChange.Children = newVal
Case "COMPANIES"
olItemToChange.Companies = newVal
Case "COMPANYMAINTELEPHONENUMBER"
olItemToChange.CompanyMainTelephoneNumber = newVal
Case "COMPANYNAME"
olItemToChange.CompanyName = newVal
Case "COMPUTERNETWORKNAME"
olItemToChange.ComputerNetworkName = newVal
Case "CUSTOMERID"
olItemToChange.CustomerID = newVal
Case "DEPARTMENT"
olItemToChange.Department = newVal
Case "EMAIL1ADDRESS"
olItemToChange.Email1Address = newVal
Case "EMAIL1ADDRESSTYPE"
olItemToChange.Email1AddressType = newVal
Case "EMAIL2ADDRESS"
olItemToChange.Email2Address = newVal
Case "EMAIL2ADDRESSTYPE"
olItemToChange.Email2AddressType = newVal
Case "EMAIL3ADDRESS"
olItemToChange.Email3Address = newVal
Case "EMAIL3ADDRESSTYPE"
olItemToChange.Email3AddressType = newVal
Case "FILEAS"
olItemToChange.FileAs = newVal
Case "FIRSTNAME"
olItemToChange.FirstName = newVal
Case "FTPSITE"
olItemToChange.FTPSite = newVal
Case "FULLNAME"
olItemToChange.FullName = newVal
Case "GENDER"
olItemToChange.Gender = newVal
Case "GOVERNMENTIDNUMBER"
olItemToChange.GovernmentIDNumber = newVal
Case "HOBBY"
olItemToChange.Hobby = newVal
Case "HOME2TELEPHONENUMBER"
olItemToChange.Home2TelephoneNumber = newVal
Case "HOMEADDRESS"
olItemToChange.HomeAddress = newVal
Case "HOMEADDRESSCITY"
olItemToChange.HomeAddressCity = newVal
Case "HOMEADDRESSCOUNTRY"
olItemToChange.HomeAddressCountry = newVal
Case "HOMEADDRESSPOSTALCODE"
olItemToChange.HomeAddressPostalCode = newVal
Case "HOMEADDRESSPOSTOFFICEBOX"
olItemToChange.HomeAddressPostOfficeBox = newVal
Case "HOMEADDRESSSTATE"
olItemToChange.HomeAddressState = newVal
Case "HOMEADDRESSSTREET"
olItemToChange.HomeAddressStreet = newVal
Case "HOMEFAXNUMBER"
olItemToChange.HomeFaxNumber = newVal
Case "HOMETELEPHONENUMBER"
olItemToChange.HomeTelephoneNumber = newVal
Case "IMPORTANCE"
olItemToChange.Importance = newVal
Case "INTERNETFREEBUSYADDRESS"
olItemToChange.InternetFreeBusyAddress = newVal
Case "ISDNNUMBER"
olItemToChange.ISDNNumber = newVal
Case "JOBTITLE"
olItemToChange.JobTitle = newVal
Case "JOURNAL"
olItemToChange.Journal = CBool(newVal)
Case "LANGUAGE"
olItemToChange.Language = newVal
Case "LASTNAME"
olItemToChange.LastName = newVal
Case "MAILINGADDRESS"
olItemToChange.MailingAddress = newVal
Case "MAILINGADDRESSCITY"
olItemToChange.MailingAddressCity = newVal
Case "MAILINGADDRESSCOUNTRY"
olItemToChange.MailingAddressCountry = newVal
Case "MAILINGADDRESSPOSTALCODE"
olItemToChange.MailingAddressPostalCode = newVal
Case "MAILINGADDRESSPOSTOFFICEBOX"
olItemToChange.MailingAddressPostOfficeBox = newVal
Case "MAILINGADDRESSSTATE"
olItemToChange.MailingAddressState = newVal
Case "MAILINGADDRESSSTREET"
olItemToChange.MailingAddressStreet = newVal
Case "MANAGERNAME"
olItemToChange.ManagerName = newVal
Case "MIDDLENAME"
olItemToChange.MiddleName = newVal
Case "MILEAGE"
olItemToChange.Mileage = newVal
Case "MOBILETELEPHONENUMBER"
olItemToChange.MobileTelephoneNumber = newVal
Case "NETMEETINGALIAS"
olItemToChange.NetMeetingAlias = newVal
Case "NETMEETINGSERVER"
olItemToChange.NetMeetingServer = newVal
Case "NICKNAME"
olItemToChange.NickName = newVal
Case "NOAGING"
olItemToChange.NoAging = CBool(newVal)
Case "OFFICELOCATION"
olItemToChange.OfficeLocation = newVal
Case "ORGANIZATIONALIDNUMBER"
olItemToChange.OrganizationalIDNumber = newVal
Case "OTHERADDRESS"
olItemToChange.OtherAddress = newVal
Case "OTHERADDRESSCITY"
olItemToChange.OtherAddressCity = newVal
Case "OTHERADDRESSCOUNTRY"
olItemToChange.OtherAddressCountry = newVal
Case "OTHERADDRESSPOSTALCODE"
olItemToChange.OtherAddressPostalCode = newVal
Case "OTHERADDRESSPOSTOFFICEBOX"
olItemToChange.OtherAddressPostOfficeBox = newVal
Case "OTHERADDRESSSTATE"
olItemToChange.OtherAddressState = newVal
Case "OTHERADDRESSSTREET"
olItemToChange.OtherAddressStreet = newVal
Case "OTHERFAXNUMBER"
olItemToChange.OtherFaxNumber = newVal
Case "OTHERTELEPHONENUMBER"
olItemToChange.OtherTelephoneNumber = newVal
Case "PAGERNUMBER"
olItemToChange.PagerNumber = newVal
Case "PERSONALHOMEPAGE"
olItemToChange.PersonalHomePage = newVal
Case "PRIMARYTELEPHONENUMBER"
olItemToChange.PrimaryTelephoneNumber = newVal
Case "PROFESSION"
olItemToChange.Profession = newVal
Case "RADIOTELEPHONENUMBER"
olItemToChange.RadioTelephoneNumber = newVal
Case "REFERREDBY"
olItemToChange.ReferredBy = newVal
Case "SELECTEDMAILINGADDRESS"
olItemToChange.SelectedMailingAddress = newVal
Case "SENSITIVITY"
olItemToChange.Sensitivity = newVal
Case "SPOUSE"
olItemToChange.Spouse = newVal
Case "SUBJECT"
olItemToChange.subject = newVal
Case "SUFFIX"
olItemToChange.Suffix = newVal
Case "TELEXNUMBER"
olItemToChange.TelexNumber = newVal
Case "TITLE"
olItemToChange.Title = newVal
Case "TTYTDDTELEPHONENUMBER"
olItemToChange.TTYTDDTelephoneNumber = newVal
Case "UNREAD"
olItemToChange.UnRead = CBool(newVal)
Case "USER1"
olItemToChange.User1 = newVal
Case "USER2"
olItemToChange.User2 = newVal
Case "USER3"
olItemToChange.User3 = newVal
Case "USER4"
olItemToChange.User4 = newVal
Case "USERCERTIFICATE"
olItemToChange.UserCertificate = newVal
Case "WEBPAGE"
olItemToChange.WebPage = newVal
Case "YOMICOMPANYNAME"
olItemToChange.YomiCompanyName = newVal
Case "YOMIFIRSTNAME"
olItemToChange.YomiFirstName = newVal
Case "YOMILASTNAME"
olItemToChange.YomiLastName = newVal
End Select
Else
ChangeOLProperty = False
GoTo ExitProc
End If
Case 13 ' task
If olItemToChange.Class = 48 Then 'olTask
Select Case UCase$(propertyToBeUpdated)
Case "ACTUALWORK"
olItemToChange.ActualWork = CLng(newVal)
Case "BILLINGINFORMATION"
olItemToChange.BillingInformation = newVal
Case "BODY"
olItemToChange.body = newVal
Case "CARDDATA"
olItemToChange.CardData = newVal
Case "CATEGORIES"
olItemToChange.Categories = newVal
Case "COMPANIES"
olItemToChange.Companies = newVal
Case "COMPLETE"
olItemToChange.complete = CBool(newVal)
Case "DATECOMPLETED"
olItemToChange.DateCompleted = CDate(newVal)
Case "DUEDATE"
olItemToChange.DueDate = CDate(newVal)
Case "IMPORTANCE"
olItemToChange.Importance = newVal
Case "MILEAGE"
olItemToChange.Mileage = newVal
Case "NOAGING"
olItemToChange.NoAging = CBool(newVal)
Case "ORDINAL"
olItemToChange.Ordinal = CBool(newVal)
Case "OWNER"
olItemToChange.Owner = newVal
Case "PERCENTCOMPLETE"
olItemToChange.PercentComplete = newVal
Case "REMINDEROVERRIDEDEFAULT"
olItemToChange.ReminderOverrideDefault = CBool(newVal)
Case "REMINDERPLAYSOUND"
olItemToChange.ReminderPlaySound = CBool(newVal)
Case "REMINDERSET"
olItemToChange.ReminderSet = CBool(newVal)
Case "REMINDERSOUNDFILE"
olItemToChange.ReminderSoundFile = newVal
Case "REMINDERTIME"
olItemToChange.ReminderTime = CDate(newVal)
Case "ROLE"
olItemToChange.Role = newVal
Case "SCHEDULEPLUSPRIORITY"
olItemToChange.SchedulePlusPriority = newVal
Case "SENSITIVITY"
olItemToChange.Sensitivity = newVal
Case "STARTDATE"
olItemToChange.StartDate = CDate(newVal)
Case "STATUS"
olItemToChange.Status = newVal
Case "SUBJECT"
olItemToChange.subject = newVal
Case "TEAMTASK"
olItemToChange.TeamTask = CBool(newVal)
Case "TOTALWORK"
olItemToChange.TotalWork = CLng(newVal)
Case "UNREAD"
olItemToChange.UnRead = CBool(newVal)
End Select
Else
ChangeOLProperty = False
GoTo ExitProc
End If
Case 11 ' journal
If olItemToChange.Class = 42 Then 'olJournal
Select Case UCase$(propertyToBeUpdated)
Case "BILLINGINFORMATION"
olItemToChange.BillingInformation = newVal
Case "BODY"
olItemToChange.body = newVal
Case "CATEGORIES"
olItemToChange.Categories = newVal
Case "COMPANIES"
olItemToChange.Companies = newVal
Case "DURATION"
olItemToChange.Duration = newVal
Case "END"
olItemToChange.End = newVal
Case "IMPORTANCE"
olItemToChange.Importance = newVal
Case "MILEAGE"
olItemToChange.Mileage = newVal
Case "NOAGING"
olItemToChange.NoAging = CBool(newVal)
Case "SENSITIVITY"
olItemToChange.Sensitivity = newVal
Case "START"
olItemToChange.Start = newVal
Case "SUBJECT"
olItemToChange.subject = newVal
Case "TYPE"
olItemToChange.Type = newVal
Case "UNREAD"
olItemToChange.UnRead = CBool(newVal)
End Select
Else
ChangeOLProperty = False
GoTo ExitProc
End If
Case 12 ' notes
If olItemToChange.Class = 44 Then 'olNote
Select Case UCase$(propertyToBeUpdated)
Case "BODY"
olItemToChange.body = newVal
Case "CATEGORIES"
olItemToChange.Categories = newVal
Case "COLOR"
olItemToChange.Color = newVal
Case "HEIGHT"
olItemToChange.Height = newVal
Case "LEFT"
olItemToChange.Left = newVal
Case "TOP"
olItemToChange.Top = newVal
Case "width"
olItemToChange.Width = newVal
End Select
Else
ChangeOLProperty = False
GoTo ExitProc
End If
End Select
' if we got this far, assume success, save changes and exit
olItemToChange.Save
ChangeOLProperty = True
GoTo ExitProc
Else
ChangeOLProperty = False
GoTo ExitProc
End If
ErrorHandler:
ChangeOLProperty = False
ExitProc:
If bWeStartedOutlook Then
olApp.Quit
End If
Set olItemToChange = Nothing
Set olItems = Nothing
Set olNS = Nothing
Set olApp = Nothing
End Function
Function GetOutlookApp() As Object
On Error Resume Next
Set GetOutlookApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set GetOutlookApp = CreateObject("Outlook.Application")
bWeStartedOutlook = True
End If
On Error GoTo 0
End Function
For example, if I had an email in my Inbox with the subject of "test" and wanted to change the subject to "OK", here is the way I would call this sub:
Dim success As Boolean
success = ChangeOLProperty("Mail", "test", "Subject", "OK")
The above line of code indicates that I have a mail item in my Inbox (first argument) with the subject of "test" (second argument), and I want to change the Subject (third argument) to "OK" (fourth argument). The boolean variable success will be TRUE to indicate, well, success.
The property names (third argument) have to match exactly the programmatic names of each property. Check the Outlook VBA Help, or visit MSDN for a list of property names (or just scan the code).
For properties with Boolean values (i.e. UnRead, NoAging, etc), simply pass in "True" or "False" as the fourth argument. Properties that require dates (like ReminderTime) should be passed string literal dates in quotes, i.e. "1/1/2009" which is a string but otherwise a valid date. Both of these are converted to "real" values using CBool and CDate, respectively.
So now I can use this single sub to update nearly any writable property of the most popular types of items in Outlook. For example:
MsgBox ChangeOLProperty("Mail", "test", "Subject", "OK")
MsgBox ChangeOLProperty("Appointment", "Dentist", "Start", "1/25/2009")
MsgBox ChangeOLProperty("Contact", "John Smith", "BusinessTelephoneNumber", "212-555-1212")
MsgBox ChangeOLProperty("Task", "Important Stuff To Do", "Complete", "True")
In line 1, we change the subject of an email in the Inbox from "test" to "OK". Line 2 changes the appointment start date for the one labelled "Dentist" to January 25th of next year. Line 3 changes the business phone number of our contact named John Smith to 212-555-1212. Finally, line 4 changes the Complete status of our very important task to True, marking the task as completed.
There is very little validation performed on each assignment; if an error occurs, the code will simply error out and return FALSE to the calling sub. I deliberately left the writable MessageClass Property out of the code, it seemed useless to me. Also note that the code only works on the default folders: Inbox, Contacts, Tasks, and so on, and only on the default items for those folders. So if you have a Post Item in your Inbox with the same Subject, the code will return FALSE.
Items in subfolders or other non-default folders will not be found. Furthermore, the code may trigger the OMG (Object Model Guard) in Outlook for any programmatically restricted properties.
