Mail merge w attachments

[Ted Horsch]

Thanks to Doug Robbins for the link to his article
http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.ht
m.

I am able to run this and to send a mail merge with
attachment by following the instructions in the article.
However, I can't figure out how to have my attachment and
also have a subject line and text in the body of the
email. Right now I can do either, but not both. If I run
the macro before clicking on the Merge to email icon, then
I am prompted for a file to , and after selecting the
directory/catalog file with email addresses and file
name/path it sends the emails flawlessly, just without any
subject line and I lose my body text. If I click on the
Merge to email icon instead of running the macro I am
prompted with the Merge to email dialog (to chose message
options, including subject line, and record selection).
But then my email merge runs but without the attachment.

It looks like Doug's solution is a popular one, and I
haven't seen any others yet. Any suggestions?

I'm running Office XP on Win2KPro.

Thanks.

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

I prepared an update to that article to accommodate the input of a subject
line about a month ago, but apparently it has not gone "live" yet.

Here is the revised code:

Sub emailmergewithattachments()



Dim Source As Document, Maillist As Document

Dim Datarange As Range

Dim Counter As Integer, i As Integer

Dim bStarted As Boolean

Dim oOutlookApp As Outlook.Application

Dim oItem As Outlook.MailItem

Dim mysubject As String, message As String, title As String



Set Source = ActiveDocument



‘ Check if Outlook is running. If it is not, start Outlook

On Error Resume Next

Set oOutlookApp = GetObject(, "Outlook.Application")

If Err <> 0 Then

Set oOutlookApp = CreateObject("Outlook.Application")

bStarted = True

End If



‘ Open the catalog mailmerge document

With Dialogs(wdDialogFileOpen)

.Show

End With

Set Maillist = ActiveDocument



‘ Show an input box asking the user for the subject to be inserted into the
email messages



message = "Enter the subject to be used for each email message." ' Set
prompt.

title = " Email Subject Input" ' Set title.

' Display message, title

mysubject = InputBox(message, title)



‘ Iterate through the rows of the catalog mailmerge document, extracting the
information

‘ to be included in each email.

Counter = 1

While Counter <= Maillist.Tables(1).Rows.Count

Source.Sections.First.Range.Cut

Documents.Add

Selection.Paste

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem

.Subject = mysubject

.Body = ActiveDocument.Content

Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range

Datarange.End = Datarange.End - 1

.To = Datarange

For i = 2 To Maillist.Tables(1).Columns.Count

Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range

Datarange.End = Datarange.End - 1

.Attachments.Add Trim(Datarange.Text), olByValue, 1

Next i

.Send

End With

Set oItem = Nothing

ActiveDocument.Close wdDoNotSaveChanges

Counter = Counter + 1

Wend



‘ Close Outlook if it was started by this macro.

If bStarted Then

oOutlookApp.Quit

End If



‘Clean up

Set oOutlookApp = Nothing

Source.Close wdDoNotSaveChanges


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Ted Horsch]

Thanks, Doug, for the updated code. It works great. It's
funny, though, because I can't get the body text to show
up in the emails that are produced. I have a test set of
three rows in my directory/catalog file, and the body text
that I set up in my merge document shows fine in the first
email that's generated, but the body text is completely
gone in the subsequent two emails. The subject line and
the attachments are working perfectly.

Any additional suggestions?

Thanks,

Ted

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

It sounds like you may not have executed the letter type mailmerge, or if
you did, rather than having the document that is produced by executing that
merge as the active document, you have the mailmerge main document as the
active document when the macro is executing.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP