Creating Outlook Calendar Events From Excel

[lefty29er]

Hello,

I'm trying to integrate a timeline in Excel to create Outlook appts and need some assistance in the VB programming since I'm not very familiar. The macro now looks like this:

Sub OutlookSetAppt()

    Dim olApp As Outlook.Application
    Dim olApt As AppointmentItem
    Dim blnCreated As Boolean
    
'Read the table with appointments:
    Dim arrAppt() As Variant, i As Long
    arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
        blnCreated = True
        Err.Clear
    Else
        blnCreated = False
    End If
    On Error GoTo 0

'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location

    For i = LBound(arrAppt) To UBound(arrAppt)
    Set olApt = olApp.CreateItem(olAppointmentItem)

    With olApt
        .Start = arrAppt(i, 1) + arrAppt(i, 2)
        .End = arrAppt(i, 1) + arrAppt(i, 3)
        .Subject = arrAppt(i, 4)
        .Location = arrAppt(i, 5)
        .Body = "Created by excel tool"
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 5
        .ReminderSet = True
        .Save
    End With
    Next i


    Set olApt = Nothing
    Set olApp = Nothing

End Sub

An example of the appts I'd like to create - Excel would have general project steps listed along row 1 ( column B-L) like "Create Flyers", "Setup Tent", etc and the date for the action in the column below. There will be multiple sites which are listed down column 'A' like "San Jose","Las Vegas", etc. I'd like to be able to create Outlook events for each project step using column A for the location. The integration part between Outlook and Excel is working great, but I just don't have the VB chops to enhance the code above. Any help is appreciated.

Thanks,

lefty29er