Create Tasks in bulk using VBA

Can't believe I haven't posted this already. Well, if I did I couldn't find it on my site.

In Using Excel VBA to set up Task Reminders in Outlook I posted a one-off method for creating tasks in Outlook using VBA in Excel. And here I have a method for creating appointments in bulk.

But I haven't posted any way of creating tasks in bulk using VBA. So here we go!

I often get lists of events with dates, but what do you do? Do you print it out and keep looking at it all day, hoping you'll remember when you need to do something? I create task reminders for anything with a time component so that my computer can do the remembering for me.

Due to my laziness, the following procedure makes a lot of assumptions and uses hardcoding, but basically it takes a spreadsheet list of dates and subjects and creates task reminders for each row.

Sub CreateAppointments()

Dim cell As Excel.Range
Dim rng As Excel.Range
Dim wholeColumn As Excel.Range
Dim startingCell As Excel.Range
Dim oApp As Outlook.Application
Dim tsk As Outlook.TaskItem
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim lastRow As Long
Dim arrData As Variant
Dim i As Long

' start Outlook
Set oApp = GetOutlookApp
If oApp Is Nothing Then
  MsgBox "Could not start Outlook.", vbInformation
  Exit Sub
End If

' read worksheet range into an array in one go
Set wkbk = ActiveWorkbook
Set wksht = wkbk.ActiveSheet
Set wholeColumn = wksht.Range("A:A")
lastRow = wholeColumn.End(xlDown).Row - 2
Set startingCell = wksht.Range("A2")
Set rng = wksht.Range(startingCell, startingCell.offset(lastRow, 1))
arrData = Application.Transpose(rng.value)

' loop through array and create tasks for each record
For i = LBound(arrData, 2) To UBound(arrData, 2)
  Set tsk = oApp.CreateItem(olTaskItem)
  With tsk
    .DueDate = arrData(2, i)
    .subject = arrData(1, i)
    .ReminderSet = True
    Select Case Weekday(CDate(arrData(2, i)) - 4, vbSaturday)
      Case 1, 6, 7
        .ReminderTime = CDate(arrData(2, i)) - 5
      Case 2, 3
        .ReminderTime = CDate(arrData(2, i)) - 3
      Case Else
        .ReminderTime = CDate(arrData(2, i)) - 4
    End Select
    .Save
  End With
Next i

End Sub

Function GetOutlookApp() As Outlook.Application
' return Outlook.Application object
On Error Resume Next
Set GetOutlookApp = CreateObject("Outlook.Application")
End Function

Assumptions

  • Early binding
  • To make the code late bound, you will need to declare all "Outlook" objects as "Object". That includes the GetOutlookApp function. You will also need to convert any Outlook constants (olTaskItem) to their numeric equivalents, or create a local constant with the same name.

  • The Subject of each task is filled down in column A
  • The actual due date (not the reminder date) of each corresponding task is in column B
  • Row 1 is a header row
  • You want a reminder to pop up 3 business days before the work is actually due
  • After writing this I found my code to return the previous business day. You may want to use that instead.

  • There are no empty rows or cells in your list

Example List

The code above was tested and worked on the following sample table:

sample bulk tasks list
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 3 Comment(s) on Create Tasks in bulk using VBA:

  1. JP, so glad to see another article! I went through all of my personal.xlsb code last week and I realized just how much your blog has affected (improved) my coding skills.

    One thing at which I'm not very consistent is loading ranges into arrays. I'm usually lazy and use the For Each Cell method. I noticed on this and one of the other linked articles you include the line

    arrData = Application.Transpose(rng.value)

    What's the advantage of the transposition? Is that just so you can use the more familiar (column, row) format instead of the VBA standard (row, column) format?

    (Also, there's a typo in the link to your Blog Comment Policy [+1 to me for reading it]: you are missing a slash in between .com and blog.)

    • Thanks Bryan! I guess you could do it either way. It depends on my mood. In this case I took some existing code that was already written that way.

      Regarding the comment policy link, I'll get that fixed shortly, thx for reporting it.

Mentions:

  1. […] you ever get a list of dates and tasks in Excel, Jimmy Pena (JP) show how to create Outlook tasks from that list, by using Excel […]

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