Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / March 2004

Tip: Looking for answers? Try searching our database.

Mail merge w attachments

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ted Horsch - 04 Mar 2004 23:38 GMT
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 CHARACTERS FROM EMAIL ADDRESS - 05 Mar 2004 03:30 GMT
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

Signature

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

> Thanks to Doug Robbins for the link to his article
> http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.ht
[quoted text clipped - 21 lines]
>
> Thanks.
Ted Horsch - 05 Mar 2004 19:56 GMT
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
>-----Original Message-----
>I prepared an update to that article to accommodate the input of a subject
[quoted text clipped - 122 lines]
>
>> Thanks to Doug Robbins for the link to his article

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.ht
>> m.
>>
[quoted text clipped - 21 lines]
>
>.
Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS - 05 Mar 2004 22:27 GMT
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.

Signature

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

> Thanks, Doug, for the updated code.  It works great.  It's
> funny, though, because I can't get the body text to show
[quoted text clipped - 188 lines]
>>
>>.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.