none
Email mail merge from Word RRS feed

  • Question

  • Hi all,

    We use Word mail merge to sent out contract renewals to our customers. In this document is some code that retrieves information directly from SQL to fill out a table in the document.

    The code is excuted in the MailMergeAfterMerge event. The code gets executed and the table filled when we merge to a document or a print. 

    However if we merge to an email and sent the document as HTML or attachment, the code runs, the table is filled on the screen BUT the table in the emailed document is emty.

    Does anybody have any ideas?

    Thanks, Jan

    Thursday, November 21, 2013 1:44 PM

Answers

  • Hi,

    >>However if we merge to an email and sent the document as HTML or attachment, the code runs, the table is filled on the screen BUT the table in the emailed document is emty.<<

    Do you mean you merge to email as followed?

    If so, I tried to reproduce your issue with the VBA macro code below. In my MailMergeApp_MailMergeAfterMerge event, I set the bold property of the document as true. After mail merge, the context original document is Bold, but the sent mail is not.

    Private WithEvents MailMergeApp As Application
    Sub RunIt()
        Set MailMergeApp = Application
    End Sub
    Private Sub MailMergeApp_MailMergeAfterMerge(ByVal Doc As Document, _
    ByVal DocResult As Document)
    Doc.Range.Bold = True
    ‘Doc.Save
    End Sub

    In fact, MailMergeAfterMerge Event occurs after all records in a mail merge have merged successfully.  So before running to the MailMergeAfterMerge event, the merging progress have been successful. We could only edit the current document but not the content of the mail. And even I add Doc.Save, the result is same.

    So to achieve your goal, I don’t suggest you adding the filling in table progress in the MailMergeAfterMerge event.

    If I have any misunderstanding, please feel free to let me know. In addition, would you mind sharing the sample code in the MailMergeAfterMerge event to help us to reproduce your issue?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Sunday, November 24, 2013 12:42 AM
    Moderator

All replies

  • Hi,

    >>However if we merge to an email and sent the document as HTML or attachment, the code runs, the table is filled on the screen BUT the table in the emailed document is emty.<<

    Do you mean you merge to email as followed?

    If so, I tried to reproduce your issue with the VBA macro code below. In my MailMergeApp_MailMergeAfterMerge event, I set the bold property of the document as true. After mail merge, the context original document is Bold, but the sent mail is not.

    Private WithEvents MailMergeApp As Application
    Sub RunIt()
        Set MailMergeApp = Application
    End Sub
    Private Sub MailMergeApp_MailMergeAfterMerge(ByVal Doc As Document, _
    ByVal DocResult As Document)
    Doc.Range.Bold = True
    ‘Doc.Save
    End Sub

    In fact, MailMergeAfterMerge Event occurs after all records in a mail merge have merged successfully.  So before running to the MailMergeAfterMerge event, the merging progress have been successful. We could only edit the current document but not the content of the mail. And even I add Doc.Save, the result is same.

    So to achieve your goal, I don’t suggest you adding the filling in table progress in the MailMergeAfterMerge event.

    If I have any misunderstanding, please feel free to let me know. In addition, would you mind sharing the sample code in the MailMergeAfterMerge event to help us to reproduce your issue?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Sunday, November 24, 2013 12:42 AM
    Moderator
  • Hi,

    Thanks for taking the time to look into this.

    Here is the code I use.

    One of the merge fields is a Contactname GUID, I use this to retrieve some more information from a SQL database and put this information into a table in the document.

    The loop makes sure we do this for all contacts in the merge. 

    Dim x As New Class1
    Sub Register_Event_Handler()
     Set x.App = Word.Application
    End Sub
    
    Public WithEvents App As Word.Application
    Dim cnn1 As Connection
    Dim RecordCounter As Integer
    
    Private Sub App_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
    Dim i As Integer, x As Integer
    Dim ContactGUID As String
    Dim rsTable As Recordset
        
    Set cnn1 = New ADODB.Connection ' open SQL server connectie
        cnn1.ConnectionString = "driver={SQL Server};" & _
            "server=xxx;;;database=yyyy"
        cnn1.Open
        i = 0
        MsgBox "Recordcounter : " & RecordCounter, vbOKOnly
        Doc.MailMerge.DataSource.ActiveRecord = wdFirstRecord
        
        For i = 1 To RecordCounter
            ContactGUID = Mid(Doc.MailMerge.DataSource.DataFields(1).Value, 2, Len(Doc.MailMerge.DataSource.DataFields(1).Value) - 2)
            Set rsTable = New ADODB.Recordset
            rsTable.CursorType = adOpenKeyset
            rsTable.LockType = adLockOptimistic
            criteria = "SELECT A ,B, C, D, E, F " & _
                        "FROM [aaa_MSCRM].[dbo].[New_Machine] " & _
                        "WHERE New_admincontactid= '" & ContactGUID & "' " & _ 
                        "ORDER BY A"
                        
                        
            rsTable.Open criteria, cnn1
            rsTable.MoveFirst
            If Not rsTable.EOF And Not rsTable.BOF Then
                Do While Not rsTable.EOF 'Or Not rsTable.BOF
                    With ActiveDocument.Tables(i).Rows.Last
                        If IsNull(rsTable.Fields(0).Value) Then
                            aa = ""
                        Else
                            aa = rsTable.Fields(0).Value
                        End If
                        .Cells(1).Range.Text = aa
                        If IsNull(rsTable.Fields(5).Value) Then
                            ee = ""
                        Else
                            ee = rsTable.Fields(5).Value
                        End If
                        .Cells(6).Range.Text = ee
                    End With
                    rsTable.MoveNext
                    If Not rsTable.EOF Then ActiveDocument.Tables(i).Rows.Add
               Loop
            Else
                MsgBox "No records ", vbOKOnly
            End If
            If i <> RecordCounter Then Doc.MailMerge.DataSource.ActiveRecord = wdNextRecord
            rsTable.Close
            
        Next i
        MsgBox "Merge finished.", vbOKOnly
        cnn1.Close
    End Sub
    
    
    Private Sub App_MailMergeAfterRecordMerge(ByVal Doc As Document)
        RecordCounter = RecordCounter + 1
    End Sub
    
    
    Private Sub App_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)
        RecordCounter = 0
    End Sub

    As you saw in your example as well the email gets sent prior to the completion of the MailMergeAfterMerge Event.

    I look forward to any suggestions on how to proceed. 

    Tuesday, November 26, 2013 12:46 PM
  • Hi,

    As I said, the MailMergeAfterMerge Event is designed to occur after all records in a mail merge have merged successfully.

    As workarounds, I think you could just try the 2 points below.

    1. You could edit individual documents as below instead of sending email message directly, and then send the mails manually. When you click the edit individual documents, it will also tigger the MailMergeAfterMerge event.

    2. You could write a macro to do the operation in your App_MailMergeAfterMerge function before mail merging.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, November 28, 2013 8:02 AM
    Moderator
  • Hi,

    Regarding your suggestions:

    1. Edit individual documents and sent manually is NOT an option there are too many records to sent manually.
    2. I need the Contact detail to perform the lookup in my code. So I can only run my code after the merge has occurred.

    This is a bug in Word since clearly the emails get sent out regardless of which MailMerge event has been completed!

    Tuesday, December 3, 2013 3:11 PM