How to Generate Membership Forms and Letters in Batches

Sub BatchGenerateMembershipDocs()
    Dim xlApp As Object, wb As Object, ws As Object
    Dim i As Long, lastRow As Long
    Dim tmplPath As String, saveFolder As String
    Dim doc As Document, newDoc As Document
    
    '--- Settings -------------------------------------------------
    tmplPath = ThisDocument.Path & "\MembershipTemplate.docx"   ' path to your Word template
    saveFolder = ThisDocument.Path & "\Output"                ' folder for PDFs
    
    '--- Open Excel data -------------------------------------------
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open(ThisDocument.Path & "\MemberData.xlsx")
    Set ws = wb.Sheets(1)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(-4162).Row   ' xlUp = -4162
    
    '--- Loop through rows -----------------------------------------
    For i = 2 To lastRow   ' assuming row 1 has headers
        Set doc = Documents.Open(tmplPath)
        With doc.Content.Find
            .Text = "{Name}"
            .Replacement.Text = ws.Cells(i, 1).Value
            .Execute Replace:=2
            .Text = "{Address}"
            .Replacement.Text = ws.Cells(i, 2).Value
            .Execute Replace:=2
            .Text = "{MembershipType}"
            .Replacement.Text = ws.Cells(i, 3).Value
            .Execute Replace:=2
        End With
        
        ' Save as PDF using the member name
        doc.ExportAsFixedFormat OutputFileName:=saveFolder & "\" & ws.Cells(i, 1).Value & ".pdf", ExportFormat:=17
        doc.Close SaveChanges:=False
    Next i
    
    '--- Clean up -------------------------------------------------
    wb.Close SaveChanges:=False
    xlApp.Quit
    Set ws = Nothing: Set wb = Nothing: Set xlApp = Nothing
    MsgBox "Batch generation complete! PDFs saved to " & saveFolder, vbInformation
End Sub