none
Word 2007 MailMergeBeforeMerge Cancel not working RRS feed

  • Question

  • I have an application that I need to Cancel the MailMerge under certain circumstances.  I have fully tested my routine apart from when I set:
           Cancel=True
    it does not stop the MailMerge from running.  What am I missing?

    The whole application can be downloaded from:
           https://www.dropbox.com/sh/xu2o0sykg9296qc/AABA8sZHVTLwMyOtda7zkXg5a?dl=0
    Just download the 3 files into the same folder and open the Word Document.

    The code for my MailMergeBeforeMerge Subroutine follows:

    Private Sub WORD_Application_MailMergeBeforeMerge( _
                ByVal Doc As Document, _
                ByVal StartRecord As Long, _
                ByVal EndRecord As Long, _
                Cancel As Boolean)
    '
    '   Written by: Brian McGuigan
    '           of: On2it Software Ltd
    '      Version: 1
    '        Dated: 18-May-15
    'First Used in: NEXT Version
    '       Status: Tested - apart from setting
    '                   Cancel=True
    '               is not preventing Mail-Merge from running
    '
    '      Purpose: This Subroutine:
    '                   - initialises the Macros, if not already done,
    '                   - Use the Subject from the MailEnvelope or the MailMerge Dialog Box, if the other is empty.
    '                     If they are different it will use the Subject from the Dialog Box - having advised the User first.
    '                     This is necessary because WORD doesn't do it for you.
    '      - The User may Cancel the Mail-Merge:
    '   - if the Subject is wrong.
    '      - The Mail-Merge will be cancelled if Oulook's Outbox is not empty.

        Dim b_AttachmentsInActiveDocument As Boolean
       
        Dim i_Field As Integer
        Dim i_PositionOfLastBackslash As Integer
       
        Dim s_MailMergeAttachmentsDb As String
       
        Dim Response As Integer
           
       
        On Error GoTo ErrorHandler
       
        If ActiveDocument.MailMerge.Destination = wdSendToEmail Then
       
            m_b_MergeToEmail = True
           
            'If Sub 'Document_Open' did not run because Macros were not Enabled
            If m_b_WORDapplication_AlreadyOpen = False Then
                Call Document_Open  'to ensure initialisation has been done
            End If
       
            'Initialise Module Level Variables, so we don't need to call Function 'ColumnNumber' every time
            m_i_ColumnNumberContaining_EmailAddress = ColumnNumber("Email_Address")
                   
            'Save Documents
            '--------------
            'to ensure any changes to
            '   .MailMerge.MailSubject      and
            '   .MailEnvelope.Item.Subject
            'are available.
            On Error Resume Next
            Documents.Save NoPrompt:=False  'This will Prompt use to Save the ActiveDocument
            On Error GoTo 0
           
            'Copy Subject from MailEnvelope into MailMerge Dialog Box
            '--------------------------------------------------------
            'GEM puts the Subject into the MailEnvelope, but WORD does not automatically copy it into the MailMerge Dialog Box.
            'So we have to do it.
            With ActiveDocument
                'If Subjects are Different
                If (.MailMerge.MailSubject <> .MailEnvelope.Item.Subject) Then
               
                    'If there is no Subject in 'Mail-Merge Wizard'
                    If Len(.MailMerge.MailSubject) = 0 Then
                        'Use Subject in the 'E-Mail Header'
                        .MailMerge.MailSubject = .MailEnvelope.Item.Subject
                    Else
                        'If there is no Subject in 'E-Mail Header'
                        If Len(.MailEnvelope.Item.Subject) = 0 Then
                            'Use Subject from 'Mail-Merge Wizard'
                            .MailEnvelope.Item.Subject = .MailMerge.MailSubject
                        Else
                            'If they are BOTH present and different
                            Response = MsgBox( _
                                Title:= _
                                    "WARNING - Subjects Differ", _
                                Prompt:= _
                                    "The Subject in the 'E-Mail Header', on-screen, is: " & vbCrLf & _
                                    "     " & .MailEnvelope.Item.Subject & vbCrLf & _
                                    "The Subject just entered in the 'Mail-Merge Wizard' was: " & vbCrLf & _
                                    "     " & .MailMerge.MailSubject & vbCrLf & _
                                    "If you proceed the Subject in the 'Mail-Merge Wizard' will be used in your E-mails." & vbCrLf & _
                                     vbCrLf & _
                                    "If that's NOT correct, you should answer NO and correct the Subject in the 'E-Mail Header', " & _
                                    "before proceeding.  " & vbCrLf & _
                                    vbCrLf & _
                                    "Do you wish to proceed and use: " & vbCrLf & _
                                    "     " & .MailMerge.MailSubject & vbCrLf & _
                                    "as your Subject?  ", _
                                Buttons:= _
                                    vbInformation + vbYesNo)
                                   
                            If Response = vbYes Then
                                'Use Subject in the 'Mail-Merge Wizard'
                                .MailEnvelope.Item.Subject = .MailMerge.MailSubject
                            Else
                                MsgBox ("Please correct your Subject and try again.  ")
    'UNTESTED CODE is BELOW here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                Cancel = True       'Does NOT not STOP Mail-Merge from Proceeding
    'UNTESTED CODE is ABOVE here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                Exit Sub
                            End If
                           
                        End If
                    End If
                Else
                    'They were the same, so we didn't need to do anything
                End If
               
            End With 'ActiveDocument
             
            'Remember the m_s_OriginalSubject
            m_s_OriginalSubject = ActiveDocument.MailMerge.MailSubject
       
            'If there are any Attachments In ActiveDocument
            '==============================================
            m_i_NumberOf_AttachmentsInMailEnvelope = ActiveDocument.MailEnvelope.Item.Attachments.Count
            s_MailMergeAttachmentsDb = m_s_FolderContainingActiveDocument & "\" & "Mail-Merge Attachments.Mdb"
           
            'If there are any Attachments in the ACtiveDocument
            If (m_i_NumberOf_AttachmentsInMailEnvelope > 0) Or _
               (NumberOfRecordsIn(s_MailMergeAttachmentsDb, "Attachments") > 0) _
            Then
                'If OUTLOOK_Application not already initialised,
                If OUTLOOK_Application Is Nothing Then
                    'do it now using LATE Binding so it works with ANY version of Outlook
                    Set OUTLOOK_Application = CreateObject("Outlook.Application")
                End If
                   
                'Create OUTLOOK_Namespace so that we can SwitchOutlook "Offline"
                Set OUTLOOK_Namespace = OUTLOOK_Application.GetNamespace("MAPI")
                SwitchOutlook "Offline"  'So E-mails will accumulate in Outlook Outbox, without being sent
           
                'Give access to OUTLOOK_Outbox so we can check whether it's empty
                Set OUTLOOK_Outbox = OUTLOOK_Namespace.GetDefaultFolder(olFolderOutbox)
               
                m_b_AttachmentsInActiveDocument = True
               
                'Check that OUTLOOK_Outbox is Empty
                If OUTLOOK_Outbox.Items.Count > 0 Then
                    s_Prompt = _
                        "Your OUTLOOK Outbox still has items in it.  " & _
                        "So a 'Merge to E-mail, with Attachments' cannot be performed at this Time.  " & vbCrLf & _
                        "" & vbCrLf
                    s_Prompt = s_Prompt & _
                        "This is because a 'Merge to E-mail, with Attachments' works by:  " & vbCrLf & _
                        "    - switching OUTLOOK offline, " & vbCrLf & _
                        "      so that E-mails generated will not be sent," & vbCrLf & _
                        "    - generating E-mails with a normal Merge to E-mail.  " & vbCrLf & _
                        "      This causes them to be created, without Attachments, " & vbCrLf & _
                        "      and sent to your OUTLOOK Outbox, where they will wait " & vbCrLf & _
                        "      until OUTLOOK is switched Online again." & vbCrLf & _
                        "    - " & PossessiveForm(g_CompanyName) & " 'Merge to E-mail' Macros,  " & vbCrLf & _
                        "      then go through your OUTLOOK Outbox adding " & vbCrLf & _
                        "      the Attachments, before" & vbCrLf & _
                        "    - switching OUTLOOK Online, " & vbCrLf & _
                        "      to allow the E-mails to be sent.  " & vbCrLf & _
                        vbCrLf
                    s_Prompt = s_Prompt & _
                        "This means you Outbox has to be clear to start with.  " & _
                        "Otherwise any Attachments being sent to 'ALL Recipients' " & _
                        "would be added to the E-mails already in your Outbox.  " & vbCrLf & _
                        "" & vbCrLf & _
                        "Please clear your OUTLOOK Mailbox before trying again.  "

                    MsgBox _
                        Title:= _
                            "ERROR - Can't Run 'Merge to E-mail, with Attachments' ", _
                        Prompt:= _
                            s_Prompt, _
                        Buttons:= _
                            vbCritical + vbOKOnly
                   
    'UNTESTED CODE is BELOW here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    Cancel = True       'Does NOT not STOP Mail-Merge from Proceeding
    'UNTESTED CODE is ABOVE here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

                End If

            Else
                m_b_AttachmentsInActiveDocument = False
                'So we can allow MailMerge to go through as normal without doing anything extra
            End If
               
        Else
            'A Merge to E-mail is not being done
            '-----------------------------------
            'So we can allow MailMerge to go through as normal without doing anything
           
        End If  'END of - If ActiveDocument.MailMerge.Destination = wdSendToEmail

    NormalExit:
        Exit Sub
       
    ErrorHandler:
        'This code is executed whenever an Error is detected during execution of this routine

        'Log Error using standard routine
        WriteErrLog "[GEM - WORD Template Macros].WORD_Application_MailMergeBeforeMerge", Err.Number, Err.Description
       
        GoTo NormalExit

        Resume          'This Statement is left here fo use during De-Bugging only.
                        'It is impossible to reach during normal execution.
                        'Just Drag the Execution Point to here and Press F8.
                        'Execution will resume at the Next instruction following the Error.
           
    End Sub

    Monday, June 15, 2015 11:29 PM

