Indicate a mailmerge record number before launching Word?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

4m0h1w3uzib7cul@jetable.org - 18 Jul 2006 14:12 GMT

Using office 2003:

I have an Excel spreadsheet that is the source data for several
mailmerge documents. Within the spreadsheet, I have links to each of
these mailmerge documents. I would like to customize these links to
make word open the document AND select a designated record number for
the mailmerge. (I'll have a link to the word document for each row of
data, and by clicking on the right link, I'll pull up that same row's
data on the merged Word document.

Another way of asking this is this: Are there command line parameters
or VBScript commands that will allow a variable to be inserted into a
mailmerge template's record number?

I suspect I may need to alter the word document (change the record
number) before it is opened each time.

Switching to Access instead of Excel is not an option here.

Reply to this Message

Cindy M -WordMVP- - 18 Jul 2006 15:24 GMT

> Using office 2003:
>

No command line parameters to do this.

One option could be to filter all records but the one you want to merge.
Link the Excel data source to the main merge document using DDE (not the
default 2003 OLE DB). Set up a filter in the main merge document to
merge only records where a particular field that you know always
contains data are "Not Blank". (DDE sees records that are filtered out,
but no field data; so only the visible records will be merged.)

The other option would be to use Automation (VBA) to open the main merge
document and set the QueryString property of the documents'
MailMerge.DataSource object. This would be a valid SQL SELECT statement
such as: "SELECT * FROM 'completepath.Excel.xls' WHERE myFieldName=1234"

> I have an Excel spreadsheet that is the source data for several
> mailmerge documents. Within the spreadsheet, I have links to each of
[quoted text clipped - 12 lines]
>
> Switching to Access instead of Excel is not an option here.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :-)

Reply to this Message

4m0h1w3uzib7cul@jetable.org - 19 Jul 2006 19:09 GMT

A follow up question:

I've decided the filtered DDE solution is the easiest for me. I now
have a column used to flag items to include in the mailmerge, and I put
a "1" in the selected row of this column before launching my mailmerge.
It works perfectly. Thanks! That problem had me stumped for a long
time.

Now I'd like to automate it further with a macro. I'll also be
scouring an excel programming group, but wondered if anyone here might
have some insight.

I want a macro that notices which row is active, places a "1" in the
Mailmerge column of that row, launches a hyperlink to the mailmerge
document, then replaces the "1" with null. The end result will allow
the user to select a row, click the macro titled "Mailmerge", and the
word document pops up with that row's data.

any suggestions?

> One option could be to filter all records but the one you want to merge.
> Link the Excel data source to the main merge document using DDE (not the
> default 2003 OLE DB). Set up a filter in the main merge document to
> merge only records where a particular field that you know always
> contains data are "Not Blank". (DDE sees records that are filtered out,
> but no field data; so only the visible records will be merged.)

> > I have an Excel spreadsheet that is the source data for several
> > mailmerge documents. Within the spreadsheet, I have links to each of
[quoted text clipped - 3 lines]
> > data, and by clicking on the right link, I'll pull up that same row's
> > data on the merged Word document.

Reply to this Message