? How to use MS Word merge field results after merge is completed

Posted on 2014-08-19
Last Modified: 2014-08-27
I have a 3rd party app that brings over data to a word document and fills the merge fields that we selected from a list to help create our own letters.  What I need to do is take the information in that letter that has been filled into the merge fields and calculate a few numbers and then fill a table with the information.

So the question is how do I access the information in the merge fields in vba, so that I can fill other custom  fields into the letter.  So I need the letter to load first with the data, then execute the vba code after the word document has been loaded.

Hoping someone has had to do something like this.  Not sure what sub to call my sub from when the document is loaded, and how to access the fields.

Question by:mgmhicks
    LVL 47

    Expert Comment


    The event  for it is


    see example

    Res = Doc.MailMerge.DataSource.DataFields("ClientNumber").Value

    LVL 76

    Accepted Solution

    A third party application might be doing something different, but the text that has been placed in Result document during the Word Mail Merge operation is not especially distinguished from the fixed text from the Main document. That means that there is no simple way to pull the merge text from the result document.

    Some workarounds - the best would depend on your exact situation

    1. You could consider using a special Style for the merge fields and looking for that in the result document. Quite a lot of work to set up.
    2. Compare the Main and the Result documents. Not particularly easy.
    3. Requery the datasource separately just before or after the merge. This would normally be the simplest method.

    Author Comment

    ok, I have this formula that gives a syntax error and I don't know why.  =datediff([renewal_begins],[current_lease_ends],"d")

    I have a Renewal_Begins merge field and a current_lease_ends merge field.    I did try it without the brackets and that still did not work.

    I not sure how to get the code to stop or show message box after mail merge just to see if I can get code to run on this.

    I'm now trying to create my own fields, like I need to know the number of days in the renewal_begins month, or the number of days in the current_lease_ends date.  

    This 3rd party app is creating the word document for us, in but how can I access the data that is coming over and maybe manipulate that, instead of the merge fields.

    hope that helps clear up my situation.
    LVL 21

    Expert Comment

    If the application is only occasional, or too ad hoc to warrant an automated solution, consider using the little-known Spike feature of Word. The name comes from a mounted spike used to pin notes. I use it to pull selected information from Word invoices into Excel. Here's how...

    1. Select the first element you need and press Ctrl-F3. The selected content will disappear, but it actually becomes the first item on the spike. Repeat for each element you need.

    2. Move to an empty paragraph mark and press Shift-Ctrl-F3. Each of the elements added to the spike will have their own paragraph in the order they were spiked.

    3. To get this to my Excel sheet, I select and copy the dumped spike items and paste it into Excel.

    Be sure to close the Word document without saving after you use this!

    Note that Spike does not use the clipboard, and its content is unavailable once you have dumped it with Shift-Ctrl-F3.
    LVL 76

    Expert Comment


    It is difficult to understand your comment in terms of your question. If you have difficulty in producing a datasource sheet, then it would be better to raise a separate question

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
    This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now