none
Word Mail Merge Problems and Windows 10 RRS feed

  • Question

  • I have just migrated to from Win 7  to Win 10 running on a Surface Pro running Office 365 Ver 2016. I run VBA routines using Excel, Word and Outlook. Generally these work OK apart from 2 major problems.

    1. Word Mail merge is painfully slow and takes about 5-10 times longer than previously in Win 7 for the same routines


    2. I get instances of the VBA 'Run Time Err 462 - The remote server does not exist or is unavailable at the statement

    Set wdDoc = AppWd.Documents.Open(DocName) 

    This statement occurs in the re-opening of a mail merge generated document (DocName) which is generated from the Mail merge master document Merge_Doc

                  

    The routines are below.

    I have found suggestions that the  statements

    AppWd.Quit                                                            
    Set AppWd = Nothing

    This does mot solve the problem and any ideas as to the cause of the problem would be greatly appreciated.

    Stuart Watson

    VBA ROUTINES

    Sub CreateDocs(Folder_Name, Doc_Type, Merge_Doc, Data_Source, datasheet, SQLSelect, RunNo)
    
    'Dim AppWd As Object     '09/10/2017
    
    CompPathName
    InitialiseMerge Doc_Type, Merge_Doc, datasheet
     
     AppWd.Visible = True   '09'20/2017
     
     'Open Mailmerge Master Word Doc
     AppWd.Documents.Open FileName:=ComputerPathName & Merge_Doc
     
     LookUpMnth datasheet, MthNa, rownos    'Determine no of rows in this datasheet to ensure that
                                            'there are no blank pages generated
                                            '*** MthNo blank if Ren Comm L is empty
     
     Application.StatusBar = " Now Writing " & Doc_Type & "s for " & MthNa
      
     With AppWd.ActiveDocument.MailMerge
         .MainDocumentType = wdFormLetters
         .Destination = wdSendToNewDocument
         .SuppressBlankLines = True
     'Link to Excel.Workbook as Data Source
         
         .OpenDataSource Name:=(ComputerPathName & Data_Source), _
          SQLStatement:=SQLSelect
           With .DataSource
                .FirstRecord = 1
                .LastRecord = rownos - 1
           End With
    
         .Execute
    End With
    
        Application.StatusBar = "Continuing " & Doc_Type & " Creation for " & MthNa
        
     'Save Mail Merged Generated Letter Documents into a file identified by this Month 'Merged_Letters mmm.doc'
       AppWd.ActiveDocument.SaveAs2 FileName:=ComputerPathNameNewDocs & Folder_Name & "\MASC_" & Folder_Name & "_" & Doc_Type & "s_Ver_" & RunNo & ".docx", FileFormat:= _
            wdFormatDocumentDefault
       Application.StatusBar = "Saving Merged " & Doc_Type & "s for " & MthNa & "_Ver" & RunNo
       
     'Close Mail Merge Master Word Document 'Renewal Letters.docx'
        FinaliseMerge Merge_Doc
       
     'Open Mail Merge Gernerated Word Document
        Application.StatusBar = "Re-Opening Merged " & Doc_Type & "s for " & MthNa & "_Ver" & RunNo
        OpenWordDoc (Folder_Name & "\MASC_" & Folder_Name & "_" & Doc_Type & "s_Ver_" & RunNo & ".docx")
        Application.StatusBar = "Task Completed  for " & MthNa & Doc_Type & "s"
    
    'Unload Me
    End Sub
    
    
    Sub FinaliseMerge(Merge_Doc)
        AppWd.Documents(ComputerPathName & Merge_Doc).Close SaveChanges:=False  'Closes MailMerge Master Doc
        AppWd.Quit                                                              'Closes Most Recent MailMerge Generated Doc
        Set AppWd = Nothing
        
        Application.StatusBar = Doc_Type & " Creation Completed"
    
        'Application.StatusBar = False
        Application.ScreenUpdating = True
    
    End Sub
    
    
    Sub OpenWordDoc(DocName)
    'In order to use this code you must set a reference to the
    'Word object library by doing this. In the VB Editor click
    'Tools, References. Then search for Microsoft Word n.n Object Library
    'where n.n will depend on your version of Word - 2010 = 14.0
    
    Dim AppWd As Object, wdDoc As Object
    
    On Error Resume Next
    Set AppWd = GetObject(, "Word.Application")
    If Err.Number <> 0 Then 'Word isn't already running
    Set AppWd = CreateObject("Word.Application")
    'Set AppWd = New Word.Application
     End If
    On Error GoTo 0
    DocName = ComputerPathNameNewDocs & DocName
    
    'Delay for 10 seconds
    Application.Wait (Now + TimeValue("0:00:05"))
    MsgBox "Excecution resumed after 5 Seconds", VbInformation 
    
    Set wdDoc = AppWd.Documents.Open(DocName)  'Run-time error Err 462 - The remote server machine does not exist or is unavailable'
    AppWd.Visible = True
    
    'You can now do whatever you like with the Word document e.g.
    
    GoToMenu
    
    End Sub
    
    


    Thursday, October 12, 2017 3:11 PM

