Word Mail Merge to E-mail message with attachment?

[Frank D. Nicodem, Jr.]

I am using Word 2003, and would like to do a Mail Merge to an E-mail
document. I seem to be encountering a couple of "glitches", though.

The first is that I would like my eventual E-mail message to go out with an
attachment -- the same attachment, on every one. However, I can't find a
way to generate Mail Merge E-mails that contain attachments.

I've seen other similar Notes that say "Cut and Paste", or "Write your
document in Word, save it, and Attach it to your E-mail message." All well
and good, if I'm doing one of these. But I have a whole database of E-mail
addresses to process. They'll all get the *same* E-mail message (which I
consider a "cover letter" to the attached Word document), and the same Word
document attached. Is there no way to do this???

Second, I normally use Outlook Express. I'm getting the feeling, however,
that I have to switch to Outlook for the Word Mail Merge to work *at all*
(even without the attachment). Is this true?

If I can't do what I want by using a Word Mail Merge (and, preferably,
Outlook Express), is there some other tool that will let me:

1) Create a separate Word document
2) Create a simple "cover letter" E-mail message
3) Process through a data file of E-mail addresses -- making NO
modifications at all to the Word document or E-mail message -- and just send
out the E-mail with attachment

Thanks.

 

[Peter Jamieson]

There are problems doing this whatever approach you take in Word:
a. Word MailMerge will only either prepare a plain text email, or an empty
e-mail with attachment, and there's no way to interrupt it to add the plain
text or attachment
b. There's no really easy "out-of-the-box" way to automate Outlook Express
to send e-mails with attachments as far as I know, although clearly Word
MailMerge does it so I'm probably just not aware of the right approach to
use.
c. Since you want a standard message and standard attachment (I assume
with /no/ variations) then you could prepare a standard message in either
Outlook or OE , then use merge to generate lists of recipients up to the
maximum length Outlook/OE can take, then copy/paste those into BCC, but I
assume you're trying to avoid the "BCC hack".
d. you prepare a standard message in Outlook then use Outlook VBA to
automate it - i.e. read your data source and send one copy per recipient.
Potentially the simplest way, but you have to be able to read your data
file - that might or might not be simple depending on what it is.
e. you prepare a standard message in Outlook, then use Word's mailmerge
object to get the recipient records one by one and send the message.

