Lotus Notes mail merge from a Microsoft Excel spreadsheet
Posted: - Modified: | lotusUpdated May 6 2010 – added Call varXLFile.Quit()
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). “<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
Wend
Call maildoc.Save(True, False)
lngRow = lngRow +1
Wend
Call varXLFile.Quit()
End Sub
33 comments
Travis
2010-01-14T23:17:09ZHmm... What version of Notes does this work with? I get "Query is not understandable."
Beth
2010-01-25T23:50:01ZI 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!
Martin
2010-02-10T18:09:13ZThank 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
Gary
2010-02-17T14:39:23ZThe 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?
Phil
2010-04-16T22:48:47ZHi Sacha
Excellent work - I love it!
Even managed to create the intial email with attachments and inserted my standard signature with logo AND the mail merge created the emails to the recipients complete with all items! FANTASTIC.
For a person with no experience in scripting and basic lotus usage, it was only about 2hrs of mucking about and googling for other lotus info before I liked what I saw.
Biggest problem was setting the agent "runtime" to "action menu selection" and target to "all selected documents". Then had to realise that when an item was selected, you had to go to "actions" on the top bar and look for the agent name in the list.
Is there a way to have the subject line as a field in the xls that can be customised per row? ie. each email can have a unique subject if desired?
And......... is there a change that can be made to allow the email messages to be send immediately they are created?
They would be even more fantastic!
Regards, Phil
Phil
2010-04-16T23:50:42ZHi Sacha & others
Just figured out how to make it do the send immediately rather than save to draft.
4th line from the bottom, replace
Call maildoc.Save(True, False)
with
Call maildoc.Send(False)
It worked for me. No matter what I tried I could not get the messages to be shown in the "sent" folder. Any ideas?
Regards, Phil
viC
2010-05-03T18:43:01ZDear Sacha and Phil, and all the others.,
thank you for the great script and the other tips, i successfully implemented the script in 1-2 hours. I had no experience with Lotus scripting in the past.
Pascal crb
2010-05-05T12:35:27ZThanks Sachua,
As a non-developer, it took me some time to figure out how your script had to be processed, but eventually I was thrilled about the way it helped me send useful personalized messages while saving a lot of time (also for the future !).
Lesson Learned during this experience:
- activate the agent while draft memo selected ... in the view list of Drafts (not on the open document !)
- for empty values in the Excel table, the variables were not replaced in the memo
remedy => before applying, replace all 'blank' cells with e.g. a dot "."
- in order to have the (tested/proven) mails sent without passing through the Drafts-folder, use the Send command rather than Save:
remove:Call maildoc.Save(True, False)
add: Call MailDoc.SEND(False)
What I would like to learn:
- how to keep 'evidence' of the sent mails ?
e.g. by having them saved in Sent (or even some self-chosen) Folder
All by all, Sachua, I'm VERY greattful that you share your knowledge with the world.
Look forward to learn more from you,
Pascal
Pascal crb
2010-05-05T13:15:15ZHi Phil -- and any other contributor to this great sharing platform !
I actually had the idea as well to use some customizable Sublect-line ...
Here is how I did it;
1./ In Excel I added a column titles , and entered a formula to create personalized subject-lines
2./ In the script I added the following lines below the similar ones for and :
If (token = "<subject>") Then
maildoc.Subject = value
End If
l share your desire to be able to keep the sent messages in Sent (or any other) folder.
Pascal
Phil
2010-05-05T23:24:43ZHi all
Prior to may last day working at my employer where LN was used, I did manage to get emails to be sent directly using the agent and with have the emails appear in the sent folder.
Unfortunately it all happened on the last day and I no longer have access since I left the company.
If my memory holds out from the farewell drinks session, I picked up some ideas from some email threads and remember that it's related to the following:
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now()
MailDoc.Send 0
There is something peculiar about the order of the statements and parameters - just can't remember!
With a bit of googling and trial and error I am sure someone will get it.
I will ask a former colleague to see if they can get the details from my old laptop . . . . . . If IT have not wiped it.
However I was able to make my Lotus;
(1) save all emails to drafts
OR
(2) send immediately with emails appearing in sent folder
I just commented out the relevant lines depending on what I wanted to achieve. Typically I used (1) to ensure the emails looked okay and then deleted them from drafts and implemented (2) to do the sending and appear in the sent.
Sorry not more help but hopefully my colleagues can get my agent code back to me.
Regards, Phil
Phil
2010-05-05T23:48:44ZSorry all for additional post,
Whenever i ran the agent the xls file would always be open after the agent finished and I would have to use task manager to close it. Could never figure it out and it bugs me! I know my old colleagues were wrapped to have a mail merge for LN and would like to overcome this problem.
Doug Fisher
2010-05-27T00:42:55ZEverything is very useful, Thank you so much for all the effort you put into this.
Unable to get field for body in Excel to populate in the body of the message.
Tried, if (token = "[body]") Then
maildoc.body = value
This runs with no errors but body does not show in message.
Any suggestions. I have no experience with Lotus notes scripting.
Sacha Chua
2010-05-27T02:19:42ZThis script uses the body from the selected message, replacing any tokens with the ones from the spreadsheet. Create a message with the body you want, select it, and then run the script.
Sacha Chua
2010-05-27T04:01:39ZPhil: Check out the new version for even more awesomeness. =)
Toyin
2010-06-21T18:58:58ZHello Sacha:
This is beautiful but I need extra help and questions to continue...
I have Lotus 8.5 at work and it is basic. Can I still do this if I use the script you provided?
Number 2: I am very new to the language of coding.. where and how do I create the agent? How do I call the agent? Is this done in Lotus Notes?
Thanks
Sacha Chua
2010-06-21T20:33:25ZYou might want to install the full version so that you have the designer. With Basic, you can probably create the agent using the Create > Agent menu item. You should then be able to run the agent from the Actions menu. Hope that helps!
toyin
2010-06-21T20:43:44ZThanks Sacha:
After poking a little bit more, I found out the Create Agent link on Lotus Notes but, it was disabled...
gotta talk to IT helpdesk to see if they can install the full version on my computer.
Thank you!
toyin
2010-06-21T23:21:57ZAight Sacha..
making it to the promised land litte by little... I was able to create the agent, create my email template and even did a test with a small excel file... if I understand your instruction right, the excel file should have a header followed by the emails.. here is what I have:
[To][Cc][Subject]
pretty@yahoo.comTesting
bibii@travel.comTesting
It saved the email to the draft as that was the option i asked for.. but the subject line and to line were empty...
Sacha Chua
2010-06-22T03:17:31ZAha! That's because you want the new version of the script, which uses [to], [cc], and [subject]. (Case might matter, too.)
mo
2010-06-29T01:14:06Zfrom above:
"and call the agent while the e-mail is selected"
how do you do this, please?
Sacha Chua
2010-06-29T15:47:02ZIf you look in your Actions menu, you should see the agent you've created.
mo
2010-06-29T17:28:37ZI have an email in my drafts folder that I want to be the email the agent uses. I only want to add the addresses to this via the agent. They are in the excel file.
How do I get my draft to be this, from above:
"create my email template"
??
Thanks for any and all help!
joey
2010-08-07T00:44:20Zthank you so much for this.... I Just happened onto this thread and it really helped me out!
Donna Horowitz
2011-05-20T19:45:15ZLove this agent to death, SO useful for my job. Is there a way to include more than one recipient on the cc line other than concatenating two addresses in the excel input file?
Sacha Chua
2011-05-20T21:18:49ZDonna: Concatenating is the way to go. But you should check out David Turner's improvements - he might have a better way to handle it! You might find this StringConcat VBA function helpful.
Donna Horowitz
2011-05-20T22:29:09ZThanks Sacha -- I'll be using it again this afternoon, and will go the concatenation route :). Wonderful, wonderful tool. Your co-worker at Big Blue, Donna
Ernesto Cassavia
2011-08-24T22:43:46ZGreat tool!!!
Thank you so much!!!!
You might also want to have a look on it too. http://www.openntf.org/Proj...
Sacha Chua
2011-08-25T03:54:46ZGlad it's working for you. =) Thanks for dropping by!
Sachin
2013-06-03T13:35:10ZHi, this is superb. But facing one problem. Its not attaching the attachments. ANy thing i should crosscheck?
sachac
2013-06-04T00:35:44ZI'm no longer using Lotus Notes (since I'm not at IBM), so unfortunately I can't help you with that. Good luck, though!
Katie
2014-07-09T14:54:54ZThis is great, but I am having one issues with it, when I run the agent the brackets are not being removed in the email...how to fix this?
sachac
2014-07-09T15:32:11ZDoes your spreadsheet also have <...> in its column labels, like <to> and <cc>? I think it just does a straightforward search and replace.
Katie
2014-07-09T16:51:26ZThank you, I got it. For me, the "to", "cc", and "subject" have to have brackets [ ] and the other fields need the direction signs < >...wierd, but it worked! Great script thanks so much!