Category Archives: lotus

Lotus Notes tweaks: Toolbar buttons to file mail

I like the GTD way of managing mail, and I’ve created folders for this. The three folders I use the most are AA Next Action, AA Waiting, and Done. Dragging messages down to the right folder is more mouse work than I like, though. I created three buttons on a custom toolbar. For example, the action that moves the current message to my Done folder runs this:

@Command([Folder]; “Done”; “1”)

I’d love to associate these with keyboard shortcuts, or spend some time hacking my mail template. That would be even awesomer! =)

Lotus Notes mail merge from a Microsoft Excel spreadsheet

UPDATE: David Turner has shared his excellent improvements. Check them out! Or if you like the way this script handles mail merges, see Even more awesome LotusScript mail merge for Lotus Notes and Microsoft Excel

Updated May 6 2010 – added Call varXLFile.Quit()


I’ve been looking for ways to recognize people’s voluntary contributions to community discussions. E-mailed thanks are great because people can use the Memo-to-File feature to save it in their performance record, and copying their managers means extra kudos.

I wanted to thank 21 people for their contributions. With the Lotus Connections Communities API, a little bit of Ruby scripting and an internal tool for looking up people’s managers, I came up with a spreadsheet that listed people’s names, e-mail addresses, number of posts, first-line manager, and either “post” or “posts” depending on how many posts they had. Pulling random bits together from examples on the Net, I developed this super-nifty Lotus Notes script which does a flexible mail merge from a Microsoft Excel spreadsheet to Lotus Notes.

Create the agent and copy the following code into it. Then write an e-mail that you’ll use as the template for your new messages, and call the agent while the e-mail is selected. Give it a spreadsheet where the column headings are the tokens you’d like to replace in the template (body only). “<to>” and “<cc>” are special – they’ll also be replaced in the mail header. The resulting mail messages will be in the “Drafts” folder so that you can customize the messages before sending them out.

This may not work with multi-line replacements or fancy formatting. Review before sending, and have fun. =)

Sub Initialize
  Dim ws As New NotesUIWorkspace
  'Prompt for the filename - should be a Microsoft Excel file
  'with columns, where the first row of each column
  'is a token that will be used when replacing text in the body of the message
  'Special tokens: <to> and <cc> set the appropriate fields
  fileName$ = ws.Prompt(12, "Select file", "3")
  If fileName$ = "" Then 
    Exit Sub   'Cancel was presed
  End If
  strXLFilename = fileName$
  Dim s As New NotesSession  
  Dim uidoc As NotesUIDocument
  Dim partno As String
  Dim db As NotesDatabase
  Dim view As NotesView
  Dim doc As NotesDocument
  Dim collection As NotesDocumentCollection
  Dim memo As NotesDocument
  Dim body As NotesRichTextItem
  Dim newBody As NotesRichTextItem
  Dim range As NotesRichTextRange
  Dim count As Integer
  Set db = s.CurrentDatabase
  Set collection = db.UnprocessedDocuments
  Set memo = collection.getFirstDocument()
  'Get data from the spreadsheet
  Set varXLFile = CreateObject("Excel.Application")
  varXLFile.Visible = False
  Set varXLWorkbook = Nothing
  varXLFile.Workbooks.Open strXLFilename
  Set varXLWorkbook = varXLFile.ActiveWorkbook
  Set varXLSheet = varXLWorkbook.ActiveSheet
  lngRow = 2
  While (Not (varXLSheet.Cells(lngRow, 1).Value = ""))
    'Fill in the template
    subject = memo.Subject(0)
    Set body = memo.GetFirstItem("Body")
    'Compose message
    Set maildoc = New NotesDocument(db)
    Set maildoc= db.CreateDocument()    
    maildoc.Form = "Memo"
    maildoc.Subject = subject
    Set newBody = maildoc.CreateRichTextItem("Body")
    Call newBody.appendRTItem(body)  
    Set range = newBody.CreateRange      
    'Look up tokens from the column headings and replace them
    columnNo = 1
    While Not(varXLSheet.Cells(1, columnNo).Value = "")
      token = varXLSheet.Cells(1, columnNo).Value      
      value = varXLSheet.Cells(lngRow, columnNo).Value
      count = range.FindAndReplace(token, value, 16) 
      If (token = "<to>") Then
        maildoc.SendTo = value
      End If  
      If (token = "<cc>") Then
        maildoc.CopyTo = value
      End If
      columnNo = columnNo + 1
    Call maildoc.Save(True, False)
    lngRow = lngRow +1
  Call varXLFile.Quit()   
End Sub

I like Lotus Notes 8.5 =)

Yes, I know, liking Lotus Notes is weird. It’s a little like liking Emacs, but even more inexplicable.

But Lotus Notes 8.5 lets me easily add my Google Calendar to my work calendar!

This rocks. =D

Oh, and I can work with Activities offline, too… I’m in love!

Lotus Notes Tweak: End of Message, No Response Needed

Taking a quick break from Javascript hacking to post this Lotus Notes tweak.

Following Susan Schreitmueller’s advice in the 28-hour Workday presentation she gave, I started replying in subject lines and using [EOM, NRN] to indicate the end of the message and that no response is necessary.

Not everyone’s familiar with this convention, so I always included a short explanation in the body of the message. After a number of these EOM/NRN messages, I created an AutoHotkey macro to save me a few keystrokes. I set up !eomnrn to expand to “EOM – end of message, NRN – no response needed”, and I used that in the body of the message.

I thought it still took too many keystrokes and mouse clicks to reply to a message, add my note to the subject line, add “[EOM, NRN]” to the end of the line, and type in the explanation in the body of the message. In fifteen minutes, I whipped up this little LotusScript agent that prompts you for a response, puts it in the subject line with an explanation, and sends the message off.

In Lotus Notes, use Create – Agent to create an agent called something like “1. EOM – NRN”. Edit the agent and put this in the Initialize sub.

	Dim workspace As New NotesUIWorkspace
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim collection As NotesDocumentCollection
	Dim memo As NotesDocument
	Dim reply As NotesDocument
	Set db = session.CurrentDatabase
	Set collection = db.UnprocessedDocuments
	Set memo = collection.getFirstDocument()
	While Not(memo Is Nothing)
		Set reply = memo.CreateReplyMessage( False )
		response = Inputbox("Response to " + memo.Subject(0))
		If (response <> "") Then
			reply.Subject = response + " re: " + memo.Subject(0) + " [EOM, NRN]"
			reply.Body = "EOM - end of message, NRN - no response necessary"
			reply.IsSavedMessageOnSend = True
		End If
		Set memo = collection.GetNextDocument(memo)		

Then you can select the message(s) you want to whiz through, type Alt-A 1 to call the action, and reply quickly. You can also call it while viewing a message, which is probably a safer place to start.