Email Mail Merge via linked Excel sheet

[joe]

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

I am wanting 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!