Category Archives: lotus

On this page:

Even more awesome LotusScript mail merge for Lotus Notes + Microsoft Excel

UPDATE: May 20, 2011 – David Turner has shared his excellent improvements. Check them out!

UPDATE: May 28, 2010 – fixed errors caused by default Option Declare. Thanks to Lisa Harnett for the feedback!
UPDATE: Oct 19, 2010 – fixed Quit(). Thanks to Vance for the feedback! Also, clarified tokens.

Based on the feedback on my Lotus Notes mail merge from a Microsoft Excel spreadsheet (2009), I’ve refined my merge script to make it more awesome. How is it more awesome?

  • Prompts you for drafting or sending
  • Saves sent messages
  • Allows you to customize the subject
  • Uses [ ] instead of < and > for built-in tokens ([to], [cc], [subject]) for less HTML confusion in blog posts and replies
  • Displays number of sent messages and errors
  • Closes the Microsoft Excel spreadsheet afterwards

The search-and-replace tokens are defined in the first row of your Microsoft Excel spreadsheet. The script searches for them in the message body, replaces them with the appropriate values from the current row, and either saves the message as the draft or sends the message. There are a few built-in tokens for this script ([to], [cc], [subject]) – these are case-sensitive, so enter them exactly like that. All the other tokens are up to you, so you could use FOO and BAR as search-and-replace tokens if you want.

Tokens are replaced only in the message body. If you want a variable subject line, use a formula to calculate the subject in a column with the [subject] header.

As always, test your mail merges with a small list before using it for your entire list. Create an agent and call it something like “Mail merge”. Edit the agent and set the type to LotusScript. In the (Declarations) section, add

%Include "lsconst.lss"

In the “Initialize” section, put in:

Sub Initialize
	'Mail merge script by Sacha Chua ([email protected])

	Dim ws As NotesUIWorkspace
	Set ws = New NotesUIWorkspace
	Dim sendTypes(1) As String
	Dim sendValue As String
	Dim errorCount As Integer
	errorCount = 0
	sendTypes(0) = "Draft messages without sending"
	sendTypes(1) = "Send messages"
	sendValue = ws.Prompt(PROMPT_OKCANCELLIST, "Sending options", "What would you like to do?", "", sendTypes)
	If (sendValue = "") Then
		Exit Sub
	End If

	Dim fileName As String
	Dim strXLFilename As String
	'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], [cc], [subject] set the appropriate fields
	'Make sure the first column does not have any blank cells
	fileName$ = ws.Prompt(12, "Select file", "3")
	If fileName$ = "" Then
		Exit Sub   'Cancel was pressed
	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()

	Dim varXLFile As variant
	'Get data from the spreadsheet
	Set varXLFile = CreateObject("Excel.Application")
	varXLFile.Visible = False
	Dim varXLWorkbook As variant
	Set varXLWorkbook = Nothing
	varXLFile.Workbooks.Open strXLFilename
	Set varXLWorkbook = varXLFile.ActiveWorkbook
	Dim varXLSheet As variant
	Set varXLSheet = varXLWorkbook.ActiveSheet

	Dim lngRow As Integer
	Dim columnNo As Integer
	Dim token As String
	Dim value As string
	lngRow = 2
	Dim maildoc As NotesDocument
	While (Not (varXLSheet.Cells(lngRow, 1).Value = ""))
		'Fill in the template
		Dim subject As string
		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			

		'Count the number of fields
		'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
			If (token = "[subject]") Then
				maildoc.Subject = value
			End If
			columnNo = columnNo + 1
		Wend
		On Error GoTo save
		If (sendValue = sendTypes(0)) Then
			Call maildoc.Save(True, False)
		Else
			maildoc.SaveMessageOnSend = True
			maildoc.PostedDate = Now()
			Call maildoc.Send(False)
			Call maildoc.Save(True, True)
		End If
		GoTo nextrow
save:
		MessageBox("Error processing " + maildoc.sendTo)
		errorCount = errorCount + 1
		Resume Next
nextrow:
		lngRow = lngRow + 1
	Wend
	If (sendValue = sendTypes(0)) Then
		MsgBox "Drafted " & (lngRow - errorCount - 2) & " message(s). Errors: " & errorCount
	Else
		MsgBox "Sent " & (lngRow - errorCount - 2) & " message(s). Errors: " & errorCount
	End If
	Call varXLFile.Quit()
End Sub

Custom fields in Lotus Notes / Domino? You may need to set the SUMMARY field flag

It took me a few hours to figure out that I needed the SUMMARY field flag, so I thought I’d save you the struggle if you ever need to create a view with custom columns.

