Was there another part to the
Yes, as I explained...
<<
I'm also going to split the description into 2 parts:
a. Getting the macro to work with some simple test data. This is to
concentrate on what the macro does (partly because you have those errors
when you try to run it) and skips the whole issue of how you actually
use mailmerge to create the files that you need.
b. How to prepare the data that the macro needs.
I'm only going to do part (a) now, partly because I don't have time to
finish right now, and partly to give us a breather. If all goes OK,
perhaps it will be enough for you to understand how the whole thing is
supposed to work, in which case we won't need part (b). But let's see.
I wasn't sure whether you had now been able to work out part (b) for
yourself. But here goes.
I. Let's recap:
For the macro to work, you need
a. an Attachment table with one row for each recipient and two
columns. Column one contains the e-mail address of the recipient and
column 2 contains the pathname of the attachment to be sent to that
recipient
b. a Message Bodies Document that contains one section for each
recipient, in the same sequence as the Attachment table.
Doug's article assumes that you would typically be able to create both
of these things starting from the same data source, and that there would
be one row in that data source for each recipient.
II. Example
So let's go back to the "you're emailing a message body and an
attachment to a small number of recipients" scenario, and to keep it
simple, let's assume there are just 3 recipients.
If your situation isn't like this, we can deal with that later, but give
this one a go anyway.
Let's create a simple data source with four columns and 3 rows of data.
The columns are:
Email (the email address)
Path (the attachment pathname
First (first name)
Last (last name)
This can be a "comma-delimited" data source in a Word document. So
a. create a blank new Word document
b. copy and paste the following data into it
Email,Path,First,Last
(e-mail address removed),c:\a\attach1.doc,Alan,Bell
(e-mail address removed),c:\a\attach2.doc,Chris,Dawes
(e-mail address removed),c:\a\attach3.doc,Ellie,Furtado
c. save it as mydata.docx and close it
III. Create the Attachment table
We need to end up with a Word document that contains a 3-row, 2 column
table like this:
(e-mail address removed) c:\a\attach1.doc
(e-mail address removed) c:\a\attach2.doc
(e-mail address removed) c:\a\attach3.doc
You could of course create that by hand. But to create it using a
mailmerge, you do this in Word 2007:
a. create a new blank document. This will be the Mail Merge Main Document
b. click Mailings->Start Mail Merge->Directory
c. click Select Recipient->use Existing List, then locate and select
mydata.docx. Word may ask for delimiter characters - in this case the
field delimiter is "," and the record delimiter (if you are asked about
that) is Enter
d. The Edit recipients List should be ungreyed. Click it and verify
that the four columns and 3 data rows are there. Click OK to dismiss it.
e. In your Mail Merge Main Document, use Insert->Table to create a
table with one row and two columns.
f. Click the Mailings tab again
g. Click in the first cell of the table
h. Click On the /text/ part of the Insert merge field button. A
dropdown showing the four column names from your data source should be
shown. Select "Email". «Email» or possibly something like { MERGEFIELD
Email } should appear in the first cell of the table.
i. Click in the second cell of the table
j. Repeat step (h), selecting the "Path" field and inserting it.
k. Click Finish & Merge->Edit individual Documents...
l. In the dialog box titled "Merge to new Document", select All and
click OK
Word should create a new Document (rather quickly in fact) with a table
containing 2 columns and 3 rows.
Save that as myat.docx and close it. This is obviously the equivalent of
the testat.docx file we created in "part (a)"
Save and close the mail merge main document that you created in step (a).
IV. Create the Message Bodies Document
To recap, the Message Bodies Document needs to have one section for each
recipient, in the same sequence as the Attachment Table.
So in Word 2007:
a. create a new blank document. This will be the Mail Merge Main Document
b. click Mailings->Start Mail Merge->Letters (we choose Letters
because then Word will automatically insert a section break between the
text for each recipient
c. as before, click Select Recipient->use Existing List, then locate
and select mydata.docx. Word may ask for delimiter characters - in this
case the field delimiter is "," and the record delimiter (if you are
asked about that) is Enter
d. The Edit recipients List should be ungreyed. Click it and verify
that the four columns and 3 data rows are there. Click OK to dismiss it.
e. Type
"My name is "
f. Insert the "first" and "Last" fields using the Insert Merge field
button, and type a space between them.
g. Click Finish & Merge->Edit individual Documents...
h. In the dialog box titled "Merge to new Document", select All and
click OK
Word should create a new Document (rather quickly in fact) with 3 sections
Save that as mymb.docm (it will need to be a Macro-enabled document),
save it and close it. This is obviously the equivalent of the
testmb.docm file we created in "part (a)"
Save and close the mail merge main document that you created in step (a).
V. So, just as in part (a), we now have an Attachment Table (in
myat.docx) and a Message Bodies Document (in mymb.docm
All you should now need to do is follow the steps in part (a) to insert
the macro, reference the Outlook library, and run the macro.
OK, so that is the experiment. If you manage to get that to work, then
maybe you will be one step closer to doing the merge you want.
Otherwise, say where you get stuck (I probably wont' be able to reply
until tomorrow now).
I would say there are now two main problem areas to go through:
a. do you really have to re-insert that macro every time you do this?
Well no, but I've suggested you do it this way because in the short term
it makes some things rather simpler.
b. your data source is almost certainly not as simple as the test data
source I've suggested. In fact, it's quite likely that your data is in
various different structures at the moment. But there are two points to
be made:
- the key thing is that the Attachments Table and the Message Bodies
Document have their rows/sections in the same sequence. But it doesn't
really matter how you achieve that.
If for example, you have already created your Attachments table manually,
a. as long as you have a data source that has a column for the
recipient's email and columns for all the other data you need in the
message body, /and/ it is in the same sequence as the Attachments table,
then all you need to do is merge that data source to create the Message
Bodies Document.
b. or, if you do not have a data source like that, but you have
already created the Attachments Table, you could do a lot worse than the
following:
- copy the Attachments table to a new Word document
- add any columns you need to contain the data you need to produce
the Message Bodies document
- add a header row with column names
- use that as the data source for the merge that roduces the Message
Bodies document.
That's about the best I can do for now. Let's see if that's enough to
help you see what is going on.
Peter Jamieson
http://tips.pjmsn.me.uk
