Lotus Notes mail merge from a Microsoft Excel spreadsheet
Mwahahahahaha!
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). “
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
Wend
Call maildoc.Save(True, False)
lngRow = lngRow +1
Wend
End Sub
(No Ratings Yet)
Loading ...
OK, way to technical for me, but I am glad you figured it out!
I take it that there was no UI-based way to do a mail merge in Notes?
D.
Doesn’t seem to be built-in, but that’s okay. Emacs Gnus didn’t have it built-in either!
As they like to say on The League of Super Evil: Victory. Is. Yours!
Great work (and I am so stealing this code
)
BTW, can you provide a sample of what the email looked like (even with Lorum… text)?
Just realized that the first part, where we assign personCounter etc., is completely unnecessary now that it’s all so flexible. =) Pardon the bitrot!
thanks – this just saved me hours of work
Hi,
Pleased to get your blog about mailmerge using lotusnotes and XL.
I tried your script but I couldn’t get anything there…no popup comes asking source for XL sheet when the agent runs.
What are other settings do I need to do?
On my system, Outlook is default mail program now.
Please help!
Hi sacha chua,
I have solved my issue now and I am all set .
Thanks a lot for sharing this script.
-Ranjeet
I tried your script. I will receive the mails in the draft folder but they all miss the recipient’s e-mail address. Seems I am doing something wrong here. Can you help me out please?
Sacha
I’m a Lotus Notes novice (but quite good with vb scripting)- how do I ‘Create the agent’ ?
You’ll need Lotus Notes with Designer installed. If you have that and then view your mail database, you should see a Create > Agent menu item.
Hi,
I found a way of doing this from outside Lotus Notes using VB Script. I’ve posted it on Microsoft’s Script Centre :
http://gallery.technet.microsoft.com/ScriptCenter/en-us/fe141119-9599-46a7-90ca-8dbc66d50297
Regards
Michael Green
Thanks very much for this Sacha!
I’ve yet to check if I do have the LN capability for this but anyhow…very useful stuff! 
Hi Sacha
After the mail merge, the email will saved in Draft folder. Do you know a how to automate to send it one short?
Or I have to go “edit” to click send individually?
Thanks
Regards
Yuan Ling
I’m seriously grateful to you for saving me hours of pain. Thanks a ton!
Glad you like it! =D Took me a while to figure it out myself.
Bernie:
Sure! It looks like
Hello, <name>! You’ve just won $1,000,000 in the <country-adjective> lottery. Claim your prize by contacting <fake-address> before <random-date>.
<official-sounding-name>
<official-sounding-position>
<official-sounding-agency>
—
The delimiters are totally arbitrary, as the spreadsheet just looks for the text used in the columns. You could even use non-delimited text (ex: pony), if you were careful about substrings. =) <to> and <cc> are the only ones that are special.
Hello Sacha
Many thanks for making this available. I am Lotus novice too and was greatly happy to see this. I have tried performing the task. For me, draft emails are getting created in the drafts folder, but everything is blank (To, CC, Subject, Mail Body etc.) My Excel file contains column To, CC, Subject, Body (as first row, second row onwards has data). I think I haven’t understood the “tokens” stuff you mentioned.
Can you (or someone who reads and can) help?
Thanks in advance
Hello Sacha and others
Refer my earlier post. I tried it little more and realized that the script works just fine. I had not included the column headers in Excel between the brackets ( ). Once included, it works just fine.
Thanks a lot, it means much to someone like me who has recently started working on Lotus.
Pankaj: You’re welcome!
The headings are very flexible. <to> and <cc> are the only ones that are fixed, but you can use anything else for the other column headers (ex: |MESSAGE|, and so on).
Glad you found the code useful!
Hmm… What version of Notes does this work with? I get “Query is not understandable.”
I do not understand what you mean by agent? Does agent refer to a new module?
I am also getting an error that says that the user defined module is not defined (“ws as New NotesUIWorkspace.
Thanks for posting this!
Thank you for the script its perfect a part from one thing.
We have a button enbeded into the email we want to send. When the script pushes to the drafts thats fine the button is still visable and works however once we send it out when it reaches the other end the button has been removed although the attachment we also have is still there any ideas? Its the last part and really frustrating that it only removes the button once the person gets is.
Cheers
Marty
The script was useful! Really thank you for making it available!
I do have a problem though – my ‘master mail’ contains the percentage % sign.
After applying the Agent, the draft mail displays the % sign as garbage.
Is there a way around this?