Mail Merge - Dynamic subject line?

sorry to sound like a fool.. but where in the code do I put the "<subject>" or excel data field?
 
In Word, the "<<subject>>" data field is added directly on your word document, not in the code.
It's been a long time since I used merge but if you still need help, just let us know.
 
Hi all,

Please help I'm tearing my hair out here!

I've successfully used the above code in the past using Word & Outlook 2007. I've no idea why but I can no longer get it to work correctly.

If I paste the code into a Module and try to compile it objects to the first line "Dim WithEvents wdapp As Application" and errors - "Only valid in object module"

I have also tried pasting the code into a class module and it does compile OK. However when I try processing the merge with the relevant fields in chevrons it does not merge the subject line.

I've no idea how I got it working last time I used it and why I can't this time. I'd be very grateful for any advice. Please bear in mind I have no experience programming in VBA but as I'm using pre-written code this shouldn't matter!

Many thanks for any input
Tom
 
Hmm, interesting problem tnm. First I want to clarify; what version of Word are you using now? (your initial post is a little ambiguous). Secondly (after thinking about it for a minute), it looks a lot like a missing library reference, but honestly it's been a while.

Before you get too far, remember; as stated in previous posts, certain newer versions of Word that utilize the Ribbon Bar can encounter problems when used with the aforementioned code. (I don't know if that's relevant here; just going off what I see at a glance).
 
Last edited:
Hi Ross,

Thanks for the quick response. I'm using Office 2007, same as last time I sucessfully used the code. Working on a different machine but the Office installation is standard throghout our network.

I do start the merge from the ribbon as I don't think Word 2007 includes the task pane that was present in 2003.

I've tried adding the code to both the document I'm working on and to Normal without success.

I'd appreciate any advice.

Regards
Tom
 
Hi tnm, sorry, I've edited my last post. I may have edited it while you were posting your last response... just posting this to prompt any automated notifications for you.
 
Hi Ross,

I'm also trying to use this piece of VBA, but dont quite understand how to point it at the column I'm looking for. I'm using Office 2007. My Data Source is Excel. The subject line I want to use is found in a column named "mysubjectfield". I added that as a merge field onto the word doc but that did not change the subject line. I suspect I've missed something here, should I rename the excel column which contains the subject lines?

RossWindows said:
In Word, the "<<subject>>" data field is added directly on your word document, not in the code.
It's been a long time since I used merge but if you still need help, just let us know.
Click to expand...
 
I have a Word 2010 document linked to an Access 2010 data source. When a user clicks a button in Access, the Word document loads and performs a email merge using the below VBA code:

Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
.MailSubject = ActiveDocument.MailMerge.DataSource.DataFields("Return_code").Value
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With
.Execute Pause:=False
End With
End Sub

However, as the .mailsubject part is not in the loop it is only retrieving the first Return Code. I have tried to integrate in the loop to no avail. Also, how do I add static text to the Subject, I need something like "Your Return Code" + "Return Code"

Thank you for any help.

Regards,

Michael
 
Thanks for getting back to me. I managed to resolve this though.
 
The VBA code works great! Thanks.

The other function I'm looking for is to be able to cc and bcc other contacts from the merge. Could someone provide the VBA code to do that and mention where in it needs to go since I'm using the code for dynamic subject line as well.

Thanks!
 
If you have access to an SMTP server (gmail even), then you can use the CDONTS library. There are plenty of examples floating around. Check the code repository. In this case, your email code would probably go right after the 'Loop' tag, before the 'End With'. If sending large batches through Gmail, beware of their Sending Limits.
 
Thank you very much. Unfortunately I don't actually know how to write this code at all. I have a very basic understanding. I'm guessing the original code, which looks like this:
Code: 
""
        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
""

....needs to be modified to look like this?

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

  Set objNewMail.bcc = CreateObject("CDONTS.NewMail".bcc)
  objNewMail.Send "user2@example.com", "user1@example.com", "Hello", _
                  "I sent this in 3 statements!", 0 ' low importance
  Set objNewMail = Nothing

    End With
""

I really have no idea how this is supposed to look. Thanks for the help.

RossWindows said:
If you have access to an SMTP server (gmail even), then you can use the CDONTS library. There are plenty of examples floating around. Check the code repository. In this case, your email code would probably go right after the 'Loop' tag, before the 'End With'. If sending large batches through Gmail, beware of their Sending Limits.
Click to expand...
 
I have a working access db sending emails via the CDO Object Library.

I sends email based on a txt file template that has dynamic fields e.g. name, order number etc. For the actual send mail bit, use this:

Code: 
 Function SendMail(strTo, strFrom, strSubject, strBody, msgType)
