How do I insert a field into the subject box for each email when .

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

morbitzer - 11 Mar 2005 14:31 GMT

I want to put one of my Excel fields in the subject line for each email when
I am doing an email merge in Word. Does anyone know how? It only allows you
to do a generic subject that gets applied to every email.

Reply to this Message

Peter Jamieson - 11 Mar 2005 17:08 GMT

The only reasonably simple way I know to do this is to write VBA to use the
MailMergeBeforeRecord event to modify the subject for each message. It will
only work in Word 2002 and 2003.

If you aren't familiar with VBA this may be difficult to follow (there is a
useful article on the Word MVP site at http://word.mvps.org that may help
you get started), but for example you need to create your mail merge main
document, then in the VBA Editor, add

a. A normal module called (say) AutoMacros with the following code:

Dim X As New EventClassModule
Sub AutoOpen()
Set X.MyApp = Word.Application
End Sub

b. a class module called EventClassModule that contains the following code:

Public WithEvents MyApp As Word.Application

Private Sub MyApp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel
As Boolean)
Doc.MailMerge.MailSubject =
Doc.MailMerge.DataSource.DataFields("mysubject")

End Sub

where mysubject is the name of the field that contains the data you want to
use as the subject for each e-mail.

You need to make sure that you have run the AutoOpen macro before you start
the merge to e-mail/

Peter Jamieson

>I want to put one of my Excel fields in the subject line for each email
>when
> I am doing an email merge in Word. Does anyone know how? It only allows
> you
> to do a generic subject that gets applied to every email.

Reply to this Message

sayling - 06 Jul 2006 20:48 GMT

I have sweated for months on how to do this in Word 2000, with the source a
tab delimited txt file, and have finally come up with a solution.

Roughly speaking, I inserted the Title field into the document with the
merge field names inside it within quotes, ie
{TITLE "{ MERGEFIELD <field name from source doc>} { MERGEFIELD <another
field name from source doc>} on { MERGEFIELD <a date field name from source
doc>}"\* MERGEFORMAT}

This places the chosen fields into the Document Properties Title field.

A little VBA (my first real attempt at writing some!) was then written:

Sub Merge_with_title_as_Subject
Dim count As Integer
count = 1

ChangeFileOpenDirectory "C:\<path to file already created as a merge
document>"
Documents.Open FileName:="<merge document>.doc"
Do
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.MailAsAttachment = True
.MailAddressFieldName = "<email address field in source file>"
.MailSubject = "Site Visit Report for " & _
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle)
.SuppressBlankLines = True
With .DataSource
.FirstRecord = count
.LastRecord = count
End With
.Execute Pause:=True
.DataSource.ActiveRecord = wdNextRecord
count = count + 1
End With
Loop Until ActiveDocument.MailMerge.DataSource.LastRecord =
ActiveDocument.MailMerge.DataSource.ActiveRecord
ActiveDocument.Close wdDoNotSaveChanges
Application.Quit
End Sub

My requirement was to have this macro auto run from a Scheduled Tasks
copmmand, to then open the document to be merged (which is already associated
with the data source), merge the first record, then the next then the next
and then shut down Word, all unattended. As the merged document does not get
saved and thus is not given a filename, this was the only way I could see of
doing it.

Now, whereas this works, I daresay that my coding is pretty ropey - if
anyone cares to tidy it up, I'd be most grateful (that's if anyone /sees/
this post, having answered a post that's more than six months old!)

Anyhoo, the same principal should be useable for a merge whatever the
source, I would have thought :)

Simon Ayling

> The only reasonably simple way I know to do this is to write VBA to use the
> MailMergeBeforeRecord event to modify the subject for each message. It will
[quoted text clipped - 36 lines]
> > you
> > to do a generic subject that gets applied to every email.

Reply to this Message

Peter Jamieson - 07 Jul 2006 01:14 GMT

Hi Simon,

I doubt if it improves on your effort but this is one of the three "do a
merge for each record at a time" macros I've posted over time - although I
think this particular version is from a couple of years ago - a useful thing
is that it shows how to set up the Subject directly from one of the fields
("tradeshow" in this case) in the Data Source.

Also, the way I detect the last record is a bit awkward, but I think I ended
up doing it that way because ActiveRecord didn't behave as I expected with
some data sources. I can't remember the details.

Peter Jamieson
Sub ProduceOneEmailPerSourceRec()
'

' NB, needs bettor error management and doubtless other things a VBA expert
' will point out.

Dim intSourceRecord
Dim objMerge As Word.MailMerge
Dim TerminateMerge As Boolean

' Need to set up this object as the ActiveDocument changes when the
' merge is performed. Besides, it's clearer.

Set objMerge = ActiveDocument.MailMerge
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
TerminateMerge = False

Do Until TerminateMerge
.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

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

.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = = wdSendToEmail
' set up the field containing the e-mail address
.MailAddressFieldName = "eaddress"
' Set up the subject
.MailSubject = .DataSource.DataFields("tradeshow").Value
.Execute

intSourceRecord = intSourceRecord + 1
End If
Loop
End With

>I have sweated for months on how to do this in Word 2000, with the source a
> tab delimited txt file, and have finally come up with a solution.
[quoted text clipped - 105 lines]
>> > you
>> > to do a generic subject that gets applied to every email.

Reply to this Message