Lotus Notes Calendaring with VB / VBA.

Part 1 - How to Automatically Schedule a Lotus Notes Calendar Appointment Using COM

Before I begin, I would like to present you with the bible of lotus notes automation: The Lotus Notes COM & OLE Object Reference. I don't remember where I found it, but I definitely know I couldn't find it again.

First of all, these snippits are meant to integrate an appointment scheduling application with lotus notes. Lotus Notes has two kinds of automation: One that mimics the actions a user would go through to accomplish something in the UI (this opens the lotus notes client automatically if not already open). The other uses the back end classes to create and view documents without opening lotus notes.

Many moons ago I fixed up some code from Fabalou.com that used the UI classes. The link to that is on the Articles page. The old example only scheduled appointments, it worked very slowly and it was very cumbersonme for secretaries to switch back and forth from several lotus notes calendars and my database app. For about six months I pounded my head against the wall trying to come up with a practical way to list a person's appointments for a day so that my app could have signify free time in a drop down box.

There are a couple prerequisites for any of these functions to work properly. First of all, this code is written in VBA for M$ Access. I have never tried it in VB6, but I suspect that there will be some minor changes needed to make it work properly. Second, this is made to be a vehicle for creating appointments for other people (one or more secretaries scheduling appointments for one or more people), so calendar permissions must be properly set on each schedulee's calendar for the secretaries they want to be doing their scheduling. Manager rights (I haven't tested any other more restrictive permissions) must be set for each secretary in two places in the schedulee's calendar: File-> Database -> Access Control while the Welcome screen has the focus and File-> Database -> Access Control while the calendar has the focus. You must set permissions in both of these places for ANY OF THESE functions to work.

On to the good stuff...

First off we have SendNewNotesAppointment. This does exactly what it says, creates a new appointment in the specified lotus notes calendar. Lets start with the arguments:

• UserName: the lotus notes shortname of the person on whose calendar you wish to schedule.
• UserPassword: the password of the person's lotus notes client who is logged on to the computer, not the password for the user you want to schedule for! This is the reason we set the permissions on the other users calendars.
• Subject: this is what you want to display on the calendar view.
• Body: body of the appointment
• ApptDate: the date (as a string) of the appointment
• StartTime: the time (as datetime) the appointment will start
• MinsDuration: how long (integer, in minutes) the appointment will last

We calculate the start time and end time:

strSTime = CStr(FormatDateTime(StartTime, vbShortTime)) strETime = CStr(FormatDateTime(DateAdd("n", MinsDuration, StartTime), vbShortTime))

And then open the session. If the password passed to the function is "~" that means that the user has not opted to store their password (this is handled by the password storage / retrieval function which is a whole different story.) Please note that this assumes that the user's mail file path is done the standard way "Mail\ShortName.nsf".

MailDbName = "mail\" + UserName + ".nsf" Set MySession = CreateObject("Lotus.NotesSession") If Not UserPassword = "~" Then Call MySession.Initialize(UserPassword) Else Call MySession.Initialize End If

This next part opens the database, creates a new document and specifies that the document should use the "Appointment" form.

Set NotesDB = MySession.GETDATABASE(ServerName, MailDbName, False) If Not NotesDB.ISOPEN Then Exit Function Set CalenDoc = NotesDB.CREATEDOCUMENT CalenDoc.REPLACEITEMVALUE "Form", "Appointment"

Now we set all the form items to our respective values.

CalenDoc.REPLACEITEMVALUE "AppointmentType", "0" CalenDoc.REPLACEITEMVALUE "STARTDATETIME", CDate(ApptDate & " " & strSTime) CalenDoc.REPLACEITEMVALUE "CALENDARDATETIME", CDate(ApptDate & " " & strSTime) CalenDoc.REPLACEITEMVALUE "EndDateTime", CDate(ApptDate & " " & strETime) CalenDoc.REPLACEITEMVALUE "Subject", Subject CalenDoc.REPLACEITEMVALUE "Body", Body CalenDoc.REPLACEITEMVALUE "MeetingType", 1 CalenDoc.REPLACEITEMVALUE "$Alarm", -30

Here is a very important line (which takes longer to execute than the rest of the function combined). ComputeWithForm calculates all the fields that we didn't explicitly set above in the same way that the actual appointment form in LotusNotes would. It also automatically corrects any errors it encounters. Very handy.

CalenDoc.COMPUTEWITHFORM True, False

Finish up. Save the document and return true signifying that the appointment was properly created. If an error was encountered, the error handler will transfer will go to SendNotesErr: and return false.

CalenDoc.Save True, False SendNewNotesAppointment = True DoCmd.Hourglass False Exit Function SendNotesErr: DoCmd.Hourglass False SendNewNotesAppointment = False

Here's the full code.