Update Outlook Task Reminder Dates from Excel
December 29, 2008 • JP • 8 Comments • Rate This Article
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.
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.codeforexcelandoutlook.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?
Automation, Excel, Outlook ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Code Contest: Submissions And Voting Information
Next Post: Look for and create folders programmatically in Outlook
Hi JP
I have a spreadsheet that has several columns that include a date in some of the cells. Currently i then open outlook calendar on the given date to add a text field reminder pasted from another column in the spreadsheet. Is it possible you can adapt your coding and include instructions on how to insert into VBA in Excel 2007? This would save me a great deal of time
thanks in advance
John
Can you please tell me the steps to implement this?
I enter a date in a cell in excel spreadsheet called Domains and Hosts e.g 21/Aug/09 in cell E28 and then open my calendar in Outlook and go to the same date to enter the text in Cell M28 at the 0900hrs slot in that day. If there is already an entry there then I place it in the next slot available as the day rather than the time of day is more important. I have up to 5 different columns that can have a date entered that then needs to be placed in Outlook though the text cell of the description (M28)is always the same.Hope that's clear?
thanks in advance
John
John,
Outlook version?
Are these appointments, or meetings?
Are you using MS Exchange?
HI
Its Outlook 2007 and it is delivered via MS Exchange 07. The dates are entered as appointments at 09:00 hours or next available 30 minute later slot.
Thanks again
John
You would use the GetFreeBusy Method to check for the next available time slot – see http://www.codeforexcelandoutlook.com/blog/2008/12/code-contest-submissions-and-voting-information/ for Stan Scott's code to do just that.
To create the appointment, you can adapt the code found here:
http://www.codeforexcelandoutlook.com/blog/2008/09/add-to-outlook-calendar-udf/
Thanks very much – i will look this over with interest.
regards
John
Thanks JP
I want to import / update tasks created from schedules created in an Access database
This is a great start for the Outlook side
Ian