asked on
Creating calendar appointment from Excel spreadsheet
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.NOTESU
'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(
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
CalenDoc.Close
Set Maildb = Nothing
Set CalenDoc = Nothing
Set WorkSpace = Nothing
End Sub
Please help!
Thank you
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.NOTESU
'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(
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
CalenDoc.Close
Set Maildb = Nothing
Set CalenDoc = Nothing
Set WorkSpace = Nothing
End Sub
Please help!
Thank you
Lotus IBMMicrosoft Excel
Last Comment
Felix Grushevsky
Not being familiar with Lotus one other thought is ...
try application.UserName rather than usename
Chris
try application.UserName rather than usename
Chris
ASKER
How do you set a breakpoint at the beginning of the sub? I'm not a VB guru by any means.
As for the username, I don't think that's it because it opens the calendar appt without any problems, the problem is in the duration line.
Thanks,
As for the username, I don't think that's it because it opens the calendar appt without any problems, the problem is in the duration line.
Thanks,
In teh VB editor put the cursor over the line:
Set WorkSpace = CreateObject("Notes.NOTESU
and press F9
Now press the button triggering the routine and then F8 at each line to see what happens/where it falls over.
Cris
Set WorkSpace = CreateObject("Notes.NOTESU
and press F9
Now press the button triggering the routine and then F8 at each line to see what happens/where it falls over.
Cris
And out of interest where are you setting the duration?
Chris
Chris
I thought that NOTESUIWORKSPACE is not available through COM. But since you get appointment created it is not a problem..
What type of appointment are you trying to create? AppointmentType = "1" is anniversary, it does not have duration
What type of appointment are you trying to create? AppointmentType = "1" is anniversary, it does not have duration
ASKER
After changing the appointment type to "0", I"m getting a different error, that seems to have helped though.
Now I get Object Variable Not Set, Please enter starting time. Where do I indicate that at?
Here is another question, is it possible to point to specific cells in the excel spreadsheet that indicate what that starting time, starting date, etc. are?
Thank you
Here is the code again:
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.NOTESU
'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(
CalenDoc.FIELDSETTEXT "AppointmentType", "0"
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
CalenDoc.Close
Set Maildb = Nothing
Set CalenDoc = Nothing
Set WorkSpace = Nothing
End Sub
Now I get Object Variable Not Set, Please enter starting time. Where do I indicate that at?
Here is another question, is it possible to point to specific cells in the excel spreadsheet that indicate what that starting time, starting date, etc. are?
Thank you
Here is the code again:
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.NOTESU
'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(
CalenDoc.FIELDSETTEXT "AppointmentType", "0"
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
CalenDoc.Close
Set Maildb = Nothing
Set CalenDoc = Nothing
Set WorkSpace = Nothing
End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Chris is correct.
The error "Please enter starting time" is a field validation error indicating that "StartTime" field is empty wich is required for Appointent = 0
I would also insert line
CalenDoc.FIELDSETTEXT "AppointmentType", "0"
CalenDoc.Refresh
after setting appointment type,to make sure that document is populated with proper defaults after appointment type is changed
The error "Please enter starting time" is a field validation error indicating that "StartTime" field is empty wich is required for Appointent = 0
I would also insert line
CalenDoc.FIELDSETTEXT "AppointmentType", "0"
CalenDoc.Refresh
after setting appointment type,to make sure that document is populated with proper defaults after appointment type is changed
ASKER
I'm sorry I'm just not getting this, what is the exact line that I would enter to point the start time to a cell?
I have tried:CalenDoc.FIELDSETTEX
1").)
But that doesn't work.
I have tried:CalenDoc.FIELDSETTEX
1").)
But that doesn't work.
try
CalenDoc.FIELDSETTEXT "StartTime", activeworkbook.worksheets(
CalenDoc.FIELDSETTEXT "StartTime", activeworkbook.worksheets(
ASKER
Getting closer, thank you very much!
It's putting the words StartTime in the time field? I have attached a screenshot for you to see.
appt.pdf
It's putting the words StartTime in the time field? I have attached a screenshot for you to see.
appt.pdf
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I'm so sorry, I'm a complete idiot, yes, it was pointing to the wrong cell. That is working, thank you so much!!!
So that works, but now I'm getting a message about the duration. And I can't get the start date line to work if I point to a cell?
I changed the StartDate and the Duration to point to cells. Did I do it incorrectly?
CalenDoc.FIELDSETTEXT "AppointmentType", "0"
CalenDoc.FIELDSETTEXT "StartDate", ActiveWorkbook.Worksheets(
CalenDoc.FIELDSETTEXT "StartTime", ActiveWorkbook.Worksheets(
CalenDoc.FIELDSETTEXT "Duration", ActiveWorkbook.Worksheets(
CalenDoc.FIELDSETTEXT "Subject", "Subject"
CalenDoc.FIELDSETTEXT "Body", "This is a test"
So that works, but now I'm getting a message about the duration. And I can't get the start date line to work if I point to a cell?
I changed the StartDate and the Duration to point to cells. Did I do it incorrectly?
CalenDoc.FIELDSETTEXT "AppointmentType", "0"
CalenDoc.FIELDSETTEXT "StartDate", ActiveWorkbook.Worksheets(
CalenDoc.FIELDSETTEXT "StartTime", ActiveWorkbook.Worksheets(
CalenDoc.FIELDSETTEXT "Duration", ActiveWorkbook.Worksheets(
CalenDoc.FIELDSETTEXT "Subject", "Subject"
CalenDoc.FIELDSETTEXT "Body", "This is a test"
i believ you need to set EndDate and EndTime
Duration should be calculated by Notes
Duration should be calculated by Notes
Chris