Even more awesome LotusScript mail merge for Lotus Notes + Microsoft Excel
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 (sacha@sachachua.com)
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
Short URL: http://sachachua.com/blog/p/7189
This is very cool. You should consider posting to openntf.org.
Hi Sasha,
Thank you for providing your script to perform a mail merge between Excel 2002 and Lotus Notes. I have somehow, through blind luck and good fortune managed to follow your instructions most of the way through, however, it is not putting the To field in.
Any tips? I’ve tried putting To and [To] in the relevant field in the saved email draft, also renamed the column in Excel to “To” and “[To]“.
Thanks,
Anne
Don’t worry – it’s all sorted …
Hi Sasha,
I created a mail template like you said, but with some formatting.
When I run the agent however, the formatting is lost. Is there anything that can be done to preserve the formatting, or am I just doomed to using blank and ugly text?
Thanks in advance,
Regards
Yusuf
Yusuf: It should keep the formatting in the original message (bold, italics, etc.), but it ignores any formatting in the Microsoft Excel spreadsheet. Make sure that when you format the text, the entire field token (ex: [message], if that’s your column header) is formatted the same way.
Sasha, the formatting I’m talking about is in fact the one from Lotus.
Let me try again:
1. I’ve created a memo in Lotus, with a message that is to be the same for all my recipients. I have formatted this very memo in the way I want it to appear, with my fancy signature and all.
2. I create an Excel file in which I put addresses in one column for which I put [to] as header and nothing else, since I don’t need any other field.
3. When I run the agent, I get to choose the Excel file all right.
4. Memos are created for all the addresses, but without the formatting I originally put in my memo :(.
So now, when you’re talking about the field token, which I supposed was in the Excel file, it does not really apply to my case, right? Or have I misunderstood something?
Thanks very much for your comprehension,
Yusuf
Hmm, works for me. Might be a Lotus Notes version difference? I’m on 8.5.2. Perhaps you could try the older version of my script? http://sachachua.com/wp/2009/07/lotus-notes-mail-merge-from-a-microsoft-excel-spreadsheet/
Ah, right; I’m using version 7.0.
Anyway, we were able to find another workaround (using Outlook instead), but I did learn many things about Lotus Notes when I did my research on this!!
Thanks for your help,
Yusuf
Hi, how do I put the email address into the ‘To’ field?
It’s ok, I got it all sorted out.
Hi Sacha:
This appears to try to work but when I go to import the emails from the Excel file, I get a Lotus Notes error box that says, “Type Mismatch in method CheckOperand: Unknown found, Unknown Expected”
I’m on Lotus 8.5. I tried your old script which did not work at all.
Any ideas? TIA
Hmm, at that point I’d probably try debugging the LotusScript and stepping through it to see which line fails…
I did run the debugger. I got no errors, but in using the script it does the following:
It will create draft messages and tell me X messages created and Y errors (which is correct) and save them into the draft folder. However, the ‘to’ address is not populated and hitting ‘send’ gives me the error (correctly) that there is no addressee.
If I choose “send’ rather than draft, it gives me the mismatch checkoperand error window. So it appears is is not reading the Excel file correctly, even though it asks to open it.
I hate Lotus…
Ah. At that point, I’d probably doublecheck the Excel file to make sure you’re using [to] (lowercase) instead of [To] (uppercase), and that’s with this new version here. (It’s <to> in the old version, I think.)
That was exactly the problem!
Thanks so much for your patience and your suggestions.
I work in marketing and the ability to email our database was critical! Somebody at IBM ought to pay attention to how people use emails!
You’re welcome. I’m delighted that it works for you! I’ve heard from people in HR and other areas, too. Definitely a feature that should be built into the system. =)
how do you hook the agent up with the email?
how do you hook the agent up with the email I have saved as a draft? I have the addresses in an excel file and I want to fill the names into the email. thanks.
Have one row per address, and use [to] as the first line in the spreadsheet.
Yes, thank you. I understand the excel side of this and the agent seems to be accessing the excel file ok.
I am ignorant, however, on the selection of the email message itself. I have an email in the Drafts folder that is the one I want to use for this project. How do I get it connected with this agent?
I see posts about a memo template. Could you direct me to how this is created and then linked with this agent?
Thanks very much for any and all help.
mo
Click on the message to select it. Then use Actions > (your agent) to run your mail-merge agent. It uses the currently-selected message as a template, no need to create stationery. =)
This was exactly what I needed.
It works wonderfully! Far Out! How cool is this. Thank you so much.
Best of luck to you.
PS congrats on upcoming marriage.
One more thing. Sorry to be demanding :)
Is there a token to change whom the email is from by chance?
I’m delighted to hear that it works for you! =)
I haven’t looked into changing the To header yet, and I’m not sure if you can do that with Lotus Notes. I suppose you could add a “Sent on behalf of _____” or teach someone else to run the script if needed…
p.s.: Thanks! =D
Hello. I love this subroutine to automate mail merge into emails. Thank you.
I am stuck however in one place.
I cannot get a value from my spreadsheet for the subject token.
My subject comes up blank in the email rather than reading a value from the spreadsheet.
Any idea why this is happening? I do have the token heading matching what is in the script.
thanks.
Never mind my last post about subject! It was my error. I had a blank column in before the [subject] column in the xls Thank goodness for debuggers to point out the obvious when I am blind. Take care. Good luck always.
I’m delighted it worked out! Thanks for sharing. =)
Hi,
I placed multiple email addresses in one [To] cell (e.g. 123@abc.com, 1234@def.com) and my rich text email became plain text with embeded images. Can anyone help resolve this?
Thanks
Hi Sacha,
I have a need to enter multiple email addresses in the cc line, and they are in separate columns in my Excel sheet. Do you have any suggestions on doing this using your mail merge tool?
Thank you,
Sarah
Make an Excel column that concatenates the other columns together, perhaps?
Hi Sacha,
I love your script, and it is mostly working, but I am having one issue.
I have three different people using this script, including myself, and it works fine for drafts on all 3 machines, but one of the machines is unable to send the messages, while the other two have no issues.
I am getting a type mismatch checkoperand error. It seems to be going to the error when I get to Call maildoc.Send(False).
Any ideas?
Hi Sacha,
I’ve got your script working on LotusNotes v8.5.1 and Excel 2007 with only one small issue causing concern… the script does not terminate the instance of Excel when it is complete. After running several trials of the script I noticed that I had multiple Excel.exe in the Task Manager. The number of instances grows with each use of the script.
What do you suggest?
Mark
Mark,
I had the same problem.
Near the end of the code.
Call varXLFile.Quit should be Call varXLFile.Quit()
Sacha,
I am trying to use this great agent, but when I send to users that only are on Yahoo mail, Plain Text or HTML, they get only plain text. When I use the draft, it formats OK, but if I use the send option the message becomes plain text. The draft memo I am starting from was created as HTML from our marketing dept.
I have tried to modify your code to support html, but without success.
Any ideas?
Vance, thanks for sharing the fix! I’m not sure how to work around the richtext/HTML problem with Yahoo mail, but I’d probably try to debug it with the following sequence:
- Use Lotus Notes to mail the HTML memo to the Yahoo mail address, to see if it’s a Lotus Notes problem. If it’s okay,
- Copy the message body from the draft into a new message and mail that to the Yahoo mail address, to see if it’s a problem with the way the message is drafted. If that’s okay,
- Use the draft option, then send it from the Draft folder, to see if it’s a problem with the send option.
There’s a passThruHTML property in LotusScript, but I’m not sure if that’s useful here… Might need more of an expert than I am! =)
Thank you so much for posting this. I was trying to develop a simple database for tracking and mailing at my work but frustration with our version of Notes not allowing import of excel spreadsheets finally turned me to the internet. Your solution is brilliant, simple and elegant and does just what I need. I appreciate so much your work and willingness to share. Saved me a lot of time and headache.
And I’ve enjoyed the rest of your blog as well. Keep it up. :-)
Thanks again.
Victor: I’m delighted it works for you! =) You can pay it forward by sharing it or another technique with other people. Have fun!
I am having a problem with replacing text in the message. Can you off any assistance with adding a personalized saluation in the message. You said something about tokens and using FOO and BAR, but that makes no sense to me. Thank you so much for posting all this stuff. I can’t believe LotusNotes doesn’t have this as an automatic agent.
Katherine: Could you check out Eric Weinberg’s tutorial and see if that helps?
Great information I can really use this in my job. Keep up the good work. I am following on Twitter.
I’m glad you found that mail merge script useful! Thanks for sharing and for reading. =)
Hi! to send automatic emails I do this:
http://runakay.blogspot.com/2011/03/i-need-to-send-automatic-email-with.html
Thank you for making the script. It worked like a magic. Just a little thought, is it difficult to make different attachments for different recipient?
PS: Your captcha is very difficult to read… :)
Hi Sacha, I’m trying to do a mail merge from Excel into Lotus 8.5.10 and following your blog, script, and the tutoral from Eric, when I create an Agent, Lotus immediately brings up the fields to place the information in, but does not allow me to Save the Agent. When I close out, I’m prompted to Save or Delete, but then I don’t know where it saved. At the final step, Create Mail Merge, I do not have a Mail Merge option under my Create menu. Is there something additional I need installed for this to be available? Thank you for your quick response. I’m looking to merge 350 emails by Friday to distribute.
Kari
Actually, I just figured it out after reading the older post/version from other comments. I still cannot figure out how to save the Agent when created, mine shows as Untitled. Under actions I can select Untitled and it worked perfectly!! Thank you so much for sharing!
Thanks Sacha! – This script worked great for me and saved me a ton of time. Personalized and customized emails are the way to go for me from now on. Should definitely be a standard built into notes.
Hey!
Ran into your wonderful Lotus mail merge script this week on the internet and it has completely made my life so much easier. :)
I wanted to ask you if there was an easy wayt to format the body of the email and have it pull fields such as name, company, greeting, title etc?
I’m no a coder so I was a little hesitant to try and figure it out myself, but with some pointers I’m totally game!
Let me know!
R
Rahaf: It should pick up most of the formatting from the Lotus Note you’re using to draft the message, and the field replacements are defined by whatever you have in the first row of the spreadsheet. For example, if you had Dear [firstname], in your draft message, [firstname] in the first row of your mail merge spreadsheet, and Joe in the corresponding row for your data, it would send a message with Dear Joe,. Test by using draft more or sending a few messages to yourself. Good luck!
Sacha,
This has made my day.
One question, I have access to multiple mailboxes and I have associated the agent with two of them (my personal one and another). What I’d like to do is send the e-mails from the other mailbox but when I do this the messages appear to come from my personal address even though they are stored in my other mailbox.
Is there a way to change that?
Hi Sasha,
many thanks for the code.
I found here:
http://www.ibm.com/developerworks/lotus/library/notes-ole/index.html
That the CreateObject does not work in Mac, do you any workaround? Maybe using Symphony?
Regards,
Oliver
Simply Awesome!!
Thanks a million.
Hi sacha,
thanks for the great script. i;ve been using it for over a year, but recently when i used it again, i had some issues. when i sent the mails to non-lotus notes uses, pictures in draft email template do no show up. instead users get messages like this:
(Embedded image moved to file:
pic26897.jpg
do you know what’s causing the error?
This is great – thank you!
Hi All,
FYI I’ve just released v1.2 of the Mail Merge script on OpenNTF which includes the ability to embed dynamic attachments. Let me know what you think, and feel free to leave any other feature requests!
- Dave :)
This is one of the coolest things I have seen ! Has made my job much simpler. !!
Can’t thank you enough !
thanks ! Worked great for us … nice script
Great script!
I have only one question: if I choose “return receipt” in draft the agent doesn’t include this in later e-mails.
Can this be fixed somehow (we always send mails with return receipt)?
I’m not sure how, but you might be able to modify the script to do so. Good luck! =)
Thanks for the suggestion. This should be straightforward. Curiously, this issue also occurs with OpenNTF project release 1.3, so I’ve created a feature request. Look out for this enhancement in the next release.
Tine, I have implemented your request in the latest release 1.3.1.
All Delivery Options I could find — including: Return Receipt, Reply To, Reply Date, High Importance — will now be copied from the email template to the generated emails.
Let me know if you encounter any problems with this new function!
- David :)
Hi,
I also get this error message when I use the send option:
Type Mismatch in method CheckOperand: Unknown found, Unknown Expected
I have a list with 20 names and it does send 1 to 3 messages before popping the error and then it does not close the excel file.
When using the ‘draft’ options it says that it has created X drafts with no errors. The drafts appear correct with the ‘to’ field correctly inserted and when I press send in the created draft is sends the message with no errors.
…any suggestions?
Hi Peter, there is probably something funny about the data on row 2 or 3 of your spreadsheet.
I would recommend you try the latest v1.3.1 of this script on OpenNTF.org. It includes improved error handling — identifying row, line numbers and fields being processed when the error occurred — which will help diagnose the problem!
Thanks for helping, David! :)
Thanks for this script, it works very good.
I have one more question. We would like to add a button or something like that with a link. If you click on it, it will take you to an online form. In this link we have added some fields. We want one of these fields to be filled in already with the email address when someone clicks on the link. For this we want to use the token [to] (e.g. http://www.test.com/test/field123=to). If we just use the link in the email body it works, because when the mail is sent, the email address will be filled in immediately (ex. [to]). But we don’t want to put the link in the email body (it doesn’t look nice, people can see their email address in the link), we want to hide it behind a button or something like that (a hotspot). Do you have any idea how we can fix this? Because when I use the link as a hotspot it fills in [to] instead of the email address.
Hi Katrien,
Thanks for the query. I’ve been looking into ways of getting a Notes Hotspot or Button or Embedded Image into the generated emails.
My research (and trial and error) suggests that for all versions at least up to Notes 8.5.3, is NOT possible to achieve this in ordinary LotusScript (no native functions for these elements exist, and AppendRTItem behaviour is restrictive). Therefore, barring hacking into Notes using DLL extensions, the solution will probably involve re-writing this agent to use Notes DXL manipulation.
I’ll add this as a feature request on the OpenNTF project and think about this as a major v1.5 enhancement.
[...] finally, never did get it to work in vba, but if anyone else is having this similar issue GO HERE Even more awesome LotusScript mail merge for Lotus Notes + Microsoft Excel | sacha chua :: living an… And thank Sacha Chua for her super awesome mail merge [...]
Sacha, it’s awesome, thank you! I’ve been looking for a solution for long time and all which I found were too complicated for me :) This one is pretty straight forward even for basic LN user as me :)
Anyway, I have a question / problem:
I’ve created a stationary, which I am using when drafting the mails. In the stationary I’ve set the “reply-to” address to the specific one. However when merging the mails the reply to address is not kept on the created mail drafts..
PS: I am using LN v 8.5.2
Any thoughts?
Martin: Good luck! I don’t have access to Lotus Notes any more (the perks of working on my own! =) ), but maybe you can give David’s script a try (see the link at the beginning of the post). Alternatively, you could add a little bit of code to set the ReplyTo yourself. Hope that helps!
Sacha: did try that and failed (I didn’t manage to install it – as I said I am Lama at LN and don’t have the Domino Designer..). I’ll keep looking around for solutions, thanks!
Sacha, David, I across your blog and then the download at http://www.openntf.org
The mailmerge function works great. Thanks for the post and then following it up. Also the documentation was very useful.
How do I attach files while sending the Mail using your Mail merge option..
Also please can u tell the format on the execl file in which I have to create a list of Id to whom I have send the mails..
regards
Salim