HR Document Automation with Excel, Word, Images, and PDF

Sub GenerateHRDocs()
    Dim wb As Workbook, ws As Worksheet
    Dim wdApp As Object, wdDoc As Object
    Dim templatePath As String, imgFolder As String
    Dim outWordFolder As String, outPdfFolder As String
    Dim logPath As String, logFile As Integer
    Dim lastRow As Long, i As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Roster")

    templatePath = "C:\HR\Templates\OfferLetter.docx"
    imgFolder = "C:\HR\Photos\"
    outWordFolder = "C:\HR\Output\WORD\"
    outPdfFolder = "C:\HR\Output\PDF\"
    logPath = outPdfFolder & "GenerationLog.txt"

    ' Ensure output folders exist
    If Dir(outWordFolder, vbDirectory) = "" Then MkDir outWordFolder
    If Dir(outPdfFolder, vbDirectory) = "" Then MkDir outPdfFolder

    ' Open log file
    logFile = FreeFile
    Open logPath For Output As #logFile
    Print #logFile, "HR Document Generation Log - " & Now

    On Error GoTo ErrHandler
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        Dim empName As String, startDate As String, empID As String, position As String
        empName = ws.Cells(i, "A").Value
        startDate = ws.Cells(i, "B").Value
        empID = ws.Cells(i, "C").Value
        position = ws.Cells(i, "D").Value

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

        With wdDoc.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = "<<EmployeeName>>"
            .Replacement.Text = empName
            .Execute Replace:=2
            .Text = "<<StartDate>>"
            .Replacement.Text = startDate
            .Execute Replace:=2
            .Text = "<<Position>>"
            .Replacement.Text = position
            .Execute Replace:=2
        End With

        ' Insert logo (static path)
        InsertImage wdDoc, "<<photo_logo>>", "C:\HR\Images\logo.png"

        ' Insert signature if it exists
        Dim sigPath As String
        sigPath = imgFolder & empID & "_sig.png"
        If Dir(sigPath) <> "" Then
            InsertImage wdDoc, "<<photo_signature>>", sigPath
        Else
            Print #logFile, "Missing signature for ID " & empID & " (row " & i & ")"
        End If

        Dim outWord As String, outPdf As String
        outWord = outWordFolder & empID & "_" & empName & ".docx"
        outPdf = outPdfFolder & empID & "_" & empName & ".pdf"

        wdDoc.SaveAs2 outWord
        wdDoc.ExportAsFixedFormat OutputFileName:=outPdf, ExportFormat:=17 'wdExportFormatPDF
        wdDoc.Close False
        Print #logFile, "Successfully processed ID " & empID & " (row " & i & ")"
    Next i

    wdApp.Quit
    Close #logFile
    MsgBox "HR documents generated: " & (lastRow - 1) & " records. See log at " & logPath, vbInformation
    Exit Sub

ErrHandler:
    Print #logFile, "Error on row " & i & ": " & Err.Description
    Resume Next
End Sub

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