Another task in our migration to Outlook as the mail client is creating Outlook calendar entries and meeting notices directly from the Notes client. Fortunately, it’s been two years since I wrote about how to do this in the UI in Notes, so I don’t feel like that was wasted time. I was exciting to solve the problem and… oddly enough, solving this one was fun as well. It helped that creating an iCal entry is far simpler than the gyrations we had to go through to create one in Notes. As noted previously, there aren’t a whole lot of required values to generate in order to have an ICS file that you can open in the UI as a meeting notice/calendar invite.
BEGIN:VCALENDAR BEGIN:VEVENT DTSTART:20170622T211500 DTEND:20170622T221500 ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;CN="Required Person/Company";RSVP=TRUE:mailto:Required_Person@company.com ATTENDEE;ROLE=OPT-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE:mailto:external_person@SecondCompany.com SUMMARY:2017 Q2 QPR: Agribusiness Competitiveness Enhancement via file UID:AC1804D765C782CD8525814500073F3720170620T104326 END:VEVENT END:VCALENDAR
Now, keep in mind that this iCal file is a mere fragment. If you sent that file to someone, they get the same behaviour you get — it thinks they’re the meeting organizer and doesn’t save it to their calendar unless they send the ‘update’. The key parameter we leave off is that we don’t set METHOD, since setting that to PUBLISH or REQUEST proved problematic in the Outlook client. If we leave it off, Outlook will allow us to treat it like a brand new calendar entry we’ve created, except that the send button will say ‘Send Update’.
So, let’s review those values in our fragment…
Objects
First, the calendar and event objects are encapsulated. Nothing fancy there.
BEGIN:VCALENDAR BEGIN:VEVENT END:VEVENT END:VCALENDAR
Meeting times
Then we have our start and end times, formatted with date first (YYYYMMDD) then a separator (T) and then the time (HHMMSS). You can include time zone information, but we’re creating this in Outlook and allowing the UI to finish everything for us. So, if the user wants to change the time zone, they can do that in Outlook.
DTSTART:20170622T211500 DTEND:20170622T221500
Attendees
The one required value for our needs in the attendees is the mailto value. Without that, it won’t know who to send the invite to and it simply ignores any other item in that list.
ATTENDEE;ROLE=CHAIR;PARTSTAT=ACCEPTED;CN="Meeting Chair/Company";RSVP=TRUE:mailto:Meeting_Chair@company.com ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;CN="Required Person/Company";RSVP=TRUE:mailto:Required_Person@company.com ATTENDEE;ROLE=OPT-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE:mailto:external_person@SecondCompany.com
ROLE is not required and can be CHAIR, REQ-PARTICIPANT (required participant), OPT-PARTICIPANT (optional participant) or even NON-PARTICIPANT (for FYI only).
PARTSTAT is not required. There are several values for an attendee in a VEVENT for their participant status, but we’re only concerned with two. Either “NEEDS-ACTION” for attendees that we don’t know when we create the meeting notice whether they’ve agreed to attend or “ACCEPTED” which we’d typically only use for the person creating the meeting notice.
CN is, of course, familiar to us as Notes developers, but it applies here to whatever will be displayed as the attendee name. In my experience, Outlook can parse the abbreviated name and display just the attendee’s common name. That might be our Outlook configuration, but I would assume it’s common.
RSVP would be either true or false, indicating whether you want a response from the attendee. In my case, we always want it from the attendees, other than the current user.
Title and description
I got fooled by this one. In my sample ICS files, I thought there was just an odd carriage return, but the DESCRIPTION value is basically the body or details of the event, while the SUMMARY is what appears in the subject line for the meeting.
SUMMARY:2017 Q2 QPR: Agribusiness Competitiveness Enhancement via file
Meeting ID
I’m guessing that Outlook computes the unique meeting ID itself, but in my code, I generate from the Notes document’s unique ID and then, in order to ensure that subsequent meetings concerning the same document get different IDs, I’m appending a creation time-stamp.
UID:AC1804D765C782CD8525814500073F3720170620T104326
So, the agent I wrote that generates the new meeting notice is pretty straight-forward. The getEmailAddress function was described and detailed in a prior blog post and my Utilities script library only provides the logging functions here. Like my mailto agent, this one relies on the creation of a file in the Notes data directory and opening it using a browser.
The agent
First, you can look over the main part of the agent…
%REM
Agent (Send Calendar Invites)
Created Jun 20, 2017 by David Navarre/DAI
Description: This Agent creates a calendar invite, listing participants and optional participants
%END REM
Option Public
Option Declare
Use "Utilities"
Dim session As NotesSession
Sub Initialize
Dim ws As New NotesUIWorkspace
' Dim thisdb As NotesDatabase declared in Utilities script library '
Dim uidoc As NotesUIDocument
Dim qprdoc As NotesDocument
Dim history As NotesRichTextItem
Dim chairName As NotesName
Dim recipientName As NotesName
Dim projectName As Variant
Dim fiscalYearAndQuarter As Variant
Dim participants As Variant
Dim participantsOptional As Variant
Dim subject As String
Dim answer As Variant
Dim reason As String
Dim unid As String
On Error GoTo errorhandler
set session = New NotesSession
Call StartAgentLogging ( session )
If ( openAddressBooks () ) Then
agentLog.Logaction("Address books opened")
End If
Set thisdb = session.CurrentDatabase
Set uidoc = ws.CurrentDocument
reason = "This will create a meeting invite for you to send to participants."
If uidoc.Editmode Then
reason = reason + Chr$(10) + "The QPR will switch to read-only mode."
reason = reason + Chr$(10) + "If you close and re-open it, you can edit it again."
End If
reason = reason + Chr$(10) + "Continue?"
answer = ws.Prompt ( PROMPT_YESNO, "Continue?", reason )
If answer = 0 Then
Exit Sub
End If
If uidoc.Editmode Then
Call uidoc.Save()
uidoc.Editmode = False
Set qprDoc = uidoc.Document
unid = qprDoc.Universalid
Call uidoc.Close(True)
Set qprDoc = thisdb.Getdocumentbyunid(unid)
Set uidoc = ws.Editdocument(False, qprDoc, True)
Else
Set qprDoc = uidoc.Document
End If
Dim fileName As String
Dim dataDirectoryPath As String
Dim url As String
Dim fileNumber As Integer
fileNumber = 1
dataDirectoryPath = session.Getenvironmentstring("Directory", True)
fileName = dataDirectoryPath & "\QPRInvite.ics"
Open fileName For Output As fileNumber
Print # fileNumber, {BEGIN:VCALENDAR}
Print # fileNumber, {BEGIN:VEVENT}
Print # fileNumber, {DTSTART:} & getMeetingTime ( "Start", qprDoc ) '20170620T211500
Print # fileNumber, {DTEND:} & getMeetingTime ( "End", qprDoc ) '20170620T221500
' Chair '
Set chairName = New NotesName ( session.Effectiveusername )
' when you send the invite from Outlook, it makes you the chair '
' this line is here to show how you would format an attendee line for the chair '
' Print # fileNumber, {ATTENDEE;ROLE=CHAIR;PARTSTAT=ACCEPTED;CN="} & chairName.Abbreviated & {";RSVP=TRUE:mailto:} & getEmailAddress ( chairName.Abbreviated ) '
' Required participants '
participants = qprDoc.Getitemvalue ( "Participants" )
ForAll entry In participants
Set recipientName = New NotesName ( entry )
If Not ( chairName.Abbreviated = recipientName.Abbreviated ) Then
Print # fileNumber, {ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;CN="} & recipientName.Abbreviated & {";RSVP=TRUE:mailto:} & getEmailAddress ( recipientName.Abbreviated )
End If
End ForAll
' Optional participants '
participantsOptional = qprDoc.Getitemvalue ( "ParticipantsOptional" )
ForAll entry In participantsOptional
Set recipientName = New NotesName ( entry )
Print # fileNumber, {ATTENDEE;ROLE=OPT-PARTICIPANT;PARTSTAT=NEEDS-ACTION;CN="} & recipientName.Abbreviated & {";RSVP=TRUE:mailto:} & getEmailAddress ( recipientName.Abbreviated )
End ForAll
projectName = qprDoc.Getitemvalue("ProjectName")
fiscalYearAndQuarter = qprDoc.Getitemvalue("FiscalYearAndQuarter")
subject = fiscalYearAndQuarter(0) & " QPR: " & projectName (0)
Print # fileNumber, {DESCRIPTION:} & subject ' this is the body of the message
Print # fileNumber, {SUMMARY:} & subject ' this is the meeting name
' assign a unique ID to meeting using the unid of the document with the current date-time appended '
' in case user creates multiple meetings for the same QPR '
Print # fileNumber, {UID:} & qprdoc.Universalid & getMeetingTime ( "Now", qprDoc )
Print # fileNumber, {END:VEVENT}
Print # fileNumber, {END:VCALENDAR}
Close # fileNumber
url = "file:///" & fileName
Call ws.Urlopen(url)
Set history = qprDoc.Getfirstitem("History")
Call history.Appendtext(Now & " - Meeting notice created by " & session.Commonusername)
Call history.Addnewline(1, True)
Call qprDoc.Replaceitemvalue("NoticeFlag", 1)
Call qprDoc.Save(True, False)
exiting:
Exit Sub
errorhandler:' report all errors in a messagebox '
reason = "Error #" & CStr (Err) & " (" & Error & ") on line " & CStr (Erl)
MessageBox reason, 16, "Error"
Call agentLog.LogAction ( reason )
Resume exiting ' transfers control to the exiting label
End Sub
getMeetingTime
The getMeetingTime function just returns the requested date-time in the format YYYYMMDDTHHMMSS, so it can be included in the creation of the ICS file.
%REM
Function getMeetingTime
Description: This Function returns a string in the format YYYYMMDDTHHMMSS
If it is the start time, the values from the source document are used -- 20170622T211500
If it is the end time, it is adjusted one hour later -- 20170622T221500
If it is the "Now" time, it returns a string for the current date and time -- 20170620T094326
%END REM
Function getMeetingTime ( startOrEnd As String, qprDoc As NotesDocument ) As String
Dim thisNotesDateTime As NotesDateTime
Dim qprDate As Variant
Dim qprTime As Variant
Dim timeString, dateString As Variant
Dim reason As String
On Error Goto errorhandler
Set qprDate = qprDoc.Getfirstitem("QPRDate")
Set qprTime = qprDoc.Getfirstitem("QPRTime")
dateString = qprDate.Text
timeString = qprTime.Text
Set thisNotesDateTime = New NotesDateTime ( dateString & " " & timeString )
Select Case startOrEnd
Case "End"
Call thisNotesDateTime.AdjustHour (1)
Case "Now"
Set thisNotesDateTime = New NotesDateTime ( Now )
Case else
' keep thisNotesDateTime as set on the source document '
End Select
dateString = thisNotesDateTime.DateOnly
timeString = thisNotesDateTime.TimeOnly
getMeetingTime = CStr ( Year ( dateString ) )
getMeetingTime = getMeetingTime & Right$ ( "0" & CStr ( Month ( dateString ) ), 2 )
getMeetingTime = getMeetingTime & Right$ ( "0" & CStr ( Day ( dateString ) ), 2 )
getMeetingTime = getMeetingTime & "T"
getMeetingTime = getMeetingTime & Right$ ( "0" & CStr ( Hour ( timeString ) ), 2 )
getMeetingTime = getMeetingTime & Right$ ( "0" & CStr ( Minute ( timeString ) ), 2 )
getMeetingTime = getMeetingTime & Right$ ( "0" & CStr ( Second ( timeString ) ), 2 )
exiting:
Call agentLog.LogAction ( "-------" )
Call agentLog.LogAction ( "-------" )
Exit Function
errorhandler:' report all errors in a messagebox '
reason = "Function getMeetingTime: "
reason = reason & "Error #" & Cstr (Err) & " (" & Error & ") on line " & Cstr (Erl)
Messagebox reason, 16, "Error"
Call agentLog.LogAction ( reason )
Resume exiting
End Function
While this did take me a few days to sort out, I’m pretty happy with the result. Our configuration has users sharing one “migration” mail file, so that users who are already on Outlook still retain a mail file and can send email. Unfortunately, that means any email from them that we create in the UI is going to have values pointing back to the “migration” mail file. I spent my first few days on this trying to spoof the mail.box by changing Principal, ReplyTo, $InetAddress and Chair when sending via Notes calendaring. While changing Chair did make it appear to come from the current user, it always displayed the email address from the “migration” mail file. It might have been getting caught in our spam filter on the way to Outlook, as my test user on Notes was still receiving the notices. Nonetheless, by switching to using Outlook as the UI, it not only took away that problem, but was far simpler and future-proofed my application. As I look at these tools I’ve created in LotusScript to generate mail messages and calendar entries, I know that it’s but a short step to doing them in server-side Javascript or maybe in Java.
There is hope for the Notes gurus of old. We just have to keep learning!
Lost in Code 