Using Excel VBA to set up Task Reminders in Outlook

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.jpsoftwaretech.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 Object ' Outlook.Application
Dim objTask As Object ' 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 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

See Get Previous Business Day in VBA for the NextBusinessDay 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

About JP

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 to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 37 Comment(s) on Using Excel VBA to set up Task Reminders in Outlook:

  1. Wendy Wong writes:

    Dear programmer,

    how to paste this code into a standard module in Excel?

    pardon my ignorance, I am zero in programming.

    thks.
    Wendy, Singapore

    • In the VB Editor, go to Insert » Module, that is where you would put the code. If you need further assistance, check out http://www.rondebruin.nl/code.htm

      • Hello,

        I have an excel sheet where I update activity report, for example

        SL # Activity start date end date Ron Sam Shawn
        1 Improve GSC process 2/2/2011 3/22/2011 Pending Pending
        2 KPI's for supply chain 3/3/2011 3/15/2011 completed
        3 Budget 2011 3/2/2011 3/22/2011 Pending Pending Pending
        4 schedule for branches 3/12/2011 3/15/2011 Pending

        The above data is the sample, whenever there is a word pending, excel should automatically send TASK mail to the respective person to his outlook or a reminder in the outlook, with the start date and end date along with activity as a subject line. I am using 2007 excel and Outlook.

        Please note, sometime there will be multiple user who are on pending.

        Regards,
        Ron

  2. Jeffrey writes:

    Thanks a lot for this. Figured out how to post the code as Wendy asks above. Took a while. Then got a debug error and had to go in the VBA editor and select Tools>References and select Microsoft Outlook Object Library. Then worked like a charm.

  3. Glad to hear it Jeffrey!

  4. Tom P writes:

    I did everything explained here, but no macro name shows up when I hit alt+f8. I'm not very experienced in excel or vba. THanks for your help.

  5. Tom P writes:

    I think I got it to work by adding it in as a new macro.

    When I run it I'm getting error: ambiguous name detected: AddToTasks

    any ideas? Thanks much!

    • Tom,
      That usually means you've pasted it twice. It should be pasted into a standard module, i.e. if you go to the VB Editor (press Alt-F11 in Excel) and then go to Insert » Module, then immediately paste in the code from the blog post. Make sure you copy all 100 lines of code.

      If it still won't work, you might want to start with a fresh workbook.

  6. Tom P writes:

    Hi JP. Thanks for your fast response. Here's whenre I'm stumped now: When I save the workbook as a macro-enabled excel file and go through the process as explained here, it seems to input fine. But when I hit alt-q to close vba, and alt-f8 to open the macros page, there are no macros there to select and run. Any ideas? Thanks a lot!

    • It won't display, because it's a function that takes arguments. Any procedure that takes arguments won't appear in the macros list.

      If you're using this from the worksheet, just type in the name of the function and provide the arguments, like

      =AddToTasks("8/1/2009", "My task", 10)

      to get a task reminder 10 days before August 1st. If the information is inside some cells on the worksheet, just reference them per above, i.e. =AddToTasks(A1,B1,C1) where A1 contains the date, B1 contains the task text, C1 contains the number of days prior that you want the reminder.

  7. Laurence B writes:

    Thanks for this JP! It worked like a charm as soon as I added the reference to the MS Outlook library (thanks to Jeffery for solving that little problem) to the macro I incorporated this into.
    Thanks for the clear notation too; I'm just a beginner and it can take a while to work out what a mass of uncommented code does when you need to edit something.

  8. Thanks for posting this and for your follow-up help/comments. I can get this to work when first creating the spreadsheet (I receive "true" result); however, when I close and re-enter the saved xlsm spreadsheet and attempt to make another entry with the same or different data I receive a "#NAME?" error. Any help or guidance would be appreciated.

    • You need to set a reference to the Microsoft Outlook object library, so make sure the reference is still there when reopening the workbook. You might also try compiling or setting a breakpoint and stepping through the code to see where it fails. Let me know!

  9. JP thanks. I'm embarrassed to admit that I was opening the doc on an associates computer that did not have macros enabled. Were all good now. Thanks again.

  10. JP:

    I put "Dec 20, 2009" in A1, "My birthday" in B1 and "10" in C1, but no task remainder generated. What else do I need to fill or modify in the code to make it work (I paste all above code into the module and did not do anything else). Thanks a lot.

    • Put a breakpoint on the first line of code (click on "Function" and press F9). Then enter the function on the worksheet and step through it by pressing F8 repeatedly.

      i.e. =AddToTasks(A1,B1,C1)

      Does it fail on any line?

  11. JP:

    Thanks. It works now. But how can make it work automatically, i.e. today's date to trigger the run of the macro, if it meets the condition.

    Another question is that how it will work for more than one items. For example:
    A1 is Dec. 20, 09, B1 is "my birthday", C1 is set to 15
    A2 is Dec. 21, 09, B2 is "return book", C2 is set to 2.

    1st item: I want that 15 days prior to Dec 20, the sheet will generate a task remainder for me to prepare birthday;
    2nd item: I want that 2 days prior to Dec 21, the sheet will generate a task remainder for me to return the book.

    Looking forward to yuor help, master.

  12. John Moberger writes:

    Hi there JP!

    I've been searching online now for weeks, but can't seem to find the answer to my question… You seem like u know what u're talking about, so I'll give it a shot:

    I have a database with pre-booked hotel rooms, which I need to cancel on certain dates – different dates for different hotels. I need reminders to be triggered and sent to Outlook, say 10 and 5 days before the last cancellation date. I would like either an email to be sent to me with a short text like "remember to cancel hotel X in 10 days from now" (preferred solution) or a task that is automatically set in the Outlook calendar.

    Now, to make it a little more complicated; I need these "reminder emails" to be sent without even opening the Excel doc. Is this possible?

    In short, this is what the file looks like:
    A1: HOTEL X B1: HOTEL Y
    A2: 12 MAY 2010 B2: 15 JUNE 2010
    etc….

    Any help you can give would be greatly appreciated! I'm a rather experienced Excel-user, but a rookie when it comes to vba-coding…

    Many thanks man!

    / John
    Stockholm, Sweden

  13. VBA Dummy writes:

    Would someone mind sending me a completed spreadsheet that they are currently using with this code applied. My email is btrcearly@yahoo.com. I just want to be able to see how the macros functions.

  14. VBA Dummy writes:

    I am a operations management analyst and my team is responsible for completing multiple task throughout the day. As team leader I would like to be able to set up a tool where I can assign task to each person on a daily/weekly/monthly basis and have reminders go out that contain list of their assigned task. In addition, I would like to be able to manage their progress and have it set up where they are able to send updates back to me main report in regard to the progress of each of their assigned task. Does a database need to me set up in access or is their a Macro that could be created to run this type of task. I do not have any experience with coding, VBA, or access so your help would be greatly appreciated.

  15. Hey Jimmy:

    In your example you create a task with this code:
    With objTask
    .StartDate = dteDate
    .Subject = strText & ", due on: " & strDate
    .ReminderSet = True
    .Save

    How come .Owner can't be hard-coded?

    And can you tell me how to set the .AssignTo? It doesn't even show up in the olTask debugger list.

    Thanks.

    • You can set the Owner Property, but that won't change the owner of the task. It's more like a backend property. Not sure if that's what you mean, but that's how the Owner Property works.

      Are you asking how to assign a task? If you want to assign a task, add recipients to the Recipients collection using the usual methods, then call the TaskItem.Assign and TaskItem.Send methods (in that order).

  16. Hey JP…this is so helpful. I'm trying to set task reminders to for 10 days before a particular date. (H1) and I'm struggling with this….HELP! Column H has all my dates to which I need task reminders. Trying to follow your post, I went to the cell with the date, hit Alt F11, clicked Insert-module, pasted the entire code, hit Alt Q and nothing happened. No task were created. So I read a little futher and went back into the Alt F11 deal and added =AddToTasks("8/1/2009", "My task", 10) at the top of the Code with a page break underneath it. The only thing that happend is VALUE popped up in the cell. HELP!!

    • If your dates are on the worksheet, try this:

      =AddToTasks(H1,"My Task",10)

      This should be placed in a cell, for example I1, not in the VB Editor.

      Change "My Task" to the name of the event. Even better, put the information in a cell and reference the cell from the function, as I showed in the function comments:

      =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.

      The three parameters of AddToTasks are:

      strDate — a string literal date that represents the date of the event
      strText — the subject of the task reminder
      DaysOut — the number of days before the event date that you want the reminder to appear

      Any or all of these may be replaced with cell references as above.

      HTH!

      • Did work….I must be doing something wrong. I get a Compile Error: User-Defined type not defined. And it highlights the line in the code – olApp As Outlook.Application. I tried both ways. Even created a test spreadsheet with a few lines to see if it worked. On the =AddToTasks(A1, A2, A3), it pop'd the cell with NAME#…….I'm lost.

        Wish you could walk me through this one. Email me…la.shone.releford@ericsson.com

        • My mistake. Change

          Dim olApp As Outlook.Application
          Dim objTask As Outlook.TaskItem

          to

          Dim olApp As Object
          Dim objTask As Object
        • OK, it works !!!!!!!!!!!!!!!!!!!!! Now, how do I get it to count only Business days? I am using the =Workday(A1,-10) as a formula, where A1 is a due date and the cell to where the formula is in, is 10 days before that date. How to make that work in the formula =AddToTask(A1,"My Task",10)…..is beyond me.

          I tried many different formula's but none seem to do the deal.

  17. I made the change, but something is still wrong. I created a test page to do this. I literally put the date of 12/13/10 in A1….Reminder !!! in A2…..and "5" in A3. I used the formula -AddToTasks(A1,A2,A3) in cell A4….the word "TRUE" popped up in A4 but no task were in my Outlook for 5 days before 12/13…….

  18. Ok, never mind that….I just added 2 extra days to account for the weekend. LOL !! How do I send it to multiple people from here? Or do I have to forward each task in Outlook?

    • I wish you would have mentioned this earlier. It's much easier to assist you if you provide the end goal up front, instead of just the particular step you are stuck on.

      In this case, if you want to assign a task, you need to add recipients to the TaskItem.Recipients collection, then call the TaskItem.Assign and TaskItem.Send methods (in that order).

      Do you need to do this programmatically?

      If so, you'll need to add the intended recipients to additional cells.

  19. Hey JP:

    This code works on a pst. Read Excel sheet and create tasks.

    Will it work on an Exchange Server?

    Set olApp = CreateObject("Outlook.Application")
    Const olTaskItem As Integer = 3
    Dim excelDueDate As String

    ' Select cell A2, first line of real data.
    Range("A2").Select
    ' Set Do loop to stop when an empty cell is reached.
    Do Until IsEmpty(ActiveCell)
    Set olTsk = olApp.CreateItem(olTaskItem)
    With olTsk
    ActiveCell.Select
    .Contact = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    .Subject = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    .Body = ActiveCell.Value
    excelDueDate = ActiveCell.Offset(0, 3).Value
    .DueDate = Format(excelDueDate, "mm/dd/yyyy")
    .Save

    End With
    ' Step down 1 row from present location.
    Range("A" & ActiveCell.Row).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    'Cleanup
    Set olTsk = Nothing
    Set olApp = Nothing

  20. Hi there,

    I'm hoping you can help me. I'm doing a database of clients, need to have follow up dates & reminders to attach to my email address.

    Is this possible to do in Excel spreadsheet format or even Access?

    I have everything in Excel right now, so that would be easier.

    Thanks for your time:)

    MJ

  21. Ellie Corera writes:

    Hi,

    I have been trying to wrap my brain around this for days now and I really really need help my deadline is coming and I need to finish this..
    Ok I figured out how to send reminders from excel to my outlook but Now I need to send reminders to ppl and I don't know how to do this??? I know nothing of programmer or computer stuff but I get by..
    Please anyone..HOW DO I SEND AND OUTLOOK TASK REMINDER TO SOMEONE ESLE..I have copy and pasted the CODE from above but now I am stuck???PLEASE ANYONE??
    I have tried this below but I don't understand how to do this? what is the formula or whatever is needed to send a task reminder from excel from OUTLOOK?
    "In this case, if you want to assign a task, you need to add recipients to the TaskItem.Recipients collection, then call the TaskItem.Assign and TaskItem.Send methods (in that order).
    Ellie

    • I'm afraid this will require some programming knowledge. Inside the With block towards the end of the function is where you need to put the additional code.

      1) Add recipients to the TaskItem.Recipients collection.

      objTask is our TaskItem object. So we need code like this:

      .Recipients.Add "john@somewhere.com"

      The leading dot is used because in a With block we only need to specify the object name once.

      2) Call the TaskItem.Assign and TaskItem.Send methods (in that order).

      After .Save, simply add .Assign and .Send to the code. These will cause the task to be assigned and sent to whomever you specified as the recipient in step #1.

      For more information see The TaskItem Object

      • Ellie Corera writes:

        Thank you for your feed back..
        I am so sorry to bother you with this but I have been thrown into this and have no choice but to somehow figure it out..

        I understand this function "=AddToTasks(A1,A2,A3)" my question:
        When you say "Call the TaskItem.Assign and TaskItem.Send methods (in that order)"
        do you mean put it this way ex: =TaskItem.AssignandTaskItem.Send methods(A1,A2,A3)

        The individuals that I need to send the reminder to we are all connected on OUTLOOK..

        I have done the binding of "Microsoft Outlook 11.0 Object Library" I got the code in the VBA..read below..
        **********************************************************
        [Ed.: code removed]
        **********************************************************
        Thank you once again =)

        • The code you posted has nothing to do with the code we are discussing. I have removed it to avoid confusing both myself and anyone else who reads this.

          Replace the existing With block with this:

          With objTask
            .StartDate = dteDate
            .Subject = strText & ", due on: " & strDate
            .ReminderSet = True
            .Recipients.Add "john@somewhere.com"
            .Save
            .Assign
            .Send
          End With

          Of course you would need to change the recipient to whomever you want to send the Task assignment.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel