Complex e-mail merge with unique attachment

[Lana]

I used the 'send as attachment' feature of the Word 'merge to e-mail' function to send unique mailmerged memos via e-mail (to maintain formatting, including letterhead), but didn't know how to do it with additional text in the actual e-mail. Some recipients were leery of opening an unexplained attachment so I needed to include a short message within the e-mails themselves by way of explanation. Is there a simple way to do that (maybe with INCLUDETEXT or something)? I printed the emailmergewithattachments() macro, but don't know how to get the unique results of the first mailmerge included in the Word mailmerge catalogue (i.e., <<Attachment1>> or <<Attachment2>>). If I could accomplish that, it may provide the solution. The mailmerged memos contain unique codes specific to each participant and MUST be sent only to the appropriate people. Suggestions?

 

[Doug Robbins - Word MVP]

As I understand it, you want the body of the email message to convey the
same message to each recipient while and individual attachment goes to each.

If the text that you want to be included in the body of the email message is
included in the ActiveDocument, when you run this modified version of the
macro at http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm (which
is I assume to what you are referring), it should do what you want. Of
course, in this case, the only mailmerge that you need to execute is the
catalog or directory type merge.

Sub emailmergewithattachments()

'Modified so that the body of the email message is the same for all
recipients.

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
Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.Subject = mysubject
.Body = Source.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
Counter = Counter + 1
Wend
Source.Close wdDoNotSaveChanges

' Close Outlook if it was started by this macro.

If bStarted Then
oOutlookApp.Quit
End If

End Sub


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

I used the 'send as attachment' feature of the Word 'merge to e-mail'

function to send unique mailmerged memos via e-mail (to maintain formatting,
including letterhead), but didn't know how to do it with additional text in
the actual e-mail. Some recipients were leery of opening an unexplained
attachment so I needed to include a short message within the e-mails
themselves by way of explanation. Is there a simple way to do that (maybe
with INCLUDETEXT or something)? I printed the emailmergewithattachments()
macro, but don't know how to get the unique results of the first mailmerge
included in the Word mailmerge catalogue (i.e., <<Attachment1>> or
<<Attachment2>>). If I could accomplish that, it may provide the solution.
The mailmerged memos contain unique codes specific to each participant and
MUST be sent only to the appropriate people. Suggestions?

 

[Doug Robbins - Word MVP]

Hi Lana,

If think this will show you how to split the document created by the merge
into individual files, saving each with a filename that you have in the
datasource in the first instance (e.g. the persons name or something esle
that is unique e.g the number in an autonumber field in the table) Then you
would use that same data in the catalog document in the mailmerge to email
with attachments macro, or you could set up a catalog mailmerge that could
be used for both purposes - starting to get the idea that I like catalog
mailmerges <g>:

Here's a method that I have used that involves creating a separate
catalog type mailmerge maindocument which creates a word document containing
a table in each row of which would be your data from the database that you
want to use as the filename.

You first execute that mailmerge, then save that file and close it. Then
execute the mailmerge that you want to create the separate files from and
with the
result of that on the screen, run a macro containing the following code
and when the File open dialog appears, select the file containing the table
created by the first mailmerge

Dim Source As Document, oblist As Document, DocName As Range, DocumentName
As String
Set Source = ActiveDocument
With Dialogs(wdDialogFileOpen)
.Show
End With
Set oblist = ActiveDocument
Counter = 1
While Counter <= oblist.Tables(1).Rows.Count
Set DocName = oblist.Tables(1).Cell(Counter, 1).Range
DocName.End = DocName.End - 1

'Change the path in the following command to suit where you want to save
the documents.
DocumentName = "I:\WorkArea\Documentum\" & DocName.Text
Source.Sections.First.Range.Cut
Documents.Add
Selection.Paste
ActiveDocument.SaveAs filename:=DocumentName, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
ActiveWindow.Close
Counter = Counter + 1
Wend


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

Hi Doug,
I appreciate your macros very much indeed. However, my problem is prior

to that I think. I create a memo in Word and mail merge it with a name and
a code from fields in an Access table. Now I have one Word document with
300+ unique memos in it. I assume that I must now split those memos into
individual files ( I can again use someone's great macro to accomplish this
step). My problem is - how do I now get the path names for these memos back
into my source file (the Access table) as a new field with each unique memo
path in the row for the correct person? Again, I'm assuming, that I must do
this in order to then create the 'catalogue' mail merge that would include
in the first cell of each table row the individual's e-mail address and
include in the second cell of each row the path to the memo file for that
person (<<Attachment1>>)? If I am interpreting it correctly, this is the
point at which I could then use your emailmergewithattachments() macro. It
would pick up the e-mail address from cell 1 and the attachment from the
document path in cell 2 of every row, thereby creating an e-mail message (my
active document) with a unique attachment (as indicated by the document
path) for each person. My problem is creating and pointing to the document
path. If you see something simple that I am overlooking in this process,
please let me know. I have already done the project, albeit less than
ideally as I explained earlier, but I would like to know how to solve this
problem for future projects. It is even more complicated because I have two
different memos, some of which go to some people and some of which go to
others, but I would be willing to create two tables and work on them
individually if I must.

 

[Lana]

Hello again
I think I get the idea - can't wait to try it on Monday. I'll let you know if it works out okay
Lana

 