(d) would probably have the advantage that you wouldn't get loads of pop-up
messages (and it's all in Outlook). However, I would either need to know
what your data source was before I could even attempt a macro for that, or
just choose one.

You might be able to find code that does that at www.slipstick.com, e.g.
http://www.slipstick.com/addins/massmail.asp . Some of those might even work
with OE, although Slipstick is very much an Outlook-oriented site.

So here's a macro that does (e). Sorry, I can't help with an OE-based
solution. You could also see if anything at www.mapilab.com helps.

To use this macro, you need to do the following /at least/
a. Create a folder in Outlook, at the same level as your Inbox, to contain
Mail items. I've called mine "amergetemplate"
b. create the mail message you want, with subject, body text and
atttachment, and put it in that folder. There should only be one item.
c. in Word, create a mail merge main document and attach it to your data
source containing your e-addresses. I've assumed an address column called
Emailaddress (NB, capitalisation is important in the mailmerge field names
in this code). This document does not need to have any content as it is not
ever actually merged to anything.
d. In the Word VB Editor, create a module with the following code, and use
Tools|Reference to make a refernce to "Microsoft Outlook x.0 Object
Library", where x is the version number, e.g. 11 for Outlook 2003.

If you need more help on that, see
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

e. Test!

Here it is:


Sub MultiSendPreparedEmail()
Dim bOutlookStarted As Boolean
Dim bTerminateMerge As Boolean
Dim intSourceRecord As Integer
Dim objMailItem As Outlook.MailItem
Dim objMerge As Word.MailMerge
Dim objOutlook As Outlook.Application

bOutlookStarted = False
bTerminateMerge = False

' Set up a reference to the
' Activedocument

Set objMerge = ActiveDocument.MailMerge

' Start Outlook as necessary and find the prepared e-mail

On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set objOutlook = CreateObject("Outlook.Application")
bOutlookStarted = True
End If

With objMerge

' If no data source has been defined,
' do it here using OpenDataSource.
' But if it is already defined in the
' document, you should not need to
' define it here.

' .OpenDataSource _
' Name:="whatever"

intSourceRecord = 1

Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord

' if we have gone past the end
' (and possibly, if there are no records)
' then the Activerecord will not be what
' we have just tried to set it to.
' Programmers may find this loop weird and wonder
' why I don't just use a For i = 1 to DataSource.recordcount
' or some such, but not all data sources behave as
' you might expect

If .DataSource.ActiveRecord <> intSourceRecord Then
bTerminateMerge = True
' the record exists
Else

' Set a reference to a copy of our mail item, which should
' be the only item in a folder called "amergetemplate"
' set up to contain mail items: this should all be in one line from "Set"
to "Copy"
Set objMailItem =
objOutlook.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders("amergetemplate").Items(1).Copy
With objMailItem
' Set the To to one of your fields
.To = objMerge.DataSource.DataFields("Emailaddress")
.Send
End With
Set objMailItem = Nothing

.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord

intSourceRecord = intSourceRecord + 1
End If
Loop
End With

' Close Outlook if appropriate

If bOutlookStarted Then
objOutlook.Quit
End If

Set objOutlook = Nothing
Set objMerge = Nothing

End Sub

Good luck!

Peter Jamieson

 

[Frank D. Nicodem, Jr.]

Thanks for the suggestions. Since you were asking about my data source,
I've currently got it in an Excel spreadsheet. I had planned on using Word
Mail Merge, so I wanted something formatted appropriately. If a specific
application needs something simpler (i.e., it can't read either an XLS or a
CSV file), I could always just pick off the E-mail column and put it to a
plan text file -- i.e., one E-mail address per line. As I said, I do *not*
need to merge in any dynamic fields; it's purely a fixed E-mail message with
a fixed attachment. The only thing that varies from one to another is the
target E-mail address.
----------------------------------
Frank D. Nicodem, Jr.
(e-mail address removed)

There are problems doing this whatever approach you take in Word:
a. Word MailMerge will only either prepare a plain text email, or an empty
e-mail with attachment, and there's no way to interrupt it to add the
plain text or attachment
b. There's no really easy "out-of-the-box" way to automate Outlook Express
to send e-mails with attachments as far as I know, although clearly Word
MailMerge does it so I'm probably just not aware of the right approach to
use.
c. Since you want a standard message and standard attachment (I assume
with /no/ variations) then you could prepare a standard message in either
Outlook or OE , then use merge to generate lists of recipients up to the
maximum length Outlook/OE can take, then copy/paste those into BCC, but I
assume you're trying to avoid the "BCC hack".
d. you prepare a standard message in Outlook then use Outlook VBA to
automate it - i.e. read your data source and send one copy per recipient.
Potentially the simplest way, but you have to be able to read your data
file - that might or might not be simple depending on what it is.
e. you prepare a standard message in Outlook, then use Word's mailmerge
object to get the recipient records one by one and send the message.

(d) would probably have the advantage that you wouldn't get loads of
pop-up messages (and it's all in Outlook). However, I would either need to
know what your data source was before I could even attempt a macro for
that, or just choose one.

You might be able to find code that does that at www.slipstick.com, e.g.
http://www.slipstick.com/addins/massmail.asp . Some of those might even
work with OE, although Slipstick is very much an Outlook-oriented site.

So here's a macro that does (e). Sorry, I can't help with an OE-based
solution. You could also see if anything at www.mapilab.com helps.

To use this macro, you need to do the following /at least/
a. Create a folder in Outlook, at the same level as your Inbox, to contain
Mail items. I've called mine "amergetemplate"
b. create the mail message you want, with subject, body text and
atttachment, and put it in that folder. There should only be one item.
c. in Word, create a mail merge main document and attach it to your data
source containing your e-addresses. I've assumed an address column called
Emailaddress (NB, capitalisation is important in the mailmerge field names
in this code). This document does not need to have any content as it is
not ever actually merged to anything.
d. In the Word VB Editor, create a module with the following code, and use
Tools|Reference to make a refernce to "Microsoft Outlook x.0 Object
Library", where x is the version number, e.g. 11 for Outlook 2003.

If you need more help on that, see
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

e. Test!

Here it is:


Sub MultiSendPreparedEmail()
Dim bOutlookStarted As Boolean
Dim bTerminateMerge As Boolean
Dim intSourceRecord As Integer
Dim objMailItem As Outlook.MailItem
Dim objMerge As Word.MailMerge
Dim objOutlook As Outlook.Application

bOutlookStarted = False
bTerminateMerge = False

' Set up a reference to the
' Activedocument

Set objMerge = ActiveDocument.MailMerge

' Start Outlook as necessary and find the prepared e-mail

On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set objOutlook = CreateObject("Outlook.Application")
bOutlookStarted = True
End If

With objMerge

' If no data source has been defined,
' do it here using OpenDataSource.
' But if it is already defined in the
' document, you should not need to
' define it here.

' .OpenDataSource _
' Name:="whatever"

intSourceRecord = 1

Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord

' if we have gone past the end
' (and possibly, if there are no records)
' then the Activerecord will not be what
' we have just tried to set it to.
' Programmers may find this loop weird and wonder
' why I don't just use a For i = 1 to DataSource.recordcount
' or some such, but not all data sources behave as
' you might expect

If .DataSource.ActiveRecord <> intSourceRecord Then
bTerminateMerge = True
' the record exists
Else

' Set a reference to a copy of our mail item, which should
' be the only item in a folder called "amergetemplate"
' set up to contain mail items: this should all be in one line from "Set"
to "Copy"
Set objMailItem =
objOutlook.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders("amergetemplate").Items(1).Copy
With objMailItem
' Set the To to one of your fields
.To = objMerge.DataSource.DataFields("Emailaddress")
.Send
End With
Set objMailItem = Nothing

.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord

intSourceRecord = intSourceRecord + 1
End If
Loop
End With

' Close Outlook if appropriate

If bOutlookStarted Then
objOutlook.Quit
End If

Set objOutlook = Nothing
Set objMerge = Nothing

End Sub

Good luck!

Peter Jamieson

 

[Peter Jamieson]

In that case you could probably use the same approach, but use the following
Outlook VBA instead of the Word VBA I provided earlier, changing the various
Const values as appropriate.

Sub SendWithXLAddresses()
Const strMergeTemplateFolder = "amergetemplate"
Const strXLWorkbookFullname = "c:\xlfiles\eaddresses.xls"
Const strEAddressColumn = "Emailaddress"
Const strQuery = "SELECT " & strEAddressColumn & " FROM [eaddr$]"
Dim objMailItem As Outlook.MailItem
Dim objConnection As ADODB.Connection
Dim objRecordset As ADODB.Recordset

Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset
' Specify Excel 8.0 by using the Extended Properties
' property, and then open the Excel file specified by
' strDBPath. This should work for Excel 97 and later
With objConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strXLWorkbookFullname
With objRecordset
.Open _
Source:=strQuery, _
ActiveConnection:=objConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly
.MoveFirst
While Not .EOF
Set objMailItem =
Outlook.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders(strMergeTemplateFolder).Items(1).Copy
With objMailItem
.To = objRecordset.Fields(strEAddressColumn).Value
.Send
End With
Set objMailItem = Nothing
.MoveNext
Wend
.Close ' the recordset
End With
.Close ' the connection/workbook
End With

Set objRecordset = Nothing
Set objConnection = Nothing

End Sub


In this case you will need to use the Outlook VBA Tools|References menu
option to add a reference to

"Microsoft ActiveX Data Objects 2.x Library"

where the x could be several values - roughly speaking, use the highest one
you see (here', it's 8).

Peter Jamieson

Thanks for the suggestions. Since you were asking about my data source,
I've currently got it in an Excel spreadsheet. I had planned on using
Word Mail Merge, so I wanted something formatted appropriately. If a
specific application needs something simpler (i.e., it can't read either
an XLS or a CSV file), I could always just pick off the E-mail column and
put it to a plan text file -- i.e., one E-mail address per line. As I
said, I do *not* need to merge in any dynamic fields; it's purely a fixed
E-mail message with a fixed attachment. The only thing that varies from
one to another is the target E-mail address.

 

[Doug Robbins - Word MVP]

See the article "Mail Merge to E-mail with Attachments" at

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm


--
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