I was trying to create a view that showed the custom fields I’d added to an RSVP message: RSVPCampaign, RSVPCode, and other details. I had created a Lotus Notes button that sent me an e-mail with the information in hidden fields. I successfully processed the extra fields using a LotusScript agent. I wanted to create a view that showed the results so that I didn’t have to keep clicking on the button and checking my mail. Using a view would make it easier for me to share these tools with other people, because I figured out how to create a button that creates a view.

I couldn’t figure out how to get my custom fields to display, though. They showed up in @DocFields, but @IsAvailable(RSVPCampaign) was always false.

I read about all of the functions in the Formula language. I experimented with @GetField, field names, and other ways to access data. I dug through documentation and websites (most of which assumed people already knew things like this). Frustrated, I opened the properties dialog and started systematically going through the fields on my RSVP messages.

It took me several passes to notice that the regular fields had “Field Flags: SUMMARY” and my custom fields didn’t.

After some quick searching, I realized that I needed to set the summary field flag. So I rewrote my RSVP mailing function to include lines like this:

Dim item As NotesItem
Set item = New NotesItem(doc, "RSVPCode", code) item.IsSummary = True

I tested my new RSVP mailing button, and my view worked!

So if you’re stumped because your custom fields don’t work in view selection formulas or in column formulas, set the summary flag on the field when you set the field, and you should be good to go.

I wonder how I could’ve learned that faster. Reading other people’s source code would help. Forums are useful, too. Part of it involves picking up the jargon so that I know how to phrase my searches. Anyway, I fixed it! =D

LotusScript: Checking another database for categories that do not contain a document of a particular type

We want to scale up Innovation Discovery and share the insights/workshop methods with more people, so one of my tasks is to remove sensitive information from our workshop output documents, post the scrubbed output documents in our community, and update the relevant sector page in our wiki.

This would be easier if people notified me after engagements, but at least we’ve gotten people into the habit of adding files to the Teamroom. I decided that instead of asking people to remember one more step in our post-engagement process, I would just regularly get into the habit of checking the Teamroom for updated documents. The Teamroom date view is useful, but there are other documents mixed into this, and I don’t think I can get my team members to adopt a consistent naming scheme or document type. However, if I wrote an agent to tell me which client categories didn’t have a final output document entry yet, I could use that to find new entries and follow up on old ones. So I did.

I didn’t have access to create new agents in the Teamroom database. I worked around this by creating this agent in my own database and then connecting to the other database from there.

Sub Initialize
	'This script looks for all the client categories that do not have a final documents entry
	'Display the current document's Categories field
	
	Dim dbID As New NotesDatabase("","dbom1\global18.nsf")
	Dim doc As NotesDocument
	Dim catView As NotesView
	Set catView = dbID.getView("CategoryLookup")
	'Determine list of clients
	Set doc = catView.Getfirstdocument()
	Dim clients List As String
	Dim finished List As String
	Do Until(doc Is Nothing)
		Dim category As String
		category = doc.Getitemvalue("Categories")(0)
		If (InStr(category, "Clients") <> 0) Then
			'This belongs in the client category. Has it been found? Add it to the list
			category = Mid(category, 9)
			clients(category) = category 
			If (doc.GetItemValue("DocType")(0) = "Final output") Then
				finished(category) = category 	
			End If 
		End if
		Set doc = catView.Getnextdocument(doc)
	Loop
	'Remove completed items
	Dim s As String
	s = ""
	ForAll client In clients
		If (Not IsElement(finished(client))) then
			s = s + client + " "
		End if
	End ForAll
	MessageBox(s)
End Sub

I change entries to the “Final output” document type after I’ve processed them. So far, so good!

Troubleshooting my Lotus Notes 8.5.2, Expeditor 6.2, and Eclipse 3.4 setup

SCHEDULED: 2010-07-21 Wed 08:00

To paraphrase Edison: I wasn’t failing, I was just figuring out a thousand ways that didn’t work. =)

—-

Summary of troubleshooting lessons learned for Lotus Notes 8.5.2, Expeditor 6.2, and Eclipse 3.4:

org.eclipse.equinox.common problems when installing Expeditor Make sure you have the version of Eclipse that matches your Expeditor’s system requirements (not a newer version, not an older version). For Expeditor 6.2, you’ll need Eclipse 3.4.

Problem occurred reading your Target. Ensure that your Target Platform’s Location is configured correctly. Set it to c:\notes\framework\rcp\eclipse, or wherever your rcp\eclipse directory is. If you still get the error, tinker around a little or wait a while. I don’t remember what I did to solve this.

