Creating Outlook Appointment from Excel Data with VBA

Question

Title says it all. I wrote the code yesterday and it worked well. I was an idiot and saved incorrectly and lost the code. However, today I rewrote the code to make it happen and I'm not sure why the appointments aren't being created today. The values are properly being stored when I F8 through my Sub. If somebody could point out the hopefully stupid mistake I overlooked, that'd be a lifesaver as I can't find it myself.

Sub test()

    Dim OL As Outlook.Application, Appoint As Outlook.AppointmentItem, ES As Worksheet, _
    r As Long, i As Long, WB As ThisWorkbook

    Set WB = ThisWorkbook
    Set ES = WB.Sheets("Export Sheet")
    r = ES.Cells(Rows.count, 1).End(xlUp).Row
    Set OL = New Outlook.Application

    For i = 2 To r
        Set Appoint = OL.CreateItem(olAppointmentItem)
        With Appoint
            .Subject = ES.Cells(i, 1).Value
            .Start = ES.Cells(i, 2).Value
            .End = ES.Cells(i, 3).Value
            .Location = ES.Cells(i, 4).Value
            .AllDayEvent = ES.Cells(i, 5).Value
            .Categories = ES.Cells(i, 6).Value & " Category"
        End With
    Next i
    Set OL = Nothing

End Sub

@peakpeak There isn't an Error Message. The code runs as expected, but there are no new Appointments in Outlook after running the macro. — JustinShotMe, Dec 14 '17 at 13:57
Assuming you've checked here and compared with your own? [link](https://blogs.msdn.microsoft.com/brunoterkaly/2014/07/24/scheduling-appointments-in-outlook-from-excel/) - You could always just copy Microsoft's example and modify it to your workbook. — Petrichor, Dec 14 '17 at 13:59

Petrichor · Accepted Answer · 2017-12-14T14:08:04.307

3

There is a working example here

It looks like you are missing .Save from the end of your loop.

Like this:

For i = 2 To r
    Set Appoint = OL.CreateItem(olAppointmentItem)
    With Appoint
        .Subject = ES.Cells(i, 1).Value
        .Start = ES.Cells(i, 2).Value
        .End = ES.Cells(i, 3).Value
        .Location = ES.Cells(i, 4).Value
        .AllDayEvent = ES.Cells(i, 5).Value
        .Categories = ES.Cells(i, 6).Value & " Category"
        .Save
    End With
Next i

edited Dec 14 '17 at 14:08

answered Dec 14 '17 at 14:02

Petrichor

975
1
9
22

If my response helped you, please mark it as an answer :) thanks! – Petrichor Dec 14 '17 at 15:02

Creating Outlook Appointment from Excel Data with VBA

1 Answers1