Mail Merge - Dynamic subject line? (1 Viewer)

Local time
Today, 07:41
Joined
Feb 25, 2008
Messages
410
I know in mail merge you can specify what you want in the subject line, but that causes the subject line to be the same for all records sent in that batch.

Is there a way to have the subject line change for each record to include the "PO#" that is included in the <<PO Number>> field in the body of each record?
 

Tanya

Access Novice
Local time
Today, 07:41
Joined
Sep 7, 2008
Messages
165
Your question is too vague. Why don't you upload a copy of what you are trying to work with?
 

hutchinsfairy

New member
Local time
Today, 07:41
Joined
Oct 21, 2009
Messages
4
For anyone else looking for the answer Try:


Code:
Dim WithEvents wdapp As Application
Dim EMAIL_SUBJECT As String
Dim FIRST_RECORD As Boolean


Private Sub Document_Open()

    Set wdapp = Application
    ThisDocument.MailMerge.ShowWizard 1
   
End Sub

Private Sub Document_Close()

    Set wdapp = Nothing
    
End Sub


Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
    Dim i As Integer
    
    With ActiveDocument.MailMerge
    
        If FIRST_RECORD = True Then 
            EMAIL_SUBJECT = .MailSubject
            FIRST_RECORD = False
        Else .MailSubject = EMAIL_SUBJECT
        End If

        i = .DataSource.DataFields.Count
        
        Do While i > 0
            .MailSubject = Replace(.MailSubject, "<" & .DataSource.DataFields(i).Name & ">", .DataSource.DataFields(i).Value, , , vbTextCompare)
            i = i - 1
        Loop

    End With
    
     
End Sub


Private Sub wdapp_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)

    FIRST_RECORD = True
    
End Sub

Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)

    ActiveDocument.MailMerge.MailSubject = EMAIL_SUBJECT
     
End Sub
This will intercept Mail Merge requests and parse the subject line for merge fields. Just put chevrons round the name of the Merge Field.

e.g. "Reference Request for <Applicant_Name>"

You can find Merge Field names by using them in your document and pressing Alt+F9.

This only works if you initiate the Mail Merge via the task pane and not if you use the Toolbar button.
 
Local time
Today, 07:41
Joined
Feb 25, 2008
Messages
410
Now that is a great 1st post! I'll definately be hanging on to that.
 

hutchinsfairy

New member
Local time
Today, 07:41
Joined
Oct 21, 2009
Messages
4
Thanks!

Bear in mind that it will error if you try to merge to another destination (printer etc)

You can wrap the code within the subs with:

Code:
If ActiveDocument.MailMerge.Destination = wdSendToEmail Then

End if
to avoid this.
 

philtulip

New member
Local time
Today, 14:41
Joined
Nov 10, 2009
Messages
4
I can't seem to get this to work.

I currently have an e-mail merge which I am creating through Word.

I have created a new Macro and pasted the code you have provided but I get the following error when running the macro (at what point do I run it? am I doing this correctly?) - "Compile Error: Syntax error"

Sorry for sounding a bit of a novice but if I can get this to work it would be excellent.

A bit of background - I need the subject line to read "<ID> notice" where ID is a merge field from an excel list.

Thanks!
 

hutchinsfairy

New member
Local time
Today, 07:41
Joined
Oct 21, 2009
Messages
4
With your letter open press Alt+F11 and double click "ThisDocument" in the window that opens (it should be near the top left in a pane called "Project - Project").

Paste the entire code into the blank pane that opens and save. Close the Visual Basic Editor and the Word document and re-open the word document.

Run the merge from the wizard that should appear to the right of the document.

Generally the ID of the merge field is the Excel column title with underscores instead of spaces.

Hope that's some help
 

philtulip

New member
Local time
Today, 14:41
Joined
Nov 10, 2009
Messages
4
Generally the ID of the merge field is the Excel column title with underscores instead of spaces.

So when I get to the stage of entering the subject line, if the merge field I wanted to insert was "StudentID" i'd enter <_StudentID_> ?

Thanks in advance.
 

hutchinsfairy

New member
Local time
Today, 07:41
Joined
Oct 21, 2009
Messages
4
If the column title in Excel is "StudentID" then try "<StudentID>" (without quotes).
If you know what the MergeField is called (which can be different from the column title in Excel) then just add the chevrons.
 

philtulip

New member
Local time
Today, 14:41
Joined
Nov 10, 2009
Messages
4
I have tried your suggestion and it has been unsuccessful.

I have copy/pasted your code exactly how you have said (I don't need to replace any of this code with my own?) and I have also put <ID> (my merge field in excel AND word) in the subject line but this does not work.

Does it matter that my file has been saved as .docm? does it need to be saved as another file type?

Cheers.
 

philtulip

New member
Local time
Today, 14:41
Joined
Nov 10, 2009
Messages
4
Never mind, it does work! It's because my macros were disabled! Hurrah!
 

mikeo1313

Registered User.
Local time
Today, 10:41
Joined
May 27, 2010
Messages
50
I pasted the code and its not working for me in word 2007, but, I'm also not using the task pane, just the ribbon.

please help!. I'm using an excel data souce and want to use a certain column for the subject of the word-outlook mail merge
 
Local time
Today, 07:41
Joined
Feb 25, 2008
Messages
410
Well, I know for sure that it works with Office 2003. That could be the source of the problem, but not necessarily so...

What steps have you taken so far? and have you tried anything else without luck? Try to be as specific as possible.
 

mikeo1313

Registered User.
Local time
Today, 10:41
Joined
May 27, 2010
Messages
50
I created my message, added my fields, saved my doc as a macro enabled file, saved the macro code in post exactly as in msowrdocs->thisdocument. enabled all macros via office->word options -> trust center,,,, used both <<sub>> & <sub> and nothing.
 
Local time
Today, 07:41
Joined
Feb 25, 2008
Messages
410
So you already tried this? Does the wizard appear at this point?
...
Paste the entire code into the blank pane that opens and save. Close the Visual Basic Editor and the Word document and re-open the word document.

Run the merge from the wizard that should appear to the right of the document...

Also like hutchinsfairy pointed out, this only works from the task pane; not the toolbar button. The ribbon in 2007 might be in the same boat so-to-speak, as the equivalent toolbar button, so I would definitely try and find a computer with 2003 and see if it works there; at least then you might narrow down the problem.

(or post it here if you're able to; I have 2003 and would be willing to test it for you)
 

mikeo1313

Registered User.
Local time
Today, 10:41
Joined
May 27, 2010
Messages
50
I've been using the ribbon button and only have a 2007 & 2010 office install on other computers. How would I use the toolbar in word 2007 as I've been usng the ribbon???
 

mikeo1313

Registered User.
Local time
Today, 10:41
Joined
May 27, 2010
Messages
50
ended up working, I had to "compile" project. great post!
 

Gr8Veto

New member
Local time
Today, 07:41
Joined
Oct 25, 2011
Messages
1
When I tried to "compile" the project it didn't like something about the ".ShowWizard". The error I got was "Method or data member not found".

I am using Word 2011 for Mac.

Any ideas...?
 

AnnSki26

New member
Local time
Today, 09:41
Joined
May 25, 2012
Messages
1
Thank you so much for this code. It solved my problem perfectly!
 

Users who are viewing this thread

Top Bottom