PreviousNext…

Mailmerge from NAB to MS Word

Background

I’ve had some code knocking around for ages which links the personal name and address book up to MS Word. I originally put a database together in the days of Notes 4.5x which linked up with Word 97. It allowed the export of selected “Person” documents from one’s PNAB to Word. The code created a temporary mailmerge data source for Word to work with, and added a custom menu to Word with some options to work with the mailmerge data. It also fired up Word using Shell. Looked like this:

Screenshot of custom NAB Mailmerge menu in MS Word

Original solution

So, the original database. How did it work?

  1. Paste in Person documents from one’s PNAB
  2. Selected documents have a mail-merge agent run over them
  3. This generates a temporary Word document which acts as a mailmerge data source
  4. The agent also automagically detaches some MS Word files to appropriate locations on the client machine. In the version we did, one document was a simple help file in Word format. Two of the others were templates for use by the mail-merge set-up (labels and badges as in the screenshot above), and the fourth was a template designed to go in the Startup directory. This startup file is important, and needs to contain the VB macros and menu definitions for the “NAB Mailmerge” option.
  5. The fifth and final file detached by the database was a tricksy one in our implementation. We decided it would be nice to have a “Remove this feature” option in the Word menu. However, the code for doing that is instantiated by one of the files that should be removed, i.e. the startup template! How to work around this? Well, courtesy of erstwhile colleague Spikey Mikey, we came up with this: instead of placing the key template in the Startup directory, the code places it in the Templates folder with all the rest. The program then detached an extra file, a Windows shortcut (an alias of said template) to the Startup directory.

Now, for the purposes of this article I looked at the old code again. It worked, but it was mighty clunky. In addition, coming up with a generic solution poses a few issues.

The problems

Over the past couple of evenings I’ve tinkered with much of the Lotusscript (it was originally all in one long painful sub-routine), and have changed the way Word was fired up (I now use COM). After looking at everything I decided simply to release snippets of various pieces of code rather than a full solution. There are a number of reasons for this, not least the fact that I’m conscious that with things like this, one size does not fit all (you may want an entirely different custom menu in Word, perhaps with additional templates and so forth). So here are some of the coding / design issues I faced:

  1. I want paths to the MS Word “Templates” and “Startup” directories to be configurable
  2. I want the COM activation of Word to be version-agnostic
  3. I want the MS Word templates to be easily edited and configured
  4. I want the mailmerge options to be easily removed in Word
  5. Windows shortcuts are extremely inflexible, and have a Target attribute. Linking this to the final location of the startup template is a faff when you want to be loosely-coupled!

Don’t give me problems!

  1. This bit’s easy: the shell database I have contains a configuration document containing the Word files, so it’s easy to have the LS read extra parameters from that should you wish
  2. Ewww. I’ve talked about this before. Unlike Excel, Word requires activation like this: Set app = CreateObject("Word.Application.XX"), where XX is a version number (“10” = Word XP for example). This versioning is a pain, but again, could be configurable.
  3. The real pain. Not insurmountable, but a pain all the same. And the main reason I decided to just get what’s done out now rather than hack away with this. If you need a complete solution, hopefully this article gives you the building blocks.
  4. See above; we have a mechanism for this, courtesy of me ole’ mucker Spikey Mikey. It involves some weird detaching code in Lotusscript as attaching shortcuts works differently in Notes 5 versus Notes 6. More on this later.
  5. Again, can be sorted. I just didn’t feel the need for the purposes of this article.

OK, OK, the code

So, rather than give out a finished database, the attached NSF instead contains a number of pieces of code for you to tweak to your heart’s content, There are specimen routines for detaching the files, extracting the mailmerge data, and so on. Paste in some Person docs, and the mailmerge code will export them to a file called c:\tempmerge.doc — you can change the path in the code of course. There are also some example VB macros for inclusion in your Startup template detailed in the attached VB file.

Opening Word with COM

Almost forgot. Just a few lines:

Dim App As Variant
Set App = CreateObject("Word.Application.10")
If App Is Nothing Then
  ' // Doh!
  Exit Sub
Else
  App.Visible = True
End If

I hope this is of use, especially to those who’ve mailed me for this article, keeping me on my toes! ;-). Please note that I have only tested this database with Notes 5-style contact data, I haven’t tried this with contact data taken from an ND6 name & address book, so your mileage may vary.

Downloads

Comments

  1. This might be of interest for people who require a single-document printing and reporting:

    The standard WinWord automation solution for this is based upon bookmarks. However there are some drawbacks when using this method.

    1. Bookmarks come in two flavours: position (standard) and range. Unskilled users tend to use the standard when creating a template so when updating the contents of the document text is appended after the position bookmark, not updated.

    2. Bookmarks names suffer from name restrictions thus you cannot reliably use Notes' field names as bookmarks which complicates the mapping procedure, especially for unskilled users.

    Both problems make it hard for unskilled users to create a word reporting template and reliably match the fields with Notes (or another data source).
    So I've developed a DocVariable-based alternative described in my german article Word als Druckschnittstelle (Word as a Printing Engine) .

    http://www.sns1.de/partner/flamme/wflamme.nsf/Shortcut/WordInterface1

    A OOP solution based on that approach has run in a professional environment for over a year now w/o problems. I can highly recommend it, the end users love it.

    Unfortunately I didn't find the time to translate the article (yet).

    Sth else to consider: At least Word2000 and earlier have a problem with automation. If an instance of word is created by automation and not visible, then any further Word instances and macros will point to the same object and interfere with your automation process (Microsoft Knowledge Base Article No. Q188546).

    My article mentioned above shows a LotusScript solution for that problem, too.

    HTH somebody here dealing with Word interfacing. Wolfgang Flamme#
  2. Thanks Wolfgang - looks like a useful link!Ben Poole#

Comments on this post are now closed.

About

I’m a software architect / developer / general IT wrangler specialising in web, mobile web and middleware using things like node.js, Java, C#, PHP, HTML5 and more.

Best described as a simpleton, but kindly. You can read more here.

";
File Attachment Icon
mailmerge.vb
File Attachment Icon
mailmerge.zip