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