Mail merge into a Word document generates Error Code 4248

Question

My goal is to use two dropdown menus (DM) within a workbook to open a filled-out document.
The DM 1 is to select which row of data will be merged.
The DM 2 is to select which template is being used.
I have separate code that highlights the selected row and opens the document.

Set doc = appWD.ActiveDocument gives me

error 4248 This command is not available because no document is open.

The template is open when I receive this error.

For Context:
Open_LPA_Template, run by itself, does open the Word document selected from the DM 2.
Select_Parcel, run by itself, does highlight the row of data selected from the DM 1.

Sub Run_Mail_Merge_LPA()
    Dim doc As Word.Document
    Dim appWD As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim tbl As ListObject
    Dim row As ListRow
    Dim searchValue As String
    Dim searchRange As Range
    Dim foundCell As Range
    
    ' Get references to the workbook and worksheets
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)
    Set ws2 = wb.Worksheets(2)
    
    ' Create an instance of the Word application
    Set appWD = CreateObject("Word.Application")
    
    ' Open the Word document that has been selected in DM 2

    Open_LPA_Template
    
    ' Select_Parcel's CODE: Select the Row of Data from DM 1 for the Mail Merge

    ws2.Select
    ' Select cell D3 in worksheet 2
    ws2.Range("D3").Select
    
    ' Store the value in D3 of worksheet 2 in a variable
    searchValue = ws2.Range("D3").Value
    
    ' Set the search range to the entire column A of worksheet 1
    ws.Select
    Set searchRange = ws.Range("A:A")
    
    ' Use the Find method to search for the search value in the search range
    Set foundCell = searchRange.Find(searchValue)
    
    If Not foundCell Is Nothing Then
        ' If a match is found, select the cell
        foundCell.Select
        ActiveCell.EntireRow.Select
    Else
        ' If no match is found, print a message
        Debug.Print "Value not found in column A"
    End If

    ' MAIL MERGE CODE: Set the active document to the Word document that was opened
    Set doc = appWD.ActiveDocument
    
    ' Perform the mail merge
    doc.MailMerge.MainDocumentType = wdFormLetters
    doc.MailMerge.OpenDataSource _
        Name:=row.Range, _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:=""
    doc.MailMerge.Execute
End Sub

Sub Open_LPA_Template()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim MainPath As String
    Dim MainPath2 As String
    Dim MainPath3 As String
    Dim MainPath4 As String
    Dim MainPath5 As String
    Dim MainPath6 As String
    Dim Parcel As String
    Dim fileName As String
    Dim FullPath As String
    Dim mWord As Object
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)
    Set ws2 = wb.Worksheets(2)
    Set appWD = CreateObject("Word.Application")
    appWD.Visible = True
    
    MainPath = "C:\Users\ME\Dropbox (ORC)\Desktop\Templates\LPA\"
    MainPath2 = "C:\Users\USER1\Dropbox (ORC)\Desktop\Templates\LPA\"
    MainPath3 = "C:\Users\USER2\Dropbox (ORC)\Desktop\Templates\LPA\"
    MainPath4 = "C:\Users\USER3\Dropbox (ORC)\Desktop\Templates\LPA\"
    MainPath5 = "C:\Users\USER4\Dropbox (ORC)\Desktop\Templates\LPA\"
    MainPath6 = "C:\Users\USER5\Dropbox (ORC)\Desktop\Templates\LPA\"
        
    fileName = ws2.Range("E3")
    
     ' Check if the file exists at the first path
    If Dir(MainPath & fileName & ".docx") <> "" Then
        FullPath = MainPath & fileName & ".docx"
    ElseIf Dir(MainPath2 & fileName & ".docx") <> "" Then
        ' If the file does not exist at the first path, check the second path
        FullPath = MainPath2 & fileName & ".docx"
    ElseIf Dir(MainPath3 & fileName & ".docx") <> "" Then
        ' If the file does not exist at either of the first two paths, check the third path
        FullPath = MainPath3 & fileName & ".docx"
    ElseIf Dir(MainPath4 & fileName & ".docx") <> "" Then
        ' If the file does not exist at any of the first three paths, check the fourth path
        FullPath = MainPath4 & fileName & ".docx"
    ElseIf Dir(MainPath5 & fileName & ".docx") <> "" Then
        ' If the file does not exist at any of the first four paths, check the fifth path
        FullPath = MainPath5 & fileName & ".docx"
    Else
        ' If the file does not exist at any of the first five paths, use the sixth path
        FullPath = MainPath6 & fileName & ".docx"
    End If
    
    appWD.Documents.Open (FullPath)

There are six paths because it could be accessed/used by six people who get to the shared Word documents through their own computers.