Answers

  • Hi Brian

    Mmmm. I can't test your code or offer direct help on this, but a couple of remarks:

    1. You shouldn't be using ActiveDocument, if I'm following correctly. You should be using the Doc parameter provided by the BeforeMerge event.

    2. The BeforeMerge event is notoriously UNRELIABLE. Sometimes it works, sometimes it doesn't and as far as I know, no one has ever been able to figure out the "why". This statement is based on (painful) personal experience.

    You might want to refer to this article if you haven't seen it already:
    http://www.word.mvps.org/FAQs/Mailmerge/mergewithattachments.htm

    Doug is another "victim" of trying to use the BeforeMerge event and experiencing extreme frustration before giving up on it...


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, June 16, 2015 6:53 PM
    Moderator

All replies

  • Hi Brian,

    What did you mean MailMergeBeforeMerge Cancel not working? I am creating the new blank Word document for mail merge and add the MailMergeBeforeMerge and MailMergeAfterMerge event.

    The MailMergeAfterMerge always fired however it wont send email when I set the cancel to true in the MailMergeBeforeMerge event.

    Can you reproduce this issue in a new document? Here is the code for your reference:

    Sub init()
    Set app = Application
    End Sub
    
    
    Private Sub app_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
    Debug.Print "after mail merge event fired."
    End Sub
    
    Private Sub app_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)
    'Cancel = True
    End Sub
    Regards & Fei


    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.

    Tuesday, June 16, 2015 6:31 AM
    Moderator
  • Hi Brian

    Mmmm. I can't test your code or offer direct help on this, but a couple of remarks:

    1. You shouldn't be using ActiveDocument, if I'm following correctly. You should be using the Doc parameter provided by the BeforeMerge event.

    2. The BeforeMerge event is notoriously UNRELIABLE. Sometimes it works, sometimes it doesn't and as far as I know, no one has ever been able to figure out the "why". This statement is based on (painful) personal experience.

    You might want to refer to this article if you haven't seen it already:
    http://www.word.mvps.org/FAQs/Mailmerge/mergewithattachments.htm

    Doug is another "victim" of trying to use the BeforeMerge event and experiencing extreme frustration before giving up on it...


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, June 16, 2015 6:53 PM
    Moderator
  • Hi Cindy,

    Thanks for your help, yet again! Option 1 worked like a dream. Changing:
            If ActiveDocument.MailMerge.Destination = wdSendToEmail Then
    to:
            If Doc.MailMerge.Destination = wdSendToEmail Then
    sorted it.

    I've not experienced any problem with MailMergeBeforeMerge being called - just that 'Cancel' did not work.  But now it does, so thanks.

    Yes, I have indeed, seen that article by Doug Robbins. I have also had a full implementation, based on his approach, actually working. I also discovered a major weakness in that approach.

    You can only send plain text E-mails with Attachments. I want to be able to send fully formatted E-mails with Attachments as a result of a Merge to E-mail. So what I'm doing is:
          - switch Outlook OFF-Line, so that E-mails will accumulate in the Outbox;
          - perform a normal 'Merge to E-mail',
            so that fully formatted E-mails without Attachments get sent to the Outbox;
          - go through the E-mails in the OUtbox to ADD their Attachments;
          - switch Outlook ON-Line, so that the E-mails will be sent.
    At present I'm still working on the 'ADD the Attachments' bit.

    When my Document opens the User is taken to the 'Mailings' Tab now, with the E-mail Header already open and any standard Attachments setup in our application already attached - though the User will hopefully be able to add their own. These Attachments will be sent to 'ALL Recipients'.

    I've added two Buttons to the 'Mailings' Tab. The first simply explains how to 'Use Merge Fields in E-mail Subject'. All you do is type the 'Field Name' in the 'Subject' surrounded by <<......>>.

    The 'ADD Attachments to Mail-Merge' Button is only needed to send some Attachments to specific Recipients only. This takes you to a small Access database that reads the MailMerge Data Source and uses the Open Dialog to select the Attachments.

    I have programmed an 'ALL Recipients' Option into that Database, just in case we can't use the Atttachments specified in the E-mail header. As that is the subject of another Forum Question, which has been escalated to 'Senior Microsoft Developers', as there is a problem associated with finding the Pathnames of those attachments after they've been added into the MailEnvelope - but at least I have a fall-back option.

    Wish me luck
    Brian

    Wednesday, June 17, 2015 1:09 AM
  • Hi Brian

    I wish you much luck :-) However...

    <<I've not experienced any problem with MailMergeBeforeMerge being called >>

    I didn't either, on my machine. But it didn't work on some 50% of the other machines it was run on! And then, at a later point, it also stopped working correctly on my machine <sigh>


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, June 17, 2015 7:17 PM
    Moderator
  • Hi Cindy.

    Hmmmm.....  I don't like the sound of THAT.  My whole approach relies on MailMergeBeforeMerge, as I have to intercept the User trying to do a Merge and doing something different. 

    I suppose I could hide the usual 'Finish & Merge' Button on the 'Mailings' Tab with my own similar looking 'Merge to E-mail' Button.  It's routine would then decide whether to do a 'Normal' Merge to E-mail, or a 'Merge to E-mail, with Attachments.  At present the MailMergeBeforeMerge sets a Boolean variable that is used by the MailMergeAfterMerge to decide whether to do additional processing following a 'Normal' Merge to E-mail, or not.  So I am also relying on the MailMergeAfterMerge being called.  Is THAT any more reliable? 

    Perhaps  I should keep all processing in my own routine behind the 'Merge to E-mail' Button?  If I do can I call a 'Normal' Merge to E-mail from there? 

    I know these sound a bit like rhetorical questions, but your thoughts and guidance would be appreciated.  I'm reasonably au-fait with calling Word and Outlook from Access, but I'm on a fairly steep learning curve when it comes to developing Word Macros.

    Thanks Again
    Brian

    P.S.  I'm not too sure the 'Cancel' is working either.  I use it twice it MailMergeBeforeMerge, I tested one and it worked.  But it looks like the other isn't.  I better do some more testing, to be sure. :-(

    Thursday, June 18, 2015 3:57 AM
  • Hi Brian

    <<MailMergeAfterMerge>>

    I've never heard that this event is NOT reliable - as far as I know, it triggers and runs as expected.

    <<Perhaps  I should keep all processing in my own routine behind the 'Merge to E-mail' Button?  If I do can I call a 'Normal' Merge to E-mail from there? >>

    I'm not quite sure what you mean by this question, but...

    I'd imagine there'd be some way to handle the "Boolean variable" on the already-merged document? If necessary, by looping the sections?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 18, 2015 5:29 PM
    Moderator