Setting Outlook calendar dates with excel

[conradcliff]

Hey guys, so while I'm waiting for help on my other thread I thought I would try and get some info on this issue as well.

I want to create a calendar event with data located in different cells on the active sheet of a workbook. The cells holding the data will be the same every time I do this, just the data inside them will change.

I've found two different macro's for doing this but I'm not sure which one would be better suited for my needs. here's the first one:

Sub ExportAppointmentsToOutlook()
    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

The stuff that confuses me the most(that's not to say it all doesn't confuse me) about this bit of code is

Dim arrAppt() As Variant, i As Long
    arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value

and

.Start = arrAppt(i, 1) + arrAppt(i, 2)
        .End = arrAppt(i, 1) + arrAppt(i, 3)
        .Subject = arrAppt(i, 4)
        .Location = arrAppt(i, 5)

Also, I need to have the body consist of values in specific cells on the active sheet as well.

I just don't know what this stuff means or how I'm supposed to change it to point to my information.

The other bit of code that I found is here:

Sub ExportAppointmentsToOutlook()

    Dim olApp As Outlook.Application
    Dim olApt As AppointmentItem
    Dim blnCreated As Boolean
    Dim x As Variant, LastRow As Long, ws As Worksheet

'Read the table with appointments:
    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

With ActiveSheet
        Set ws = ActiveWorkbook.Sheets(.Name)
    End With
    
    With ws.Range("A2:B" & ws.Rows.Count)
        LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
    
    For x = 2 To LastRow
    Set olApt = olApp.CreateItem(olAppointmentItem)

    With olApt
        .Start = Range("B" & x).Value
        .End = Range("C" & x).Value
        .Subject = Range("A" & x).Value
       .Location = Range("E" & x).value
        .BusyStatus = olBusy
        .ReminderSet = False
        .AllDayEvent = True
        .Save
    End With
    Next x


    Set olApt = Nothing
    Set olApp = Nothing

End Sub

This part makes me think it's searching for something:

With ws.Range("A2:B" & ws.Rows.Count)
        LastRow = .Find(What:="*", after:=.Cells(1, 1), LookAt:=xlPart,  SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With

which is why I don't think this one is as useful for my purposes as the other one. However, if you think it would be better I just need help figuring out how to input my cell ranges.

If anyone could give me some insight or point me in the right direction I would greatly appreciate it.. Thanks!

 

[conradcliff]

Thanks so much for all your help on this one, I've got it all figured out now. The next trick will be to combine it all together.

 

[marcodomingues]

How can put colors (categories) in the code ?

 

[conradcliff]

I've been out of pocket for a while now and I just wanted to stop back in and say thanks to John for helping me out with this code. Things have been working really well and I don't know what I would do without it.

Thanks a ton!

 

[veespike]

Tagging in late -

What changes would have to be made so that, instead of posting an event, it created a Task in the Task list. I want set a PO template to create reminder tasks in Outlook, rather than calendar events. Also, how would I set it to only make the task when the file is saved?

 

[John_w]

Use Outlook.TaskItem and its related properties instead of Outlook.AppointmentItem.

Call the code from the Workbook_BeforeSave event handler to create the task when the file is saved.

 

[Southernwave]

Hi ... been doing some playing here and found this tread which helped totally . I have a data base which has 4 entries per day of the whole year... when I use the VBA here I get a OUT OF MEMORY ERROR and things stop at the .SAVE line ... any suggestions how I get around this