Go Back   Access World Forums > Apps and Windows > Word

 
Reply
 
Thread Tools Rating: Thread Rating: 114 votes, 5.00 average. Display Modes
Old 02-09-2014, 08:53 AM   #31
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?

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.

__________________
"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-09-2014, 12:21 PM   #32
motlaghjs
Newly Registered User
 
Join Date: Feb 2014
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
motlaghjs is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.

Quote:
Originally Posted by RossWindows View Post
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.
motlaghjs is offline   Reply With Quote
Old 02-10-2014, 12:59 AM   #33
MHutcheson
Newly Registered User
 
Join Date: Sep 2013
Location: N/A
Posts: 23
Thanks: 1
Thanked 0 Times in 0 Posts
MHutcheson is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "ExchServerName"
    ' ** SMTP PORT **
    .Fields("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
    ' ** CDO PORT **
    .Fields("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
    ' ** TIMEOUT **
    .Fields("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 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 = somebody@somewhere.com
 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?

MHutcheson is offline   Reply With Quote
Old 07-08-2014, 09:57 PM   #34
Jade4U
Newly Registered User
 
Join Date: Jul 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Jade4U is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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
Quote:
Originally Posted by hutchinsfairy View Post
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.
Jade4U is offline   Reply With Quote
Old 01-26-2015, 09:14 AM   #35
wickahead
Newly Registered User
 
Join Date: Jan 2015
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
wickahead is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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
wickahead is offline   Reply With Quote
Old 04-15-2015, 12:23 PM   #36
Office_junkie
Newly Registered User
 
Join Date: Apr 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Office_junkie is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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.
Office_junkie is offline   Reply With Quote
Old 07-23-2015, 12:37 PM   #37
foxlovefish
Newly Registered User
 
Join Date: Jul 2015
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
foxlovefish is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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


Quote:
Originally Posted by hutchinsfairy View Post
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.

foxlovefish is offline   Reply With Quote
Old 07-23-2015, 12:40 PM   #38
foxlovefish
Newly Registered User
 
Join Date: Jul 2015
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
foxlovefish is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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




Quote:
Originally Posted by RossWindows View Post
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.
foxlovefish is offline   Reply With Quote
Old 07-23-2015, 12:40 PM   #39
foxlovefish
Newly Registered User
 
Join Date: Jul 2015
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
foxlovefish is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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!!!
foxlovefish is offline   Reply With Quote
Old 10-30-2018, 12:43 AM   #40
rkysoo
Newly Registered User
 
Join Date: Jul 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
rkysoo is on a distinguished road
Re: Mail Merge - Dynamic subject line?

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?



Quote:
Originally Posted by hutchinsfairy View Post
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.

rkysoo 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 11:20 AM
Mail Merge from an SQL server database tigernick SQL Server 2 09-04-2006 11:51 PM
[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 10:43 PM.


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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World