Email Mail Merge via linked Excel sheet

I have a MS Word 2007 document setup as a Mail Merge doc. I am using Excel as the data source. The MERGEFIELD ClientData contains an Excel file (test.xlsx).

I want to merge the data from the Excel file listed in ClientData into the respective Mail Merge document. However, whenever I start the Mail Merge the {MERGEFIELD ClientData} field gets resolved only once and does not select the next row from ClientData.

So this:

{LINK Excel.Sheet.12 "C:\path\to\file\{MERGEFIELD ClientData}" a f 4 h}

Becomes this after starting the merge:

{LINK Excel.Sheet.12 "C:\path\to\file\test.xlsx" a f 4 h}

So every Mail Merge doc uses the test.xlsx instead of the respective Excel document specific to the client (i.e test1.xlsx, test2.xlsx, test3.xlsx, etc.)

As the merge runs through each Mail Merge doc I expect to see this:

{LINK Excel.Sheet.12 "C:\path\to\file\test.xlsx" a f 4 h}

{LINK Excel.Sheet.12 "C:\path\to\file\test1.xlsx" a f 4 h}

{LINK Excel.Sheet.12 "C:\path\to\file\test2.xlsx" a f 4 h}

{LINK Excel.Sheet.12 "C:\path\to\file\test3.xlsx" a f 4 h}

But for some reason this isn’t happening. Does anyone have any suggestions?

Thanks!

The problem is that LINK fields always resolve all nested fields, sooner or later.

Your best bet is probably to use Word’s Mailmerge events, then use the BeforeRecordMerge event to:

• a. get the filename (test.xlsx etc.) (e.g. via Doc.Mailmerge.Datasource.Datafields("ClientData").Value)
• b. either plug that back into the existing LINK field, or perhaps more easily, delete the existing LINK field and insert a new one.

Hint: insert a new Empty field, then insert the entire LINK field.