Can't get MailMerge to work through VBA

[DetroitJimBurke]

I'm trying to populate a Word document with info from a spreadsheet. I created Merge fields in the Word doc, e.g. <<Patient_Name>>. The excel book I'm testing with has two tabs, one called Sheet1 that has a row with the field names and a row with the values, the other sheet just has a button I will be having the user click to run the process. I found some code from your site where they're basically doing the same thing I'm trying to do. They posted their code and said it was working for them, so I copied and pasted their code and modified it to fit what I'm doing. It works up until it gets to this line of code:

  wordMailMerge.OpenDataSource Name:=excelPath, SQLStatement:="SELECT * FROM `'Sheet1$'`"

It locks up for about 2 minutes and then starts giving me a message 'Microsoft Excel is waiting for another application to complete an OLE action' every 10 seconds or so.

Here is the complete subroutine:

Sub RunMailMerge()
   On Error GoTo HandleError

    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim wordMailMerge As Word.MailMerge
    Dim wordMergeFields As Word.MailMergeFields
    Dim wordPath As String
    Dim excelPath As String
    Dim LtrFinalRow As Long
       
    CurrentWorksheet = ActiveSheet.Name
    excelPath = ThisWorkbook.Path & "\Create Word Doc.xlsm"
    ' excelPath = "C:\Users\jimib_000\Documents\word template data.xlsx"

        ' Worksheets("Ltr Data").Visible = True
        Sheets("Sheet1").Select
        LtrFinalRow = Cells(Rows.Count, 1).End(xlUp).Row
        
            If LtrFinalRow > 1 Then
                'Clear any extra, blank rows.
                Range("B" & LtrFinalRow + 1).Select
                Range(Selection, Selection.End(xlToRight)).Select
                Range(Selection, Selection.End(xlDown)).Select
                Selection.Delete Shift:=xlUp
                
                wordPath = ThisWorkbook.Path & "\test template.docx"
                Set wordApp = CreateObject("Word.Application")
                Set wordDoc = wordApp.Documents.Open(wordPath)
                Set wordMailMerge = wordDoc.MailMerge
                
                wordMailMerge.OpenDataSource Name:=excelPath, SQLStatement:="SELECT * FROM `'Sheet1$'`"
                wordMailMerge.Execute
                wordDoc.Close
                wordApp.Quit
            End If
        ' Worksheets("Ltr Data").Visible = False
        
    Set wordMailMergeFields = Nothing
    Set wordMailMerge = Nothing
    Set wordDoc = Nothing
    Set wordApp = Nothing
    Sheets(CurrentWorksheet).Select
    Exit Sub
    
HandleError:
   MsgBox "An error has occurred."
   wordDoc.Close
   wordApp.Quit
   Set wordDoc = Nothing
   Set wordApp = Nothing
 
End Sub

Here's the data from the Sheet1 tab:

Patient_Name	First_Name	His/HerCaps	His/Her	He/SheCaps	He/She
Jim Burke	Jim	His	his	He	he

I'm not sure how to attach copies of the actual excel file and word doc, or if you need those. Here's the content of the Wotd doc, it's a shorter version of what I'll end up using. You can see the Merge fields, defiend with the <<>> delimiters.

Confidential Assessment Summary​

Name: «Patient_Name»	Examiners:
Age:
Date of Birth	Dates of Evaluation:
	Date of Feedback

Reason for Referral:

«Patient_Name», a 6-year-old male, was referred by «His/Her» to determine the sources of current difficulties.



The current evaluation consisted of a diagnostic interview, direct testing, and observations of My_First_Name, and developmental and behavioral rating scales completed by My_His_Her_Small My_Guardian.



Background information was also obtained through an interview .



This is some text that I want to insert into a Word document. I'm going to use the function that word has of inserting from a file by inserting and choosing this file. Let's see what happens.

This is some text that I want to insert into using the AutoText function. I’m going to store this in auto text and then insert it and we’ll see what happens.



Johnathon Lebo, a 6-year-old male, was referred by his/her pediatrician at Kids First to determine the sources of current difficulties.



There are concerns with both sleep onset and sleep maintenance and since «He/She» was around 3-4 years of age. He is very overactive and has difficulty settling down at night. He often does not fall asleep until around midnight, then wakes up early in the morning at about 5-6 am. As a result, he is often fatigued during the day. His teachers have expressed concerns about his trying to sleep during class at times