How to Create Employee Document Packs from Excel and Word

Option Explicit

Sub GenerateEmployeePacks()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim tmplPath As String
    Dim outDocPath As String
    Dim outPdfPath As String
    Dim photosFolder As String
    Dim wdApp As Object ' Word.Application
    Dim wdDoc As Object ' Word.Document
    Dim employeeName As String
    Dim photoFile As String

    '--- Configuration ---
    tmplPath = "C:\Templates\EmployeePack.docx"
    outDocPath = "C:\Output\DOCX"
    outPdfPath = "C:\Output\PDF"
    photosFolder = "C:\Photos\Employees"
    '----------------------

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Employees")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False

    For i = 2 To lastRow ' assume header in row 1
        employeeName = ws.Cells(i, "B").Value ' column B = FullName
        photoFile = ws.Cells(i, "F").Value ' column F = PhotoFilename

        Set wdDoc = wdApp.Documents.Open(tmplPath, ReadOnly:=False)

        '--- Replace text placeholders ---
        Call FindReplace(wdDoc, "{{FullName}}", employeeName)
        Call FindReplace(wdDoc, "{{StartDate}}", ws.Cells(i, "C").Value)
        Call FindReplace(wdDoc, "{{Position}}", ws.Cells(i, "D").Value)
        Call FindReplace(wdDoc, "{{Email}}", ws.Cells(i, "E").Value)

        '--- Insert standard logo and signature ---
        Call InsertImage(wdDoc, "{{photo_logo}}", "C:\Images\company_logo.png")
        Call InsertImage(wdDoc, "{{photo_signature}}", "C:\Images\signature.png")

        '--- Insert employee photo ---
        If Len(photoFile) > 0 Then
            Call InsertImage(wdDoc, "{{EmployeePhoto}}", photosFolder & "\" & photoFile)
        End If

        '--- Save DOCX ---
        Dim docFileName As String
        docFileName = outDocPath & "\" & Replace(employeeName, " ", "_") & ".docx"
        wdDoc.SaveAs2 docFileName, 16 ' wdFormatXMLDocument

        '--- Export PDF ---
        Dim pdfFileName As String
        pdfFileName = outPdfPath & "\" & Replace(employeeName, " ", "_") & ".pdf"
        wdDoc.ExportAsFixedFormat OutputFileName:=pdfFileName, ExportFormat:=17 ' wdExportFormatPDF

        wdDoc.Close SaveChanges:=False
    Next i

    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing
    MsgBox "Employee packs generated: " & (lastRow - 1) & " documents.", vbInformation
End Sub

Private Sub FindReplace(ByVal doc As Object, ByVal findText As String, ByVal replaceText As String)
    With doc.Content.Find
        .Text = findText
        .Replacement.Text = replaceText
        .Wrap = 1 ' wdFindContinue
        .Execute Replace:=2 ' wdReplaceAll
    End With
End Sub

Private Sub InsertImage(ByVal doc As Object, ByVal placeholder As String, ByVal imgPath As String)
    Dim rng As Object
    Set rng = doc.Content
    With rng.Find
        .Text = placeholder
        .Wrap = 1
        If .Execute Then
            doc.InlineShapes.AddPicture FileName:=imgPath, LinkToFile:=False, SaveWithDocument:=True, Range:=rng
        End If
    End With
End Sub