Set objCDOConf = CreateObject("CDO.Configuration")
 With objCDOConf
    ' ** OUT GOING SMTP SERVER **
    .Fields("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "ExchServerName"
    ' ** SMTP PORT **
    .Fields("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
    ' ** CDO PORT **
    .Fields("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
    ' ** TIMEOUT **
    .Fields("[URL]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout[/URL]") = 60
    .Fields.Update
End With
 Set objMail = CreateObject("CDO.Message")
Set objMail.Configuration = objCDOConf
 objMail.To = strTo
objMail.From = strFrom
objMail.Subject = strSubject
 'bcc me to check emails are sent
objMail.Bcc = [EMAIL="somebody@somewhere.com"]somebody@somewhere.com[/EMAIL]
 If msgType = TEXTMSG Then
  objMail.TextBody = strBody
Else
  objMail.htmlBody = strBody
End If
 objMail.Fields.Update
objMail.Send
 
Set objMail = Nothing
 End Function
Does this help?
 
Hi there,

I've implemented the code from early on in this thread. Have replaced these two lines:

Else .MailSubject = EMAIL_SUBJECT

&

ActiveDocument.MailMerge.MailSubject = EMAIL_SUBJECT

with:

Else: .MailSubject = Service_Request_ID

&

ActiveDocument.MailMerge.MailSubject = Service_Request_ID



When I do a test merge with 3 records, using Confirm that (<Service_Request_ID>) is closed as the subject line, all three send but only the first one has the subject line with the custom entry as desired. The last 2 are blank; when I go to run mail merge again from Word the subject line is blanked out and I have to enter it again.

I'm guessing something in the VB code is blanking the subject line for any records after the first one is processed, but I have almost no VB understanding.

Using: Word 2013 and Excel 2013.

Any suggestions?

Cheers

D
hutchinsfairy said:
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.
Click to expand...
 
I stumbled across this thread, and want to thank you all for your help!
I am using Word and Excel 2013, and because it wasn't working as written (for Office 2013 stuff),I needed to update the code. After a few days of working with it (dusting off the scripting skills), I got it to work!

(Note - this does not address the CC or BCC functionality - just the dynamic subject line)

Same assumptions as before:
- Using Wizard, not toolbar button (since the code activates the wizard by default, this shouldn't be an issue.)
- Only handles one dynamic subject line variable
- Assumes the subject line variable is wrapped in <ANGLE_BRACKETS>

Code: 
Public WithEvents wdapp As Word.Application


Dim ORIG_EMAIL_SUBJECT As String
Dim FIRST_RECORD As Boolean
Dim SUBJ_VAR As String
Dim COL_NUM As Integer



Private Sub Document_Open()

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

Private Sub Document_Close()

    Set wdapp = Nothing
    
End Sub

Public Function extract_value(str As String) As String
    Dim openPos As Integer
    Dim closePos As Integer
    Dim midBit As String
     
     On Error Resume Next
    openPos = InStr(str, "<")
     On Error Resume Next
    closePos = InStr(str, ">")
     On Error Resume Next
    midBit = Mid(str, openPos + 1, closePos - openPos - 1)
    If openPos <> 0 And Len(midBit) > 0 Then
        extract_value = midBit
    Else
        extract_value = "NO VARIABLE FOUND"
    End If
End Function


Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
    'Called once per record (row in spreadsheet).
    
    With ActiveDocument.MailMerge
        
        'Get or reset the .MailSubject MailMerge property
        If FIRST_RECORD = True Then
            ORIG_EMAIL_SUBJECT = .MailSubject
            FIRST_RECORD = False 'Set the flag so it won't change the ORIG_EMAIL_SUBJECT anymore
            SUBJ_VAR = extract_value(.MailSubject) 'SUBJ_VAR is the what I want to extract from the subject line and change - this is input in MailMerge Wizard in angle brackets "<  >"
            
        Else:
            .MailSubject = ORIG_EMAIL_SUBJECT 'Reset from customized .MailSubject back to original for next mailmerge iteration
                      
        End If

        'Updated for Office 2013 Object model. I have not tested on Office 2010 object model.
        .MailSubject = Replace(.MailSubject, "<" & SUBJ_VAR & ">", .DataSource.DataFields(SUBJ_VAR).Value, , , vbTextCompare)
        
        
    End With
     
End Sub


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

    'Initialize for first use
    FIRST_RECORD = True
    
End Sub

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

    'Reset back to original when done.
    ActiveDocument.MailMerge.MailSubject = ORIG_EMAIL_SUBJECT
     
End Sub
 
Thank you all for this awesome VBA code. I was wondering what a previous user stated about adding a line to include someone to include as a CC.

It would be great to be able to set the email address or addresses in the existing VBA code itself because I don't think I have access to an SMTP server. I also might need to CC 2 people. Ex. email1;email2.

Any help is greatly appreciated.
 
Hi, i am a excel user really need this function, but i try many times it still doesn't fit for my mail file? would you mind help me step show me how to do it.?
thanks
my phone number is 253-666-5688
Very appreciated for your help!!!


hutchinsfairy said:
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.
Click to expand...
 
Hi, i am a excel user really need this function, but i try many times it still doesn't fit for my mail file? would you mind help me step show me how to do it.?
thanks
my phone number is 253-666-5688 my name is Allen
Very appreciated for your help!!!




RossWindows said:
If you have access to an SMTP server (gmail even), then you can use the CDONTS library. There are plenty of examples floating around. Check the code repository. In this case, your email code would probably go right after the 'Loop' tag, before the 'End With'. If sending large batches through Gmail, beware of their Sending Limits.
Click to expand...
 
Hi, i am a excel user really need this function, but i try many times it still doesn't fit for my mail file? would you mind help me step show me how to do it.?
thanks
my phone number is 253-666-5688
Very appreciated for your guys help!!!
 
Tried using this in Word 2016 in the Task Pane but the subject is not changing at all, is there any way to go about this code?



hutchinsfairy said:
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.
Click to expand...
 
You must log in or register to reply here.

Similar threads

Hulk009
Concatenate records from a query in 1 line
Replies
13
Views
436
Gasman
Gasman
D
Table Search and Total
Replies
18
Views
393
DDJKSA
D
A
Mail merge query with multiple records
Replies
10
Views
335
ebs17
E
H
Mail merging words with data from excel
Replies
5
Views
327
Pat Hartman
P
W
How to get Access to open Outlook in HTML instead of Plain Text
Replies
6
Views
291
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom