[MarkWey]

Is it possible for a mail merge record in Word 2007 to have an embedded table
that contains more than one related record?

My data source contains a column with 3 email addresses and another column
with 6 records €“ Sales by Quarter. Id like the Word output to produce three
emails, containing a table with 2 merged records in each.

Thanks in advance!

[Peter Jamieson]

Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

Word data sources are in essence simple relations with no repeating
elements. Word does not have any general-purpose built-in ways to split
multiple-valued elements into multiple records.

In this case,
a. what is the data source? Is it an Access 2007 database with multi-valued
elements, or something else?
b. are the 3 email addresses related to the 6 sales by quarter records in
the most obvious way (i.e. the first two sales records belong with the first
email address, the 3rd and 4th sales records with th e 2nd email address,
and so on)? Or what/

(Not sure I can help even then, but IMO anyone trying to solve this problem
would need to know the answers)

Peter Jamieson

"MarkWey" wrote in message
...
Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

My data source contains a column with 3 email addresses and another column
with 6 records €“ Sales by Quarter. Id like the Word output to produce
three
emails, containing a table with 2 merged records in each.

Thanks in advance!

[Mark]

I could use either Access or Excel as my data source, I currently have it in
Excel.

WRT relationships of the records - you understood correctly as (b) below.

"Peter Jamieson" wrote:

Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

Word data sources are in essence simple relations with no repeating
elements. Word does not have any general-purpose built-in ways to split
multiple-valued elements into multiple records.

In this case,
a. what is the data source? Is it an Access 2007 database with multi-valued
elements, or something else?
b. are the 3 email addresses related to the 6 sales by quarter records in
the most obvious way (i.e. the first two sales records belong with the first
email address, the 3rd and 4th sales records with th e 2nd email address,
and so on)? Or what/

(Not sure I can help even then, but IMO anyone trying to solve this problem
would need to know the answers)

Peter Jamieson

"MarkWey" wrote in message
...
Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

My data source contains a column with 3 email addresses and another column
with 6 records €“ Sales by Quarter. Id like the Word output to produce
three
emails, containing a table with 2 merged records in each.

Thanks in advance!

[Doug Robbins - Word MVP]

With an Access data source, it is probably possible to achieve what you are
after simply by using the reporting capability of Access.

If you are hell bent on using Word, see fellow MVP Macropod's "Word 97-2007
Catalogue/Directory Mailmerge
Tutorial" at:

http://www.wopr.com/index.php?showtopic=731107

or

http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

Do read the tutorial before trying to use the mailmerge document included
with it as you must get the mail merge main document set up exactly as
required.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"Mark" wrote in message
...
I could use either Access or Excel as my data source, I currently have it
in
Excel.

WRT relationships of the records - you understood correctly as (b) below.

"Peter Jamieson" wrote:

Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

Word data sources are in essence simple relations with no repeating
elements. Word does not have any general-purpose built-in ways to split
multiple-valued elements into multiple records.

In this case,
a. what is the data source? Is it an Access 2007 database with
multi-valued
elements, or something else?
b. are the 3 email addresses related to the 6 sales by quarter records
in
the most obvious way (i.e. the first two sales records belong with the
first
email address, the 3rd and 4th sales records with th e 2nd email address,
and so on)? Or what/

(Not sure I can help even then, but IMO anyone trying to solve this
problem
would need to know the answers)

Peter Jamieson

"MarkWey" wrote in message
...
Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

My data source contains a column with 3 email addresses and another
column
with 6 records €“ Sales by Quarter. Id like the Word output to produce
three
emails, containing a table with 2 merged records in each.

Thanks in advance!

[Peter Jamieson]

To do a mailmerge direct to email, the main constraint is that you have
to choose a column that contains the e-mail address for each email the
merge is going to produce. In that case, you would at the very least
need to reorganise your data source so that you have either
a. one or more records for each email address. In that case you could
either construct a merge that consumed one or more records for each address.
b. each email address in a separate field in the record, which is
obviously only feasible if you have a known maximum e-mail address count
per record, preferably rather a small one. e.g. if you always have 3
addresses and 6 sales records, you would probably be OK. In that case
you would actually need to do 3 merges, choosing a different field as
the email address in each case.