Bundle com.ibm.jxesupport not found. Ignore that. You’re supposed to be able to correct that issue by right-clicking on the project, selecting Properties > Client Services, and clicking OK, but no luck. It doesn’t stop the system from moving forward, though.

com.ibm.rcp.platform.personality error or java.lang.SecurityException: Unable to locate a login configuration: *Enable all the features and be patient.

—-

I’ve been working on getting a Lotus Notes + Eclipse development environment so that I can make a Lotus Notes plugin for my community tools. There’s a lot of interest in the community metrics tool, for starters.

The challenge with setting up development environments is getting all the versions to line up with the tutorials on the Net. I came across a page that described how to set up Lotus Notes 8.5.1 with the Eclipse Plugin Development Environment (PDE). I was on a newer version of Eclipse, so I needed to figure out a couple of the steps, and I eventually ran into a security exception with login configurations.

Along the way, I came across Lotus Expeditor and decided I wanted to try that. I saw an old article that said Expeditor only works with Eclipse 3.2.2 and not the newer versions, so I installed that, but it had problems trying to find com.ibm.equinox.common. Then I found out that I had a newer version of Expeditor which requires Eclipse 4.0. When I installed that, Expeditor installed fine.

Lesson learned: Look up the version of the toolkit you’re using. Look up the specific software requirements for that version. Match it instead of using newer versions.

Hmm. New error: Problem occurred reading your Target. Ensure that your Target Platform’s Location is configured correctly. I have it set to c:\notes\framework\rcp\eclipse. It won’t accept c:\notes\framework\eclipse . Hmm. It works now. I don’t know what I did, though.

I’m running into the com.ibm.rcp.platform.personality error again. Let’s try reloading those. They show up in the plugin list for the run configuration, though. Ah. Selecting another plugin that depends on that plugin might’ve done the trick.

There’s a note about Bundle com.ibm.jxesupport not found. com.ibm.jxesupport was removed in Lotus Expeditor 6.2.0. You’re supposed to be able to correct that issue by right-clicking on the project, selecting Properties > Client Services, and clicking OK, but no luck. It doesn’t stop the system from moving forward, though.

… and we’re back at the java.lang.SecurityException: Unable to locate a login configuration which I encountered this morning.

Okay. What do I know about this error?

  • Maybe I’ve configured the wrong JVM.
  • Maybe the JVM can’t find lib/security/java.security .
  • Maybe there isn’t one by default in Notes, so I have to create it.
  • Maybe the classes aren’t in the classpath.

Aha! Found someone with the same error message, but in a different language. The person reported that checking all the boxes in the plugin tab helped. Let’s try running it with all the features enabled (oh my). Lots of warnings, but still going… And there’s the Lotus Notes login dialog, and the sample QuickNote plugin. I think we have it!

Useful links:

Sample code for allowing drag-and-drop of Notes/Domino documents (including email) to a table in a plugin

Because I had to piece this together from examples on the Internet, and probably other people do too:

Transfer[] transferArray = new Transfer[]{
    XMLTransfer.getInstance(),
};
tableViewer.addDropSupport(DND.DROP_DEFAULT | DND.DROP_COPY | DND.DROP_MOVE | DND.DROP_LINK,
    transferArray, new DropTargetAdapter() {
        public void drop(DropTargetEvent event) {
            TableItem item = (TableItem) event.item;
            // You can access the object with item.getData()
            try {
                NotesThread.sinitThread();
                Session session = NotesFactory.createSessionWithFullAccess();
                if (event.data instanceof URIDescriptor[]){ 
                    URIDescriptor[] droppedURL = (URIDescriptor[]) event.data;
                    for (int i = 0; i < droppedURL.length; i++) { 
                        URI uri = ((URIDescriptor) droppedURL[i]).uri;
                        Document d = (Document) session.resolve(uri.toString());
                        // Do things with the document
                    }					
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                NotesThread.stermThread();
            }
        }});

Use session.resolve instead of db.getDocumentByURL to retrieve a document from a plugin, as both session.getAgentContext() and session.getCurrentDatabase() will return null.

Welcome, listeners of the Taking Notes podcast!

(If you haven’t listened to this morning’s podcast where Bruce Elgort and Julian Robichaux interviewed Luis Benitez and me about Lotus Connections, check it out – it’s about 40 minutes long.)

I’m a tech evangelist, storyteller, and geek in IBM Global Business Services. In addition to helping organizations learn more about emerging technologies through executive workshops, I build software that makes people’s lives better, like the Lotus Connections tools people have been using to help with community adoption. (Newsletters, metrics, data export, etc.)

More later, but you might be interested in:

Have fun, and leave a comment if you want to learn more or if you want to share any tips!