I have this partially working, creating a calendar appointment by click a command button in an Excel spreadsheet. But I keep getting an Object Variable Not Set message on the new appointment in Lotus Notes.
I click the button, it opens the appointment, but the variable message comes up. When I click ok, really quick I can see it's a problem with the duration, it says Incorrect Data and something else. I can't see what it says because it flashes so quickly and then the appointment closes.
Here is the code I'm using:
Private Sub CommandButton1_Click()
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim MailDbName As String 'The persons notes mail database name
Dim CalenDoc As Object 'The calendar entry itself
Dim WorkSpace As Object
Set WorkSpace = CreateObject("Notes.NOTESUIWORKSPACE")
'Get the engineer username and then calculate the mail file name
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " ")))
MailDbName = "mail\" & Left$(MailDbName, 8) & "user.nsf"
'Create a new calender appointment based on template and set the attributes.
Set CalenDoc = WorkSpace.COMPOSEDOCUMENT("bbkw1notes", MailDbName, "Appointment")
CalenDoc.FIELDSETTEXT "AppointmentType", "1"
CalenDoc.FIELDSETTEXT "StartDate", CStr(Format(Date, "mm/dd/yy"))
CalenDoc.FIELDSETTEXT "StartTime", CStr(Duration)
CalenDoc.FIELDSETTEXT "Subject", "Subject"
CalenDoc.FIELDSETTEXT "Body", "Body"
CalenDoc.Save False, False, False
Set Maildb = Nothing
Set CalenDoc = Nothing
Set WorkSpace = Nothing