Code For Excel And Outlook Blog

Using Excel VBA to set up Task Reminders in Outlook

November 2, 2008 – 10:41 pm by JP

---

    I know I have trouble remembering when different projects or tasks are due. Here’s one way to remember: Set up a list of tasks and due dates in Excel, then use VBA to set up reminders in Outlook.

    It’s perfect for things like birthdays, holidays, anniversaries, etc, anytime you want a reminder prior to the actual event, instead of the usual task reminders which seem to pop up right when something is due.

    This code will silently create a task reminder in Outlook a set number of days before the date you specify. I say “silently,” because this code does not trigger the Outlook object model guard — it creates tasks without any popups whatsoever!

    Paste this code into a standard module in Excel:

Dim bWeStartedOutlook As Boolean

Function AddToTasks(strDate As String, strText As String, DaysOut As Integer) As Boolean
' Adds a task reminder to Outlook Tasks a specific number of days before the date specified
' Returns TRUE if successful
' Will not trigger OMG because no protected properties are accessed
' by Jimmy Pena, http://www.codeforexcelandoutlook.com, 10/30/2008
'
' Usage:
' =AddToTasks("12/31/2008", "Something to remember", 30)
' or:
' =AddToTasks(A1, A2, A3)
' where A1 contains valid date, A2 contains task information, A3 contains number of days before A1 date to trigger task reminder
'
' can also be used in VBA :
'If AddToTasks("12/31/2008", "Christmas shopping", 30) Then
'  MsgBox "ok!"
'End If

Dim intDaysBack As Integer
Dim dteDate As Date
Dim olApp As Outlook.Application
Dim objTask As Outlook.TaskItem

' make sure all fields were filled in
If (Not IsDate(strDate)) Or (strText = "") Or (DaysOut <= 0) Then
  AddToTasks = False
  GoTo ExitProc
End If

' We want the task reminder a certain number of days BEFORE the due date
' ex: if DaysOut = 120, then we want the due date to be -120 before the date specified
' we need to pass -120 to the NextBusinessDay function, so to go from 120 to -120,
' we subtract double the number (240) from the number provided (120).
' 120 - (120 * 2); 120 - 240 = -120

intDaysBack = DaysOut - (DaysOut * 2)

dteDate = NextBusinessDay(CDate(strDate), intDaysBack)

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

If Not olApp Is Nothing Then
  Set objTask = olApp.CreateItem(3)  ' task item

  With objTask
    .StartDate = dteDate
    .Subject = strText & ", due on: " & strDate
    .ReminderSet = True
    .Save
  End With

Else
  AddToTasks = False
  GoTo ExitProc
End If

' if we got this far, it must have worked
AddToTasks = True

ExitProc:
If bWeStartedOutlook Then
  olApp.Quit
End If
Set olApp = Nothing
Set objTask = Nothing
End Function

Function NextBusinessDay(dteDate As Date, intAhead As Integer) As Date
' from Sue Mosher's book "Microsoft Outlook Programming"
Dim dteNextDate As Date

dteNextDate = DateAdd("d", intAhead, dteDate)

Select Case Weekday(dteNextDate)

' if Sunday, add 1 day to make it next business day (Monday)
    Case 1
        dteNextDate = dteNextDate + 1
' if Saturday, add 2 days to make it next business day (Monday)
    Case 7
        dteNextDate = dteNextDate + 2
End Select
NextBusinessDay = dteNextDate

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 you get TRUE or FALSE in the target cell, you’ll want to delete the function, so it doesn’t keep trying (and succeeding) to add task reminders over and over every time the worksheet recalculates.

–JP

 Print This Post  |   Email This Post  |   Subscribe to Posts Feed  |   Subscribe to Comments

Filed Under: Excel, Outlook, automation
Tags: CreateItem, GetObject, task reminder, TaskItem