Hi,
I’m trying to write a VBA script in Excel to add a Calendar entry to Notes from Excel. I’m using the code from this source written by Joe Korzeniewski of www.vortexwd.net http://www.vortexwd.net as my template
http://www.fabalou.com/VBandVBA/lotusnotesappointment_ver6.asp http://www.fabalou.com/VBandVBA/lotusnotesappointment_ver6.asp I have also been referring to the IBM® Lotus® Notes® Calendaring & Scheduling Schema which dates from 2005 and relates to Notes version 5 and later
So far the code opens a new Calendar entry and correctly adds the date, time etc, but I can’t get it to go to the final stage “Save and Send Invitations” so the user still has to go in and manually “save and send”
I suspect a lot of the code doesn’t seem to work as the schema suggests, which isn’t surprising given that we are 4 Notes version later, e.g. according to the schema this code should change the Appointment from a meeting to a reminder:
CalenDoc.FIELDSETTEXT “AppointmentType”, “4”
But it doesn’t.
Below is my code, the first sub gets the required data from my Excel workbook and passes it to the second sub which should create the Calendar entry. n.b. I haven’t set the server name because my understanding is that if you don’t the default server is the one the user is using, which is what I want since the goal is to add a calendar reminder into the users own Notes Calendar and not anyone elses.
Thanks in advance for taking the time to look at this.
David
Sub GetData()
Dim UserName As String
Dim Subject As String
Dim Body As String
Dim AppDate As Date
Dim StartTime As Date
Dim MinsDuration As Integer
Dim Session As Object
MsgBox “Please open Lotus Notes, before clicking ok.”
UserName = Range(“StaffNumber”).Value
Subject = Range(“EventName”).Value
Body = Range(“EventNotes”).Value
AppDate = Range(“EventDate”).Value
StartTime = “09:30:00”
MinsDuration = 60
SendNotesAppointment UserName, Subject, Body, AppDate, StartTime, MinsDuration
End Sub
Public Sub SendNotesAppointment(UserName As String, Subject As String, Body As String, AppDate As Date, StartTime As Date, MinsDuration As Integer)
'Set up the objects required for Automation into lotus notes
Dim MailDbName As String 'The persons notes mail database name
Dim StartTime As String
Dim EndTime As String
Dim CalenDoc As Object 'The calendar entry itself
Dim WorkSpace As Object
Dim ErrCnt As Integer
Set WorkSpace = CreateObject(“Notes.NOTESUIWORKSPACE”)
MailDbName = "mail" + UserName + “.nsf”
StartTime = CStr(FormatDateTime(StartTime, vbShortTime))
EndTime = CStr(FormatDateTime(DateAdd(“n”, MinsDuration, StartTime), vbShortTime))
'MAKE SURE TO SET SERVER NAME BELOW
Set CalenDoc = WorkSpace.COMPOSEDOCUMENT(“”, MailDbName, “Appointment”)
CalenDoc.FIELDSETTEXT “AppointmentType”, “4”
CalenDoc.Refresh
'Each loop is used to write the value to the field until the field is changed to that value
Do Until (CDate(Right(CalenDoc.fieldgettext(“StartDate”), 10)) = CDate(AppDate)) Or ErrCnt = 1000
CalenDoc.FIELDSETTEXT “StartDate”, CStr(FormatDateTime(AppDate, vbShortDate))
CalenDoc.Refresh
'ErrCnt is used to prevent an endless loop
ErrCnt = ErrCnt + 1
Loop
ErrCnt = 0
Do Until (CDate(CalenDoc.fieldgettext(“StartTime”)) = CDate(strSTime)) Or ErrCnt = 1000
CalenDoc.FIELDSETTEXT “StartTime”, strSTime
CalenDoc.Refresh
ErrCnt = ErrCnt + 1
Loop
ErrCnt = 0
Do Until (CDate(Right(CalenDoc.fieldgettext(“EndDate”), 10)) = CDate(AppDate)) Or ErrCnt = 1000
CalenDoc.FIELDSETTEXT “EndDate”, CStr(FormatDateTime(AppDate, vbShortDate))
CalenDoc.Refresh
ErrCnt = ErrCnt + 1
Loop
ErrCnt = 0
Do Until (CDate(CalenDoc.fieldgettext(“EndTime”)) = CDate(strETime)) Or ErrCnt = 1000
CalenDoc.FIELDSETTEXT “EndTime”, strETime
CalenDoc.Refresh
ErrCnt = ErrCnt + 1
Loop
CalenDoc.FIELDSETTEXT “Subject”, Subject
CalenDoc.FIELDSETTEXT “Body”, Body
CalenDoc.Refresh
CalenDoc.Save
Set CalenDoc = Nothing
Set WorkSpace = Nothing
End Sub