Update Outlook Task Reminder Dates from Excel

A few days ago I completed a routine that lets you update Outlook task reminder dates from Excel. For example, if you had a list of tasks in one column, with updated dates in a second column, this routine would allow you to change the reminder date for the task to the newly updated date. Works well if you are managing a project and need to change reminder dates for particular items.

As usual, I used a boolean variable at the top of the module (in the Declarations section, outside of any sub) to indicate whether Outlook was started programmatically. If that is the case, we'll need to call the Quit Method (in addition to destroying the Outlook Application Object). I also used the techniques from Take advantage of Intellisense when writing late bound code by setting a reference to the Outlook Object Library and declaring my objects as Outlook.Application, Outlook.TaskItem, etc, in order to write the code, then changing declarations to Object after the code was written. Then I removed the reference to the Outlook Object Library.

The following code should be placed in a standard module in Excel. No reference to Outlook is necessary. It can be used as a UDF directly from the worksheet, with cell references as arguments, or in your VBA code as part of a larger application. It returns TRUE (boolean) if successful.

Dim bWeStartedOutlook As Boolean

Function ChangeTaskReminderDate(strName As String, dteDate As Date) As Boolean
' - updates reminder date for an Outlook task
' - if the reminder date and due date are the same, the function updates both
' - works with Outlook open or closed
' - will not set reminders to past
' by Jimmy Pena, http://www.jpsoftwaretech.com/, 12/17/2008
'
' Usage (VBA):
' Dim success As Boolean
' success = ChangeTaskReminderDate("My Task Subject", "12/21/2008")
' Usage (UDF):
' =ChangeTaskReminderDate("My Task Subject", "12/21/2008")
' or
' =ChangeTaskReminderDate(A1, B1)
' where A1 contains the task's subject, and B1 contains a valid date
'
Dim olApp As Object
Dim olNS As Object
Dim olItems As Object
Dim olItemToChange As Object

' don't change reminder date to the past
If dteDate < Now Then
  ChangeTaskReminderDate = False
  GoTo ExitProc
End If

On Error Resume Next
  Set olApp = GetOutlookApp
On Error GoTo 0

If Not olApp Is Nothing Then
  Set olNS = olApp.GetNamespace("MAPI")
  Set olItems = olNS.GetDefaultFolder(13).Items
  Set olItemToChange = olItems.Find("[Subject] = " & strName)
  'or use default Subject Property: Set olItemToChange = olItems.Item(strName)

  If olItemToChange Is Nothing Then
    ChangeTaskReminderDate = False
    GoTo ExitProc
  End If

  With olItemToChange
  ' if the reminder date and due date are the same, change both,
  ' else just change the reminder date
    If .ReminderTime = .DueDate Then
      .ReminderTime = dteDate
      .DueDate = dteDate
    Else
      .ReminderTime = dteDate
    End If
    .Save
  End With

  ' if we got this far, assume success
  ChangeTaskReminderDate = True
  GoTo ExitProc

Else
  ChangeTaskReminderDate = False
  GoTo ExitProc
End If

ExitProc:
If bWeStartedOutlook Then
  olApp.Quit
End If
Set olApp = Nothing
Set olNS = 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

After getting or creating the Outlook Application Object, we set an object reference to the default Tasks folder. Using the Find Method on the TaskItem.Subject Property will locate the task to be updated.

Finally, we check the reminder date; if it is the same as the due date, I assume that both should be updated. If the reminder date and due date are the same, and you move the reminder X days into the future, I assume that the due date moves with it; it doesn't make sense to set a reminder to occur AFTER the due date. I suppose the routine could get more complicated, for example, we could check if ((.ReminderTime + dteDate) > .DueDate) and then also move both. Of course, you can edit the code to meet your needs (let me know if you do so).

Finally, the TaskItem.Save Method is called, to make sure our changes permanent.

Note that the code assumes that each of your tasks has a unique Subject. If you index your tasks by another method, you'll need to adjust the code accordingly. Check out the restricted properties of the Find Method to see which ones cannot be used as a filter to search for the task.

For example, if you maintain a list of tasks related to a single project, with each task having the same Subject but different information in the Body, you'll need to use a different property, such as Complete (indicates completed tasks) or CreationTime (date & time the task was created) to uniquely identify the task to be updated. In general, I recommend using unique Subject lines for each task, to make them easy to identify programmatically.

Comments? Suggestions?

Related Articles:

• August 19, 2009 -- Instantiating Office Applications
• February 7, 2009 -- Easy Application.Object References
• December 17, 2008 -- SearchBox Week, Day 3