Reading Excel Date fields and Creating Outlook Appointments from them

[Chameleon5669]

Hi.

I have code that used to work on previous versions of Office but doesn't seem to now and I can't work out why.

I have a spreadsheet with various columns, one of which is dates, formatted as dates (Long Date specifically), then a macro that reads each line and creates a recurring outlook appointment for each one. However, the result is all the details are correct on the Outlook appointment, except it's creating them all today (whichever day you run it). The portion of code that is not quite working is below. It's the Start and End parameters that are just being ignored or overridden. I've also tried DateValue(cells(i, 3) and the results are the same. NB: This worked in ALL versions of Office until 365 (Office16). MS have changed something in the object model and not told anyone, which is typical and unhelpful.

Is anyone able to shed any light please?

With olAppt
Set oPat = .GetRecurrencePattern
oPat.RecurrenceType = olRecursYearly
'Define calendar item properties

.Start = Format(Cells(i, 3), "yyyy-mm-dd") & " 9:00:00 AM" 'DateValue(apptDate)
.End = Format(Cells(i, 3), "yyyy-mm-dd") & " 9:01:00 AM" 'DateValue(apptEnd)

.Subject = Cells(i, 1) + " " + Cells(i, 2) + " is on " & Cells(i, 3) & " since " & Cells(i, 4)
.Body = .Subject
.BusyStatus = olFree
.ReminderMinutesBeforeStart = 10080
.ReminderSet = True
.Save
End With

 

[John_w]

Delete:

Set oPat = .GetRecurrencePattern
oPat.RecurrenceType = olRecursYearly

Insert after setting the start and end dates & times:

.GetRecurrencePattern = olRecursYearly

PS please put VBA code inside VBA code tags - click the VBA icon in the message editor toolbar.

 

[Chameleon5669]

Delete:

Set oPat = .GetRecurrencePattern
oPat.RecurrenceType = olRecursYearly

Insert after setting the start and end dates & times:

.GetRecurrencePattern = olRecursYearly

PS please put VBA code inside VBA code tags - click the VBA icon in the message editor toolbar.

Thanks John. That has solved it. I'm curious though - why does the order of setting the members matter? The start and end would be the start and end no matter what the recurrence pattern (unless my logic is way off beam for some reason)?

 

[John_w]

I don't know. Maybe it needs to know the start date before it can calculate the recurrence series dates.