Dates not being read correctly

Question

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.

Is anyone able to shed any light please?

With olAppt
Set oPat = .GetRecurrencePattern
.oPat.RecurrenceType = olRecursYearly
.Start = DateValue(Cells(i, 3)) + TimeValue("9:00:00")
.End = DateValue(Cells(i, 3)) + TimeValue("9:01:00")
.Subject = Cells(i, 1) + " " + Cells(i, 2) + " is on " & Cells(i, 3) & " since " & Cells(i, 4)
.Body = .Subject
.BusyStatus = olFree
.ReminderMinutesBeforeStart = 10080
.ReminderSet = True
'.Categories = Cells(i, 4)
.Save
End With

Answer 1

Hi,

Try out this code-

With olAppt
    Set oPat = .GetRecurrencePattern
    .oPat.RecurrenceType = olRecursYearly
    
    ' Convert the date from the cell to a Date data type explicitly
    Dim apptDate As Date
    apptDate = DateValue(Cells(i, 3)) + TimeValue("9:00:00")
    .Start = apptDate
    
    ' Calculate the end time
    .End = apptDate + TimeValue("0:01:00")
    
    .Subject = Cells(i, 1) & " " & Cells(i, 2) & " is on " & Format(apptDate, "Long Date") & " since " & Cells(i, 4)
    .Body = .Subject
    .BusyStatus = olFree
    .ReminderMinutesBeforeStart = 10080
    .ReminderSet = True
    '.Categories = Cells(i, 4)
    .Save
End With

I've added explicit conversion of the date from the cell to a Date data type using DateValue(). Also, I've used Format() to ensure that the date is presented to Outlook in a recognizable format.

Also, ensure that your Outlook version and Excel version are compatible with the VBA libraries and references you're using.

Best Regards.