Go Back   Access World Forums > Apps and Windows > Word

 
Reply
 
Thread Tools Rating: Thread Rating: 35 votes, 5.00 average. Display Modes
Old 07-16-2008, 10:57 AM   #1
RossWindows
Que?
 
RossWindows's Avatar
 
Join Date: Feb 2008
Location: California
Posts: 410
Thanks: 0
Thanked 6 Times in 5 Posts
RossWindows is on a distinguished road
Mail Merge - Dynamic subject line?

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?

RossWindows is offline   Reply With Quote
Old 12-08-2008, 12:08 AM   #2
Tanya
Access Novice
 
Join Date: Sep 2008
Location: Sydney, Australia
Posts: 165
Thanks: 0
Thanked 0 Times in 0 Posts
Tanya is on a distinguished road
Re: Mail Merge - Dynamic subject line?

Your question is too vague. Why don't you upload a copy of what you are trying to work with?
Tanya is offline   Reply With Quote
Old 10-21-2009, 07:00 AM   #3
hutchinsfairy
Registered User
 
Join Date: Oct 2009
Posts: 4
Thanks: 0
Thanked 2 Times in 2 Posts
hutchinsfairy is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.

hutchinsfairy is offline   Reply With Quote
The Following User Says Thank You to hutchinsfairy For This Useful Post:
motlaghjs (02-09-2014)
Old 10-21-2009, 08:39 AM   #4
RossWindows
Que?
 
RossWindows's Avatar
 
Join Date: Feb 2008
Location: California
Posts: 410
Thanks: 0
Thanked 6 Times in 5 Posts
RossWindows is on a distinguished road
Re: Mail Merge - Dynamic subject line?

Now that is a great 1st post! I'll definately be hanging on to that.
__________________
"I prefer thinking about what's inside the box." - Me

Post hoc ergo propter hoc.

- Access '97, 2003, 2007, SQL Server 2008
RossWindows is offline   Reply With Quote
Old 10-22-2009, 02:08 AM   #5
hutchinsfairy
Registered User
 
Join Date: Oct 2009
Posts: 4
Thanks: 0
Thanked 2 Times in 2 Posts
hutchinsfairy is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.
hutchinsfairy is offline   Reply With Quote
Old 11-10-2009, 06:08 AM   #6
philtulip
Registered User
 
Join Date: Nov 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
philtulip is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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!
philtulip is offline   Reply With Quote
Old 11-10-2009, 08:07 AM   #7
hutchinsfairy
Registered User
 
Join Date: Oct 2009
Posts: 4
Thanks: 0
Thanked 2 Times in 2 Posts
hutchinsfairy is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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

hutchinsfairy is offline   Reply With Quote
The Following User Says Thank You to hutchinsfairy For This Useful Post:
motlaghjs (02-09-2014)
Old 11-10-2009, 08:16 AM   #8
philtulip
Registered User
 
Join Date: Nov 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
philtulip is on a distinguished road
Re: Mail Merge - Dynamic subject line?

Quote:
Originally Posted by hutchinsfairy View Post
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.
philtulip is offline   Reply With Quote
Old 11-11-2009, 12:51 AM   #9
hutchinsfairy
Registered User
 
Join Date: Oct 2009
Posts: 4
Thanks: 0
Thanked 2 Times in 2 Posts
hutchinsfairy is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.
hutchinsfairy is offline   Reply With Quote
Old 11-11-2009, 01:12 AM   #10
philtulip
Registered User
 
Join Date: Nov 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
philtulip is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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 is offline   Reply With Quote
Old 11-11-2009, 01:15 AM   #11
philtulip
Registered User
 
Join Date: Nov 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
philtulip is on a distinguished road
Re: Mail Merge - Dynamic subject line?

Never mind, it does work! It's because my macros were disabled! Hurrah!
philtulip is offline   Reply With Quote
Old 12-06-2010, 10:03 AM   #12
meazcc
Newly Registered User
 
Join Date: Dec 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
meazcc is on a distinguished road
Re: Mail Merge - Dynamic subject line?

This was great! Thank you so much!
meazcc is offline   Reply With Quote
Old 02-01-2011, 01:38 PM   #13
mikeo1313
Newly Registered User
 
Join Date: May 2010
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
mikeo1313 is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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
mikeo1313 is offline   Reply With Quote
Old 02-01-2011, 01:47 PM   #14
RossWindows
Que?
 
RossWindows's Avatar
 
Join Date: Feb 2008
Location: California
Posts: 410
Thanks: 0
Thanked 6 Times in 5 Posts
RossWindows is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.
__________________
"I prefer thinking about what's inside the box." - Me

Post hoc ergo propter hoc.

- Access '97, 2003, 2007, SQL Server 2008
RossWindows is offline   Reply With Quote
Old 02-01-2011, 01:56 PM   #15
mikeo1313
Newly Registered User
 
Join Date: May 2010
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
mikeo1313 is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.

mikeo1313 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced Mail Merge Question adam.greer General 7 02-09-2007 02:43 AM
Mail merge query results garywood84 Queries 16 09-23-2006 12:20 PM
Mail Merge from an SQL server database tigernick SQL Server 2 09-05-2006 12:51 AM
[SOLVED] problems with mail merge and combo boxes discman9 Queries 5 11-04-2003 12:31 PM
Mail Merge from a Query expublish Forms 1 03-07-2002 08:36 AM




All times are GMT -8. The time now is 03:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World