IMO (a) would ultimately be simpler (i.e. if you have to split up the
email address field at some point, why not separate the data into
separate records, with the sales data for each email address in each
record while you're at it? It's probably a fairly simple piece of Access
or Excel VBA to do that) and essential if the number of e-mail addresses
per record is actually either large or unconstrained.

There is then the question of how to deal with the multiple sales
records. Again, if it is only ever 2 per email, I would probably
separate the 2 sales records into 2 sets of fields in the same record at
the same time as I did (a) above. At that point you could create a merge
to email with a simple 2-row table (one row for each sales record) and
produce one e-mail for each record in your reorganised data source. If
on the other hand there could be an unlimited number of sales records,
there are at least a couple of ways you could go:
i. generate a single table with one record for each email/sales
record combination. Properly constructed, that would allow you to use
macropod's method (mentioned by Doug) although AFAIK that actually has
to work by merging to a new document which you would then have to split
and email.
ii. generate a parent table (one record per email) and a child table
(multiple sales records per email. In order to use Mailmerge to merge
/that/ you could
- use the parent table as the mail merge data source
- use Word's Mailmerge events to process the mail merge main
document for each record in the data source prior to merge. In that
processing you could for example use ADO to get the child records
related to the current parent and insert them into a table.

(you could actually do that with method (i) as well but it's probably a
bit simpler with method 2)

Alternatively, it might be simpler to use (say) Excel or Access VBA to
process each email address in turn and generate your email (either by
generating a Word document or otherwise) either by creating each
document from scratch using VBA, or (for example) using { DOCVARIABLE }
fields in Word instead of { MERGEFIELD } fields and updating the values
of the appropriate Word Document Variables in your VBA code.

Peter Jamieson

http://tips.pjmsn.me.uk

Mark wrote:
I could use either Access or Excel as my data source, I currently have it in
Excel.

WRT relationships of the records - you understood correctly as (b) below.

"Peter Jamieson" wrote:

Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?
Word data sources are in essence simple relations with no repeating
elements. Word does not have any general-purpose built-in ways to split
multiple-valued elements into multiple records.

In this case,
a. what is the data source? Is it an Access 2007 database with multi-valued
elements, or something else?
b. are the 3 email addresses related to the 6 sales by quarter records in
the most obvious way (i.e. the first two sales records belong with the first
email address, the 3rd and 4th sales records with th e 2nd email address,
and so on)? Or what/

(Not sure I can help even then, but IMO anyone trying to solve this problem
would need to know the answers)

Peter Jamieson

"MarkWey" wrote in message
...
Is it possible for a mail merge record in Word 2007 to have an embedded
table
that contains more than one related record?

My data source contains a column with 3 email addresses and another column
with 6 records €“ Sales by Quarter. Id like the Word output to produce
three
emails, containing a table with 2 merged records in each.

Thanks in advance!

[macropod[_2_]]

Hi Peter,


Properly constructed, that would allow you to use macropod's method (mentioned by Doug) although AFAIK that actually has to work by
merging to a new document which you would then have to split and email.

Actually, instead of requiring the user to split & email an output document, the technique in my tutorial uses a two-stage merge
process (with a macro in between) to generate the emails.

--
Cheers
macropod
[Microsoft MVP - Word]


"Peter Jamieson" wrote in message ...
To do a mailmerge direct to email, the main constraint is that you have to choose a column that contains the e-mail address for
each email the merge is going to produce. In that case, you would at the very least need to reorganise your data source so that
you have either
a. one or more records for each email address. In that case you could either construct a merge that consumed one or more records
for each address.
b. each email address in a separate field in the record, which is obviously only feasible if you have a known maximum e-mail
address count per record, preferably rather a small one. e.g. if you always have 3 addresses and 6 sales records, you would
probably be OK. In that case you would actually need to do 3 merges, choosing a different field as the email address in each case.

IMO (a) would ultimately be simpler (i.e. if you have to split up the email address field at some point, why not separate the data
into separate records, with the sales data for each email address in each record while you're at it? It's probably a fairly simple
piece of Access or Excel VBA to do that) and essential if the number of e-mail addresses per record is actually either large or
unconstrained.

There is then the question of how to deal with the multiple sales records. Again, if it is only ever 2 per email, I would probably
separate the 2 sales records into 2 sets of fields in the same record at the same time as I did (a) above. At that point you could
create a merge to email with a simple 2-row table (one row for each sales record) and produce one e-mail for each record in your
reorganised data source. If on the other hand there could be an unlimited number of sales records, there are at least a couple of
ways you could go:
i. generate a single table with one record for each email/sales record combination. Properly constructed, that would allow you
to use macropod's method (mentioned by Doug) although AFAIK that actually has to work by merging to a new document which you would
then have to split and email.
ii. generate a parent table (one record per email) and a child table (multiple sales records per email. In order to use Mailmerge
to merge /that/ you could
- use the parent table as the mail merge data source
- use Word's Mailmerge events to process the mail merge main document for each record in the data source prior to merge. In
that processing you could for example use ADO to get the child records related to the current parent and insert them into a table.

(you could actually do that with method (i) as well but it's probably a bit simpler with method 2)

Alternatively, it might be simpler to use (say) Excel or Access VBA to process each email address in turn and generate your email
(either by generating a Word document or otherwise) either by creating each document from scratch using VBA, or (for example)
using { DOCVARIABLE } fields in Word instead of { MERGEFIELD } fields and updating the values of the appropriate Word Document
Variables in your VBA code.

Peter Jamieson

http://tips.pjmsn.me.uk

Mark wrote:
I could use either Access or Excel as my data source, I currently have it in Excel.

WRT relationships of the records - you understood correctly as (b) below. "Peter Jamieson" wrote:

Is it possible for a mail merge record in Word 2007 to have an embedded table
that contains more than one related record?
Word data sources are in essence simple relations with no repeating elements. Word does not have any general-purpose built-in
ways to split multiple-valued elements into multiple records.

In this case,
a. what is the data source? Is it an Access 2007 database with multi-valued elements, or something else?
b. are the 3 email addresses related to the 6 sales by quarter records in the most obvious way (i.e. the first two sales
records belong with the first email address, the 3rd and 4th sales records with th e 2nd email address, and so on)? Or what/

(Not sure I can help even then, but IMO anyone trying to solve this problem would need to know the answers)

Peter Jamieson

"MarkWey" wrote in message ...
Is it possible for a mail merge record in Word 2007 to have an embedded table
that contains more than one related record?

My data source contains a column with 3 email addresses and another column
with 6 records €“ Sales by Quarter. Id like the Word output to produce three
emails, containing a table with 2 merged records in each.

Thanks in advance!

[Peter Jamieson]

OK - sorry to misrepresent your article.

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Peter,


Properly constructed, that would allow you to use macropod's method
(mentioned by Doug) although AFAIK that actually has to work by
merging to a new document which you would then have to split and email.

Actually, instead of requiring the user to split & email an output
document, the technique in my tutorial uses a two-stage merge process
(with a macro in between) to generate the emails.

[macropod[_2_]]

Hi Peter,

That's OK - it's only a fairly recent enhancement to the tutorial.

--
Cheers
macropod
[Microsoft MVP - Word]


"Peter Jamieson" wrote in message ...
OK - sorry to misrepresent your article.

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Peter,


Properly constructed, that would allow you to use macropod's method
(mentioned by Doug) although AFAIK that actually has to work by
merging to a new document which you would then have to split and email.

Actually, instead of requiring the user to split & email an output
document, the technique in my tutorial uses a two-stage merge process
(with a macro in between) to generate the emails.