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()

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
  • David

    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.

  • http://sachachua.com Sacha Chua

    Doesn’t seem to be built-in, but that’s okay. Emacs Gnus didn’t have it built-in either! ;)

  • http://smartpeopleiknow.wordpress.com Bernie

    As they like to say on The League of Super Evil: Victory. Is. Yours!

    Great work (and I am so stealing this code :) )

  • http://smartpeopleiknow.wordpress.com Bernie

    BTW, can you provide a sample of what the email looked like (even with Lorum… text)?

  • http://sachachua.com Sacha Chua

    Just realized that the first part, where we assign personCounter etc., is completely unnecessary now that it’s all so flexible. =) Pardon the bitrot!

  • benwah

    thanks – this just saved me hours of work

  • Ranjeet

    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!

  • Ranjeet

    Hi sacha chua,

    I have solved my issue now and I am all set .

    Thanks a lot for sharing this script.

    -Ranjeet

  • Sacha

    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

  • Michael Green

    I’m a Lotus Notes novice (but quite good with vb scripting)- how do I ‘Create the agent’ ?

  • http://sachachua.com Sacha Chua

    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.

  • Michael Green

    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

  • Sheryl

    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! :D

  • yuan ling

    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

  • LoutsNotesUser

    I’m seriously grateful to you for saving me hours of pain. Thanks a ton!

  • http://sachachua.com Sacha Chua

    Glad you like it! =D Took me a while to figure it out myself.

  • http://sachachua.com Sacha Chua

    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.

  • Pankaj

    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

  • Pankaj

    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.

  • http://sachachua.com Sacha Chua

    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!

  • Travis

    Hmm… What version of Notes does this work with? I get “Query is not understandable.”

  • Beth

    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!

  • Martin

    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

  • Gary

    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?

  • Phil

    Hi 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

    Hi 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

    Dear 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

    Thanks 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

    Hi 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

    Hi 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

    Sorry 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

    Everything 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.

  • http://sachachua.com Sacha Chua

    This 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.

  • http://sachachua.com Sacha Chua

    Phil: Check out the new version for even more awesomeness. =)

  • Toyin

    Hello 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

  • http://sachachua.com Sacha Chua

    You 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

    Thanks 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
    • http://sachachua.com Sacha Chua

      Aha! That’s because you want the new version of the script, which uses [to], [cc], and [subject]. (Case might matter, too.)

  • mo

    from above:
    “and call the agent while the e-mail is selected”

    how do you do this, please?

  • http://sachachua.com Sacha Chua

    If you look in your Actions menu, you should see the agent you’ve created.

  • mo

    I 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

    thank you so much for this…. I Just happened onto this thread and it really helped me out!

  • Donna Horowitz

    Love 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?

  • http://sachachua.com Sacha Chua

    Donna: 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

    Thanks 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

    Great tool!!!
    Thank you so much!!!!

    You might also want to have a look on it too. http://www.openntf.org/Projects/pmt.nsf/projectlookup/MailMerge%20Excel%20to%20Notes

  • http://sachachua.com Sacha Chua

    Glad it’s working for you. =) Thanks for dropping by!

  • Sachin

    Hi, this is superb. But facing one problem. Its not attaching the attachments. ANy thing i should crosscheck?

    • http://sachachua.com sachac

      I’m no longer using Lotus Notes (since I’m not at IBM), so unfortunately I can’t help you with that. Good luck, though!

  • Derek Scott

    test