All replies

  • Clearly this is not all the related code and there are lots of anomalies in the code that you have provided.

    A good start would be to add Option Explicit to the top of the module - then Debug > Compile VBAProject and correct the anomalies that it reports.


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, October 13, 2017 6:35 AM
  • Graham,

    I have exactly the same issue on a very mature Access application automating a suite of Word templates using code similar. The complete code of the module is below.

    This has run perfectly for a number of years on Windows 7 and Access 2010 (and lately Access 2013). Then a couple of the corporate PCs were upgraded to Windows 10 and the merge takes minutes - not seconds. The "Merge_Data" source is in a table on the user's PC and the Word template is pre-configured to use this as a data source.

    Is the issue the loading of the Word Module (and the speed with which this happens)? If so what has happened in Windows 10 to cause this. 

    Warren Belz

    warrenbelz@hotmail.com

    Public Sub WordMerge(VarTemp As String, ExportFile As String, WordVersion As Boolean)
    
        '  Start the mail merge
        Dim objDoc As Word.Document                   'This will be the template
        Dim objWord As New Word.Application           'Define a Word Instance
        Set objDoc = objWord.Documents.Open(VarTemp)  'Define the template
        objWord.Visible = False                       'Visibility not required
    
        '  Define the data source (the query done earlier)
        objDoc.MailMerge.OpenDataSource Name:=CurrentProject.Path & _
            "\Templates\Merge_Data.accdb", SQLStatement:="SELECT * FROM `Merge_Data`"
    
        objDoc.MailMerge.Execute     'Do the Mail Merge
        objDoc.AttachedTemplate.Saved = True
        objDoc.Close False  'close the template - the merged document is now the active one
    
        '  Set the PDF actions if required
        If Not WordVersion Then
            objWord.ActiveDocument.ExportAsFixedFormat OutputFileName:=ExportFile, _
            ExportFormat:=wdExportFormatPDF
            objWord.ActiveDocument.AttachedTemplate.Saved = True
            objWord.Quit False        'Close Word
        Else
            '  Save the merged Word doc (if Word chosen)
            objWord.ActiveDocument.SaveAs ExportFile
            objWord.ActiveDocument.AttachedTemplate.Saved = True
            objWord.Quit False         'Close Word
        End If
    
        '  Clean up values
        Set objWord = Nothing
        Set objDoc = Nothing
    
    End Sub

                                




    • Edited by RWazzaB Sunday, December 10, 2017 3:06 AM
    Sunday, December 10, 2017 3:00 AM
  • I have to say that Access is not my first love, but having mocked up a database to test your code, I would suggest using Late Binding to Word as this will not be affected by the requirement to set references to Word versions that may not be available.It will work with any version.

    I would also suggest you use the running Word application where present as this loads infinitely more quickly than having to start a separate Word application first - however while Office versions after 2010 are noticeably slower, even at its slowest the process below only took five seconds (against virtually instantaneous when Word was already open).

    There was some dubious syntax in your code, but the following should work for you.

    Public Sub WordMerge(VarTemp As String, ExportFile As String, WordVersion As Boolean)
    
    '  Start the mail merge'
    Dim objDoc As Object                  'This will be the template'
    Dim objWord As Object           'Define a Word Object'
    Dim bStarted As Boolean
    
        On Error Resume Next
        Set objWord = GetObject(, "Word.Application")
        If Err.Number <> 0 Then    'Word isn't already running
            Set objWord = CreateObject("Word.Application")
            bStarted = True
        End If
        On Error GoTo 0
    
        Set objDoc = objWord.Documents.Open(VarTemp)  'Define the template'
        objWord.Visible = True                       'Visibility not required but will aid bug testing'
    
        '  Define the data source (the query done earlier)'
        objDoc.MailMerge.OpenDataSource _
                Name:=CurrentProject.Path & _
                      "\Templates\Merge_Data.accdb", _
                      SQLStatement:="SELECT * FROM `Merge_Data`"
    
        objDoc.MailMerge.Execute     'Do the Mail Merge'
        objDoc.AttachedTemplate.Saved = True
        objDoc.Close 0    'close the template - the merged document is now the active one'
    
        '  Set the PDF actions if required'
        If Not WordVersion Then
            objWord.ActiveDocument.ExportAsFixedFormat OutputFileName:=ExportFile, _
                                                       ExportFormat:=17
        Else
            '  Save the merged Word doc (if Word chosen)'
            objWord.ActiveDocument.SaveAs ExportFile
        End If
    objWord.ActiveDocument.AttachedTemplate.Saved = True objWord.ActiveDocument.Close 0 If bStarted Then objWord.Quit 0 'Close Word' ' Clean up values' Set objWord = Nothing Set objDoc = Nothing End Sub




    Graham Mayor - Word MVP
    www.gmayor.com




    Sunday, December 10, 2017 6:12 AM