[Dave]

Thanks Peter,
Will try this out and let you know how i get on. Again i can't say how much
i appreciate the help.

Have a Great day.
"Peter Jamieson" wrote:

Was there another part to the
merge?

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 a
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
,Alan,Bell
,Chris,Dawes
,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:

c:\a\attach1.doc
c:\a\attach2.doc
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

Dave wrote:
Thanks Peter,
one more thing, do we always have to do the page break manually to add the
body of the mail? i have 350 users to send out the mails to each with a
little bit of different text and attachement. Was there another part to the
merge?

"Peter Jamieson" wrote:

You need to add a line to Doug's code, and in there it will look a bit
different from the line I gave.

e.g., add a line to Doug's code


Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = mysubject
.Body = Source.Sections(j).Range.Text

so that it reads

Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = mysubject
.Body = Source.Sections(j).Range.Text
.SentOnBehalfOfName = "the Exchange user name you want to use"


Obviously, you have to insert the correct name instead of "the Exchange
user name you want to use"

I certainly can't guarantee that that will work, and I'm not currently
in a position to investigate if it does not, but I was able to do it
with my own copy of Exchange when I looked this area a year or two ago.


Peter Jamieson

http://tips.pjmsn.me.uk

Dave wrote:
Thanks Peter,

I will be working from an exchange address and have "Send As" or
"Send As Delegate" permission for another user account . where do i put in
objMailItem.SentOnBehalfOfName = "the Exchange user name you want to use"
?
"Peter Jamieson" wrote:

Good.

if i want the
mail to be sent from a different address is there a way to be able to
select
the email address its set from?

1. For one thing, as I undertand it the MAPILabs toolkit mentioned by
Graham Mayor in his response to this thread has a facility to do this.

2. Otherwise, it depends on what you mean. Unless you're connected to
Microsoft exchange, the macro will only send from the Outlook /profile/
you are currently logged into. If

3. However, if all you need is the "Reply-to" to be set to a different
address, if you have control over your own email /account/ settings you
should be able to change that address.

4. If you are working via Microsoft Exchange and you have "Send As" or
"Send As Delegate" permission for another user account, then you should
be able to set

objMailItem.SentOnBehalfOfName = "the Exchange user name you want to use"

And send the item. In that case, the item does not necessarily appear as
"a sent on behalf of" item. If you have "Send As" permission, it appears
as if it's from the user you specified in SentOnBehalfOfName and there
is no "on behalf of" - the same is true if you fill in the "From"

And that's about all I know...

Peter Jamieson

http://tips.pjmsn.me.uk

Dave wrote:
Ok, got it to work. thx very much. just a question though, if i want the
mail to be sent from a different address is there a way to be able to select
the email address its set from?

"Peter Jamieson" wrote:

Well, in order to do that, I will try to work backwards through the
procedure, because then the reason for each step may become a little
clearer.

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.

Using this method, I suggest you do not try to grasp the entire process
in one go. Work through it step by step. At least if you get stuck, you
will probably be able to identify where and why.

The main prerequisites are that you have a version of Word that lets you
use Word VBA, and Outlook (not Outlook Express).

I'm also going to make a simplification by assuming that you want one,