Outlook calendar help please

[fosterp]

Hi

I am trying to populate the Outlook calendar with future appointments from Excel using VBA.
However, at the line marked **, I get the error below. Please could someone help.

[VBA] Sub SetAppt()
Dim olApp As Object
' Dim olApp As Outlook.Application
Dim olApt As Object
Dim olNs As Object
Dim usedate As Date
' The following routine displays the calendar, opening OL if needed
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If err.Number = 429 Then
Set olApp = CreateObject("Outlook.application")
End If
On Error GoTo 0
Set olNs = olApp.GetNamespace("MAPI")
If olApp.ActiveExplorer Is Nothing Then
olApp.Explorers.Add _
(olNs.GetDefaultFolder(9), 0).Activate
Else
Set olApp.ActiveExplorer.CurrentFolder = _
olNs.GetDefaultFolder(9)
olApp.ActiveExplorer.Display
End If
' Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)
' Gather the values to use in the appointment
For Each cell In Range([a2], [a65536].End(xlUp))

usedate = [a2].Value
With olApt
.Start = usedate + TimeValue("9:00:00")**' Object doesn't support this property or method (Error 438)
.End = usedate + TimeValue("11:00:00")
.Subject = usesubject
.Location = usesubject & " location"
.Body = "enter the text of your appointment here"
.BusyStatus = olOutOfOffice
.ReminderMinutesBeforeStart = 30
.ReminderSet = True
.Save
End With

olApt.Display

Next

Set olApt = Nothing
Set olApp = Nothing
Set olNs = Nothing
End Sub[/VBA]~VBA Tags Added by Oorang
[UVBA]It's True.[/UVBA]

[Aaron Blood]

Testing briefly inside of Outlook, this seemed to work...

 
    Dim olApp As New Outlook.Application
    Dim olApt As Outlook.AppointmentItem
    Set olApt = olApp.CreateItem(olAppointmentItem)
    
    With olApt
        .Start = TimeValue("9:00:00")
        .End = TimeValue("11:00:00")
        .Subject = "Test"
        .Location = "My Location"
        .Body = "enter the text of your appointment here"
        .Display
    End With

[fosterp]

The problem is, I have a spreadsheet which contains a load of dates.
The macro needs to populate the calendar with these dates as well as times.

In the code you have, it would only populate the calendar today with those times.

[Aaron Blood]

Uhh... yeah, I saw that...

I'm just showing you how to fix your problem with ill-dimensioned objects. (which is why the error raised on the line you pointed out)

There was no need for me to rewrite your entire macro. The values I assigned (whether static or incrementing) are irrelevent.

Pay attention to how I dimensioned the objects and compare it to your code. There's a reason why mine doesn't raise the same error that yours does.