You say _Open_LPA_Template, when ran by itself, does correctly open the word document_. That suggests you've opened it in a seperate instance of Word. Add that code and we'll show you how to connect the Subs — chris neilsen, Dec 27 '22 at 21:11
You need to deal with a single Word `Application` instance if you want to use the `ActiveDocument` property in the code anywhere. — Eugene Astafiev, Dec 27 '22 at 21:25
As I suspected you're creating an instance of Word in Open_LPA_Template and opening the doc in that instance. Then creating another in `Run_Mail_Merge_LPA` — chris neilsen, Dec 27 '22 at 21:26
What would be a good way to remove one instance of word being open to allow these to work together? — Aaron Bradow, Dec 27 '22 at 21:38
Change Open_LPA_Template() to Open_LPA_Template(appWD As Object). Remove the createobject from Open_LPA_Template(). Call it in Run_Mail_Merge_LPA() with Open_LPA_Template appWD — user3598756, Dec 27 '22 at 21:42
Either 1) put all the code in one sub 2) pass the word instance to the called Sub as a parameter 3) put the word variable at module scope (only run Create once) — chris neilsen, Dec 27 '22 at 21:43
What would putting the word variable at module scope look like? I appreciate the help. Like I said, novice. This is all from zero knowledge built up over the last few weeks, so the more advice the better. :D — Aaron Bradow, Dec 27 '22 at 22:00
Put `Dim appWD As Object` outside and above the `Sub`s and remove it from all the `Sub`s — chris neilsen, Dec 27 '22 at 22:41
That said, that's the least preferred option. Better to pass a parameter, as in user3598756's comment — chris neilsen, Dec 27 '22 at 22:43
I added the code directly into the sub per your suggestion. That's a simple solution I could implement no issue. However, it's moved the error code down to the Mail Merge section of code: 91 Object variable or with block variable not set. Code has been adjusted above to reflect the change. What would you suggest? — Aaron Bradow, Dec 27 '22 at 22:58
Please don't alter the Q to ask something new. Your original Q has been answered. If you have another Q ask a new one (after you've done your research and are still stuck — chris neilsen, Dec 27 '22 at 23:15
@AaronBradow as to your next error you should really post a new question, as Chris already told you. Meanwhile I could guess the code stops at name:=row.Range, since you have never set row before that codeline. — user3598756, Dec 28 '22 at 08:23

Eugene Astafiev · Answer 1 · 2022-12-27T21:25:08.797

0

The problem is that you create two separate instances of Word Application in the code and try to access a Document instance opened in another Word Application instance/process. If you want to use the ActiveDocument property you need to deal with a single Word Application instance in the code. So, you may pass a created Word Application instance as a parameter to the method to open files.

Be aware, the Documents.Open function from the Word object model opens the specified document and adds it to the Documents collection. It also returns a Document object which can be used instead of the ActiveDocument property in the code.

Sub OpenDoc() 
  Dim doc As Word.Document
  Set doc = Documents.Open FileName:="C:\MyFiles\MyDoc.doc"
End Sub

edited Dec 27 '22 at 21:25

answered Dec 27 '22 at 21:01

Eugene Astafiev

47,483
3
24
45

Thank you for the new direction, however I am not familiar with Documents.Open function yet. How would you work this in instead of the ActiveDocument property? – Aaron Bradow Dec 27 '22 at 21:10
I've update the post with a sample code. – Eugene Astafiev Dec 27 '22 at 21:25

score 0 · Answer 2 · answered Dec 27 '22 at 21:16

0

Since you are creating a new instance of Word via

Set appWD = CreateObject("Word.Application")

That Word instance has no open documents. You need to open the relevant document and address it via code like:

Set doc = appWD.Documents.Open(Filename:="C:\Users\Aaron Bradow\Documents\Mail Merge Document.docx", AddToRecentFiles:=False, ReadOnly:=True)

answered Dec 27 '22 at 21:16

macropod

12,757
2
9
21

I'm not committed to ActiveDocument. It was just what I came across first that sounded right since I had made a separate code that already opened the document. I tried something similar to what you and Eugene recommended, Drocuments.Open, earlier but couldn't figure out how to adapt it to use the subroutine already created. – Aaron Bradow Dec 27 '22 at 22:07
I suggest you look at *Run a Mailmerge from Excel, Sending the Output to Individual Files* in the **Mailmerge Tips & Tricks** thread at https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html. I'd also question why you think you need different mailmerge documents rather than using field coding & custom document properties/document variables to control the output from a single document. That said, all you'd need to add to the code is the logic to change the StrMMDoc variable and to define which record gets processed. – macropod Dec 31 '22 at 05:23

Mail merge into a Word document generates Error Code 4248

2 Answers2