[Doug Robbins - Word MVP]

Hi Lana,

The following should overcome the blank page problem:

' Throw Away Macro created by Doug Robbins
'
Dim Source As Document, oblist As Document, DocName As Range, DocumentName
As String
Dim i As Long, doctext As Range, target As Document
Set Source = ActiveDocument
With Dialogs(wdDialogFileOpen)
.Show
End With
Set oblist = ActiveDocument
Counter = 1
For i = 1 To oblist.Tables(1).Rows.Count
Set DocName = oblist.Tables(1).Cell(i, 1).Range
DocName.End = DocName.End - 1

'Change the path in the following command to suit where you want to save
the documents.
DocumentName = "I:\WorkArea\Documentum\" & DocName.Text
Set doctext = Source.Sections(i).Range
doctext.End = doctext.End - 1
Set target = Documents.Add
target.Range.FormattedText = doctext
target.SaveAs FileName:=DocumentName
target.Close
Next i

The way in which you got the document into the catalog is fine. The path
could however have been entered into mailmerge with attachments macro.

Regarding the mismatch between the fullname and the name on the attachment
and the email, I think you need to set all of this up so that you have just
the single query to use as the datasource.

Re the catalog merge, what I do is set up the catalog merge main document
with a single row table with nothing after the table with the mergefields in
the cells of that table. Then when you execute the merge, everything will
be inserted into a table.

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

Hi Doug,

Success!! (although some snags - maybe you can clear them up for me).

First, each letter saved with a Section Break (new page) at the end,

creating an unnecessary blank page on every attachment. Have you ever
encountered this? Is there a way to get rid of it from within the macro?

Second, I don't know yet how you thought I should get the document path

into a catalog table - I did it by creating a query and adding a field,
Attachment, that began with the text of my document path, followed by
([ID]), followed by text ".doc". This worked just great, but if you do it
some other way I would be glad to hear of it. The ID was the only unique
field in my database.

Third, and most difficult for me to immediately comprehend, the merged

information within the e-mail itself included the "fullname". The
"fullname" came from a separate query and did not match the name on the
attachment or the e-mail, which of course both came from the same table so
obviously matched. What should I have done to ensure that the name within
the e-mail message would match that on the attachment? I must solve this
problem or will feel unsafe in using a mailmerged e-mail message with an
attachment.

Lastly, I have not used Catalog mailmerges before, but I was a bit

surprised that it did not automatically create a table, I had to insert -
table in the beginning or convert text to table later. Is that the way it
is designed to work?

I know you guys must hear this everyday, but I couldn't begin to tell you

how comforting it is to know that you can have what seems to be a monumental
problem and get such expert, "to the point" help in such a timely manner.
What a blessing. Thank you all!

 

[Doug Robbins - Word MVP]

One thing I forgot, we don't hear that everyday, so thanks.

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

Hi Doug,

Success!! (although some snags - maybe you can clear them up for me).

First, each letter saved with a Section Break (new page) at the end,

creating an unnecessary blank page on every attachment. Have you ever
encountered this? Is there a way to get rid of it from within the macro?

Second, I don't know yet how you thought I should get the document path

into a catalog table - I did it by creating a query and adding a field,
Attachment, that began with the text of my document path, followed by
([ID]), followed by text ".doc". This worked just great, but if you do it
some other way I would be glad to hear of it. The ID was the only unique
field in my database.

Third, and most difficult for me to immediately comprehend, the merged

information within the e-mail itself included the "fullname". The
"fullname" came from a separate query and did not match the name on the
attachment or the e-mail, which of course both came from the same table so
obviously matched. What should I have done to ensure that the name within
the e-mail message would match that on the attachment? I must solve this
problem or will feel unsafe in using a mailmerged e-mail message with an
attachment.

Lastly, I have not used Catalog mailmerges before, but I was a bit

surprised that it did not automatically create a table, I had to insert -
table in the beginning or convert text to table later. Is that the way it
is designed to work?

I know you guys must hear this everyday, but I couldn't begin to tell you

how comforting it is to know that you can have what seems to be a monumental
problem and get such expert, "to the point" help in such a timely manner.
What a blessing. Thank you all!

 

[Doug Robbins - Word MVP]

Hi Lana,

You should use the second macro to replace the first one.

If Outlook is set as the default Mail Client, then I would expect it to be
the program that is used.

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

Hi Doug,

I ran the new macro you gave me and it did just as you thought – removed

the Section Breaks (new page) command from the bottom of each of the memos,
making them just the one page documents they should be. Question – can I
now delete the earlier macro I copied and always use this new one?

Will have to try the query suggestion later – I’m thinking maybe if I can

sort both the data in the query and the document path table to match (in ID
number order) it might work – can’t quite convince myself yet. But will try
to figure it out. In my current case I can certainly use a static e-mail
message so it wouldn’t even have to come into play, but I’d like to know I
could do a merge with the e-mail message if it was necessary.

Wanted to let you know that I downloaded the Express ClickYes program, but

found that (for whatever reason) I did not need it – the warning message
does not display on my computer. I guess there’s a setting somewhere that
is set to suppress the message. At any rate, I unloaded it after
successfully testing the process without it.

Does your macro ensure that the e-mails will be sent from Outlook? The

ones sent from the built-in Word merge to e-mail function went from Outlook
Express? I prefer Outlook.