Batch Offer Letters

How to Generate Offer Letters from Excel and Word Templates

Turn reviewed candidate rows into separate offer-letter drafts with one controlled Word template, exact display fields, guarded filenames, and an HR release gate.

Many candidate rows
One Word template
DOCX and/or PDF
HR review required
This is Factory logic. Generation creates drafts; it does not approve employment terms, authorize a signature, or release an offer.

Quick answer: use one row per candidate and one approved offer template

Create an Offers sheet with one candidate per row. Keep dates and compensation as reviewed display text, validate every READY row before Word starts, and generate each offer from a fresh copy of OfferLetterTemplate.docx.

Keep the workflow narrow

This batch produces standardized offer-letter drafts. Benefits schedules, employment agreements, policy acknowledgments, and onboarding documents belong in separate approved templates and may require a Contractor pack after the offer is approved.

Create the Offers sheet in one exact A–M order

Required column order in row 1

A OfferID; B CandidateName; C JobTitle; D DepartmentName; E WorkLocation; F EmploymentType; G OfferDateDisplay; H StartDateDisplay; I CompensationDisplay; J HiringManagerName; K OutputFormat; L OfferStatus; M OutputName.

Identifiers

Store OfferID as text and keep it unique. Use a separate Windows-safe OutputName without .docx or .pdf.

Candidate and role

Review CandidateName, JobTitle, optional DepartmentName, WorkLocation, EmploymentType, and HiringManagerName.

Display fields

Store OfferDateDisplay, StartDateDisplay, and CompensationDisplay as final text rather than relying on Excel formatting.

Output format

Use exactly DOCX, PDF, or BOTH. The example creates only the requested file types.

Status

Use exactly READY, HOLD, or DONE. Only READY rows enter the generation pass.

No silent cleanup

The macro rejects outer spaces, unexpected control characters, duplicate identifiers, duplicate names, and unsafe filenames.

Status-specific validation

Every populated row needs an exact, unique OfferID and a valid OfferStatus. HOLD and DONE may leave the remaining fields blank; if they include OutputName, it must still be safe and unique. Only READY rows must pass the complete B–K and M contract.

Build one controlled Word offer-letter template

Ten exact bookmarks

Create OfferID, CandidateName, JobTitle, DepartmentName, WorkLocation, EmploymentType, OfferDateDisplay, StartDateDisplay, CompensationDisplay, and HiringManagerName.

Uniform formatting

Keep each bookmark in one consistent style and outside nested fields, content controls, images, or paragraph marks.

Approved wording

Keep standard clauses in the template. Put only reviewed variable terms in Excel.

Clean review state

Turn off Track Changes, accept or reject revisions, and remove comments before the batch.

Controlled release

Version and approve the actual DOCX separately. A matching filename does not authenticate its contents.

Unsigned drafts

Do not use a pasted signature image as proof of authority. Apply the approved signing process after review.

Preflight the entire offer batch before Word starts

Workbook gate

Require a writable .xlsm workbook, an unprotected Offers sheet, and exact A–M headers.

Row gate

Reject blank interior rows and invalid statuses, then apply the complete field and output contract only to READY rows.

Uniqueness gate

Reject duplicate OfferID values and case-insensitive duplicate OutputName values.

Target gate

Stop if a requested DOCX or PDF already exists. SaveAs2 can overwrite, so this guard matters.

Template gate

Check the ten bookmarks, Track Changes, revisions, and comments before processing the batch.

Change gate

Recheck the current Excel row plus the template size and modification time before generating each draft.

Free Excel VBA example: generate guarded offer-letter drafts

Save the workbook as .xlsm. Create the Offers sheet, put OfferLetterTemplate.docx in Templates beside the workbook, and run GenerateOfferLetterBatch. Results go to GeneratedOffers.

Excel VBA — guarded offer-letter batch Factory logic
Option Explicit

Private Const DATA_SHEET As String = "Offers"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "OfferLetterTemplate.docx"
Private Const OUTPUT_FOLDER As String = "\GeneratedOffers"
Private Const FIRST_DATA_ROW As Long = 2

Private Const COL_OFFER_ID As Long = 1
Private Const COL_CANDIDATE_NAME As Long = 2
Private Const COL_JOB_TITLE As Long = 3
Private Const COL_DEPARTMENT_NAME As Long = 4
Private Const COL_WORK_LOCATION As Long = 5
Private Const COL_EMPLOYMENT_TYPE As Long = 6
Private Const COL_OFFER_DATE As Long = 7
Private Const COL_START_DATE As Long = 8
Private Const COL_COMPENSATION As Long = 9
Private Const COL_HIRING_MANAGER As Long = 10
Private Const COL_OUTPUT_FORMAT As Long = 11
Private Const COL_OFFER_STATUS As Long = 12
Private Const COL_OUTPUT_NAME As Long = 13

Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const FORMAT_DOCX As String = "DOCX"
Private Const FORMAT_PDF As String = "PDF"
Private Const FORMAT_BOTH As String = "BOTH"

Private Const WD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_EXPORT_OPTIMIZE_FOR_PRINT As Long = 0
Private Const WD_EXPORT_ALL_DOCUMENT As Long = 0
Private Const WD_EXPORT_DOCUMENT_CONTENT As Long = 0
Private Const WD_EXPORT_CREATE_HEADING_BOOKMARKS As Long = 1
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_ALERTS_NONE As Long = 0
Private Const FILE_ATTRIBUTES As Long = 39

Public Sub GenerateOfferLetterBatch()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim dataValues As Variant
    Dim lastCell As Range
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim dataRow As Long
    Dim readyCount As Long
    Dim generatedCount As Long
    Dim templatePath As String
    Dim outputFolder As String
    Dim offerId As String
    Dim candidateName As String
    Dim jobTitle As String
    Dim departmentName As String
    Dim workLocation As String
    Dim employmentType As String
    Dim offerDateText As String
    Dim startDateText As String
    Dim compensationText As String
    Dim hiringManagerName As String
    Dim outputFormat As String
    Dim offerStatus As String
    Dim outputName As String
    Dim templateSize As Double
    Dim templateModified As Date
    Dim docxPath As String
    Dim pdfPath As String
    Dim workingDocxPath As String
    Dim runToken As String
    Dim idRows As Object
    Dim outputRows As Object
    Dim wdApp As Object
    Dim doc As Object
    Dim errorNumber As Long
    Dim errorMessage As String
    Dim currentOfferId As String

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    If Len(wb.Path) = 0 Then
        Err.Raise vbObjectError + 3300, , _
            "Save this workbook as .xlsm before generating offer letters."
    End If
    If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
        Err.Raise vbObjectError + 3301, , _
            "Save the workbook as an Excel Macro-Enabled Workbook (.xlsm)."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 3301, , _
            "Open a writable workbook before generating offer letters."
    End If
    If wb.ProtectStructure Then
        Err.Raise vbObjectError + 3302, , _
            "Unprotect the workbook structure before generation."
    End If

    Set ws = RequireWorksheet(wb, DATA_SHEET)
    If ws.ProtectContents Then
        Err.Raise vbObjectError + 3303, , _
            "Unprotect the Offers sheet so DONE can be recorded."
    End If
    ValidateHeaders ws

    Set lastCell = ws.Range("A:M").Find(What:="*", _
        After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    If lastCell Is Nothing Then
        Err.Raise vbObjectError + 3304, , "The Offers sheet is empty."
    End If
    If lastCell.Row < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 3304, , _
            "The Offers sheet has no offer rows."
    End If
    lastRow = lastCell.Row

    Application.CalculateFull
    dataValues = ws.Range(ws.Cells(FIRST_DATA_ROW, 1), _
        ws.Cells(lastRow, COL_OUTPUT_NAME)).Value2

    templatePath = wb.Path & TEMPLATE_FOLDER & "\" & TEMPLATE_FILE
    outputFolder = wb.Path & OUTPUT_FOLDER
    runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
        Format$(CLng(Timer * 100), "00000000")
    If Not FileExists(templatePath) Then
        Err.Raise vbObjectError + 3305, , _
            "Word template is missing: " & templatePath
    End If
    templateSize = FileLen(templatePath)
    templateModified = FileDateTime(templatePath)

    Set idRows = CreateObject("Scripting.Dictionary")
    idRows.CompareMode = vbTextCompare
    Set outputRows = CreateObject("Scripting.Dictionary")
    outputRows.CompareMode = vbTextCompare

    ' Pass 1: validate every row and every target before Word starts.
    For dataRow = 1 To UBound(dataValues, 1)
        rowNumber = dataRow + 1
        If ArrayRowIsBlank(dataValues, dataRow) Then
            Err.Raise vbObjectError + 3310, , _
                "Blank row inside the data range: row " & rowNumber
        End If

        offerStatus = ReadRequiredArrayText( _
            dataValues(dataRow, COL_OFFER_STATUS), "OfferStatus", rowNumber)
        ValidateOfferStatus offerStatus, rowNumber

        offerId = ReadRequiredArrayText(dataValues(dataRow, COL_OFFER_ID), _
            "OfferID", rowNumber)
        RequireTextStorage dataValues(dataRow, COL_OFFER_ID), _
            "OfferID", rowNumber
        ValidateIdentifier offerId, "OfferID", rowNumber
        If idRows.Exists(offerId) Then
            Err.Raise vbObjectError + 3311, , _
                "Duplicate OfferID in rows " & idRows(offerId) & _
                " and " & rowNumber & ": " & offerId
        End If
        idRows.Add offerId, rowNumber

        If offerStatus = STATUS_READY Then
            candidateName = ReadRequiredArrayText( _
                dataValues(dataRow, COL_CANDIDATE_NAME), _
                "CandidateName", rowNumber)
            jobTitle = ReadRequiredArrayText( _
                dataValues(dataRow, COL_JOB_TITLE), "JobTitle", rowNumber)
            departmentName = ReadOptionalArrayText( _
                dataValues(dataRow, COL_DEPARTMENT_NAME), _
                "DepartmentName", rowNumber)
            workLocation = ReadRequiredArrayText( _
                dataValues(dataRow, COL_WORK_LOCATION), _
                "WorkLocation", rowNumber)
            employmentType = ReadRequiredArrayText( _
                dataValues(dataRow, COL_EMPLOYMENT_TYPE), _
                "EmploymentType", rowNumber)
            offerDateText = ReadRequiredDisplayText( _
                dataValues(dataRow, COL_OFFER_DATE), _
                "OfferDateDisplay", rowNumber)
            startDateText = ReadRequiredDisplayText( _
                dataValues(dataRow, COL_START_DATE), _
                "StartDateDisplay", rowNumber)
            compensationText = ReadRequiredDisplayText( _
                dataValues(dataRow, COL_COMPENSATION), _
                "CompensationDisplay", rowNumber)
            hiringManagerName = ReadRequiredArrayText( _
                dataValues(dataRow, COL_HIRING_MANAGER), _
                "HiringManagerName", rowNumber)
            outputFormat = ReadRequiredArrayText( _
                dataValues(dataRow, COL_OUTPUT_FORMAT), _
                "OutputFormat", rowNumber)
            outputName = ReadRequiredArrayText( _
                dataValues(dataRow, COL_OUTPUT_NAME), _
                "OutputName", rowNumber)
            ValidateOutputFormat outputFormat, rowNumber
        Else
            outputName = ReadOptionalArrayText( _
                dataValues(dataRow, COL_OUTPUT_NAME), _
                "OutputName", rowNumber)
        End If

        If Len(outputName) > 0 Then
            If outputRows.Exists(outputName) Then
                Err.Raise vbObjectError + 3312, , _
                    "Duplicate OutputName in rows " & outputRows(outputName) & _
                    " and " & rowNumber & ": " & outputName
            End If
            outputRows.Add outputName, rowNumber
            ValidateFileStem outputName, rowNumber
        End If

        If offerStatus = STATUS_READY Then
            readyCount = readyCount + 1
            docxPath = outputFolder & "\" & outputName & ".docx"
            pdfPath = outputFolder & "\" & outputName & ".pdf"
            If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
                RequireTargetAvailable docxPath, rowNumber
            End If
            If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
                RequireTargetAvailable pdfPath, rowNumber
            End If
        End If
    Next dataRow

    If readyCount = 0 Then
        Err.Raise vbObjectError + 3313, , _
            "No READY offer letters were found."
    End If

    EnsureFolder outputFolder
    wb.Save

    ' Validate all required Word bookmarks once before processing the batch.
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False
    wdApp.DisplayAlerts = WD_ALERTS_NONE
    wdApp.ScreenUpdating = False
    Set doc = wdApp.Documents.Open(templatePath, False, True, False)
    ValidateTemplateBookmarks doc
    doc.Close WD_DO_NOT_SAVE_CHANGES
    Set doc = Nothing

    ' Pass 2: generate from the cached values captured before preflight.
    For dataRow = 1 To UBound(dataValues, 1)
        rowNumber = dataRow + 1
        offerStatus = CStr(dataValues(dataRow, COL_OFFER_STATUS))
        If offerStatus = STATUS_READY Then
            RequireRowUnchanged ws, rowNumber, dataValues, dataRow
            RequireSourceUnchanged templatePath, templateSize, templateModified
            offerId = CStr(dataValues(dataRow, COL_OFFER_ID))
            currentOfferId = offerId
            candidateName = CStr(dataValues(dataRow, COL_CANDIDATE_NAME))
            jobTitle = CStr(dataValues(dataRow, COL_JOB_TITLE))
            departmentName = CStr(dataValues(dataRow, COL_DEPARTMENT_NAME))
            workLocation = CStr(dataValues(dataRow, COL_WORK_LOCATION))
            employmentType = CStr(dataValues(dataRow, COL_EMPLOYMENT_TYPE))
            offerDateText = CStr(dataValues(dataRow, COL_OFFER_DATE))
            startDateText = CStr(dataValues(dataRow, COL_START_DATE))
            compensationText = CStr(dataValues(dataRow, COL_COMPENSATION))
            hiringManagerName = CStr(dataValues(dataRow, COL_HIRING_MANAGER))
            outputFormat = CStr(dataValues(dataRow, COL_OUTPUT_FORMAT))
            outputName = CStr(dataValues(dataRow, COL_OUTPUT_NAME))
            docxPath = outputFolder & "\" & outputName & ".docx"
            pdfPath = outputFolder & "\" & outputName & ".pdf"

            If outputFormat = FORMAT_PDF Then
                workingDocxPath = outputFolder & "\.__pdf_stage_" & _
                    outputName & "_" & runToken & ".docx"
                RequireTargetAvailable workingDocxPath, rowNumber
            Else
                workingDocxPath = docxPath
                RequireTargetAvailable docxPath, rowNumber
            End If
            If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
                RequireTargetAvailable pdfPath, rowNumber
            End If

            Set doc = wdApp.Documents.Open(templatePath, False, True, False)
            WriteRequiredBookmark doc, "OfferID", offerId
            WriteRequiredBookmark doc, "CandidateName", candidateName
            WriteRequiredBookmark doc, "JobTitle", jobTitle
            WriteRequiredBookmark doc, "DepartmentName", departmentName
            WriteRequiredBookmark doc, "WorkLocation", workLocation
            WriteRequiredBookmark doc, "EmploymentType", employmentType
            WriteRequiredBookmark doc, "OfferDateDisplay", offerDateText
            WriteRequiredBookmark doc, "StartDateDisplay", startDateText
            WriteRequiredBookmark doc, "CompensationDisplay", compensationText
            WriteRequiredBookmark doc, "HiringManagerName", hiringManagerName
            ValidateGeneratedDocument doc
            doc.SaveAs2 workingDocxPath, WD_FORMAT_XML_DOCUMENT

            If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
                doc.ExportAsFixedFormat pdfPath, WD_EXPORT_FORMAT_PDF, _
                    False, WD_EXPORT_OPTIMIZE_FOR_PRINT, _
                    WD_EXPORT_ALL_DOCUMENT, 1, 1, _
                    WD_EXPORT_DOCUMENT_CONTENT, False, True, _
                    WD_EXPORT_CREATE_HEADING_BOOKMARKS, True, True, False
            End If

            doc.Close WD_DO_NOT_SAVE_CHANGES
            Set doc = Nothing
            If outputFormat = FORMAT_PDF Then
                If FileExists(workingDocxPath) Then Kill workingDocxPath
            End If
            workingDocxPath = vbNullString

            ValidateGeneratedOutputs outputFormat, docxPath, pdfPath, rowNumber
            SaveDoneStatus wb, ws, rowNumber
            generatedCount = generatedCount + 1
        End If
    Next dataRow

    wdApp.ScreenUpdating = True
    wdApp.NormalTemplate.Saved = True
    wdApp.Quit WD_DO_NOT_SAVE_CHANGES
    Set wdApp = Nothing
    currentOfferId = vbNullString

    MsgBox generatedCount & " offer letter(s) generated.", _
        vbInformation, "Offer letter batch"
    Exit Sub

CleanFail:
    errorNumber = Err.Number
    errorMessage = Err.Description
    On Error Resume Next
    If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
    If Not wdApp Is Nothing Then
        wdApp.ScreenUpdating = True
        wdApp.NormalTemplate.Saved = True
        wdApp.Quit WD_DO_NOT_SAVE_CHANGES
    End If
    On Error GoTo 0
    MsgBox "Generation stopped." & vbCrLf & _
        IIf(Len(currentOfferId) > 0, _
            "Current OfferID: " & currentOfferId & vbCrLf, "") & _
        "Error " & errorNumber & ": " & errorMessage & vbCrLf & _
        "Review GeneratedOffers before retrying. Existing files are not deleted.", _
        vbCritical, "Offer letter batch"
End Sub

Private Function RequireWorksheet(ByVal wb As Workbook, _
                                  ByVal sheetName As String) As Worksheet
    On Error Resume Next
    Set RequireWorksheet = wb.Worksheets(sheetName)
    On Error GoTo 0
    If RequireWorksheet Is Nothing Then
        Err.Raise vbObjectError + 3320, , _
            "Required sheet is missing: " & sheetName
    End If
End Function

Private Sub ValidateHeaders(ByVal ws As Worksheet)
    Dim headers As Variant
    Dim columnNumber As Long
    Dim rawHeader As Variant
    headers = Array("OfferID", "CandidateName", "JobTitle", _
        "DepartmentName", "WorkLocation", "EmploymentType", _
        "OfferDateDisplay", "StartDateDisplay", "CompensationDisplay", "HiringManagerName", _
        "OutputFormat", "OfferStatus", "OutputName")
    For columnNumber = 1 To 13
        rawHeader = ws.Cells(1, columnNumber).Value2
        If IsError(rawHeader) Then
            Err.Raise vbObjectError + 3321, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " contains an Excel error."
        End If
        If StrComp(CStr(rawHeader), CStr(headers(columnNumber - 1)), _
                   vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 3321, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " must match exactly: " & headers(columnNumber - 1) & "."
        End If
    Next columnNumber
End Sub

Private Function ArrayRowIsBlank(ByVal values As Variant, _
                                 ByVal dataRow As Long) As Boolean
    Dim columnNumber As Long
    For columnNumber = 1 To COL_OUTPUT_NAME
        If IsError(values(dataRow, columnNumber)) Then Exit Function
        If Len(CStr(values(dataRow, columnNumber))) > 0 Then Exit Function
    Next columnNumber
    ArrayRowIsBlank = True
End Function

Private Function ReadRequiredArrayText(ByVal rawValue As Variant, _
                                       ByVal fieldName As String, _
                                       ByVal rowNumber As Long) As String
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 3322, , _
            fieldName & " contains an Excel error in row " & rowNumber & "."
    End If
    If VarType(rawValue) <> vbString Then
        Err.Raise vbObjectError + 3322, , _
            fieldName & " must be stored as text in row " & rowNumber & "."
    End If
    ReadRequiredArrayText = CStr(rawValue)
    If Len(ReadRequiredArrayText) = 0 Then
        Err.Raise vbObjectError + 3323, , _
            fieldName & " is required in row " & rowNumber & "."
    End If
    ValidateOuterSpaces ReadRequiredArrayText, fieldName, rowNumber
    ValidateSingleLine ReadRequiredArrayText, fieldName, rowNumber
    ValidateControlCharacters ReadRequiredArrayText, fieldName, rowNumber
End Function

Private Function ReadOptionalArrayText(ByVal rawValue As Variant, _
                                       ByVal fieldName As String, _
                                       ByVal rowNumber As Long) As String
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 3322, , _
            fieldName & " contains an Excel error in row " & rowNumber & "."
    End If
    If Len(CStr(rawValue)) > 0 And VarType(rawValue) <> vbString Then
        Err.Raise vbObjectError + 3322, , _
            fieldName & " must be stored as text in row " & rowNumber & "."
    End If
    ReadOptionalArrayText = CStr(rawValue)
    If Len(ReadOptionalArrayText) > 0 Then
        ValidateOuterSpaces ReadOptionalArrayText, fieldName, rowNumber
        ValidateSingleLine ReadOptionalArrayText, fieldName, rowNumber
        ValidateControlCharacters ReadOptionalArrayText, fieldName, rowNumber
    End If
End Function

Private Function ReadRequiredDisplayText(ByVal rawValue As Variant, _
                                         ByVal fieldName As String, _
                                         ByVal rowNumber As Long) As String
    RequireTextStorage rawValue, fieldName, rowNumber
    ReadRequiredDisplayText = ReadRequiredArrayText( _
        rawValue, fieldName, rowNumber)
End Function

Private Sub RequireTextStorage(ByVal rawValue As Variant, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long)
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 3322, , _
            fieldName & " contains an Excel error in row " & rowNumber & "."
    End If
    If VarType(rawValue) <> vbString Then
        Err.Raise vbObjectError + 3325, , _
            fieldName & " must be stored as text in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateOuterSpaces(ByVal valueText As String, _
                                ByVal fieldName As String, _
                                ByVal rowNumber As Long)
    If StrComp(valueText, Trim$(valueText), vbBinaryCompare) <> 0 Then
        Err.Raise vbObjectError + 3326, , _
            fieldName & " has leading or trailing spaces in row " & _
            rowNumber & "."
    End If
End Sub

Private Sub ValidateSingleLine(ByVal valueText As String, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long)
    If InStr(valueText, vbCr) > 0 Or InStr(valueText, vbLf) > 0 Or _
       InStr(valueText, vbTab) > 0 Then
        Err.Raise vbObjectError + 3327, , _
            fieldName & " must be one line in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateControlCharacters(ByVal valueText As String, _
                                      ByVal fieldName As String, _
                                      ByVal rowNumber As Long)
    Dim position As Long
    Dim characterCode As Long
    For position = 1 To Len(valueText)
        characterCode = AscW(Mid$(valueText, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            Err.Raise vbObjectError + 3324, , _
                fieldName & " contains an unsupported control character in row " & _
                rowNumber & "."
        End If
    Next position
End Sub

Private Sub ValidateIdentifier(ByVal valueText As String, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long)
    Dim position As Long
    Dim character As String
    For position = 1 To Len(valueText)
        character = Mid$(valueText, position, 1)
        If Not (character Like "[A-Za-z0-9_-]") Then
            Err.Raise vbObjectError + 3328, , _
                fieldName & " may use only letters, digits, underscore, and hyphen " & _
                "in row " & rowNumber & "."
        End If
    Next position
End Sub

Private Sub ValidateOutputFormat(ByVal valueText As String, _
                                 ByVal rowNumber As Long)
    If valueText <> FORMAT_DOCX And valueText <> FORMAT_PDF And _
       valueText <> FORMAT_BOTH Then
        Err.Raise vbObjectError + 3329, , _
            "OutputFormat must be DOCX, PDF, or BOTH in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateOfferStatus(ByVal valueText As String, _
                                 ByVal rowNumber As Long)
    If valueText <> STATUS_READY And valueText <> STATUS_HOLD And _
       valueText <> STATUS_DONE Then
        Err.Raise vbObjectError + 3330, , _
            "OfferStatus must be READY, HOLD, or DONE in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateFileStem(ByVal fileStem As String, _
                             ByVal rowNumber As Long)
    Dim forbidden As Variant
    Dim item As Variant
    Dim position As Long
    Dim characterCode As Long
    Dim baseName As String
    forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    If Len(fileStem) > 120 Then
        Err.Raise vbObjectError + 3331, , _
            "OutputName exceeds 120 characters in row " & rowNumber & "."
    End If
    If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
        Err.Raise vbObjectError + 3331, , _
            "OutputName ends with a dot or space in row " & rowNumber & "."
    End If
    If LCase$(Right$(fileStem, 5)) = ".docx" Or _
       LCase$(Right$(fileStem, 4)) = ".pdf" Then
        Err.Raise vbObjectError + 3331, , _
            "Store OutputName without .docx or .pdf in row " & rowNumber & "."
    End If
    For Each item In forbidden
        If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
            Err.Raise vbObjectError + 3331, , _
                "OutputName contains a forbidden character in row " & _
                rowNumber & "."
        End If
    Next item
    For position = 1 To Len(fileStem)
        characterCode = AscW(Mid$(fileStem, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            Err.Raise vbObjectError + 3331, , _
                "OutputName contains a Windows control character in row " & _
                rowNumber & "."
        End If
    Next position
    baseName = fileStem
    If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
        baseName = Left$(baseName, InStr(1, baseName, ".", vbBinaryCompare) - 1)
    End If
    If IsReservedWindowsName(UCase$(baseName)) Then
        Err.Raise vbObjectError + 3331, , _
            "OutputName starts with a reserved Windows name in row " & _
            rowNumber & "."
    End If
End Sub

Private Function IsReservedWindowsName(ByVal upperBase As String) As Boolean
    Dim finalCharacter As String
    Select Case upperBase
        Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
            IsReservedWindowsName = True
            Exit Function
    End Select
    If Left$(upperBase, 3) = "COM" Or Left$(upperBase, 3) = "LPT" Then
        If Len(upperBase) = 4 And Mid$(upperBase, 4, 1) Like "[1-9]" Then
            IsReservedWindowsName = True
            Exit Function
        End If
        If Len(upperBase) = 4 Then
            finalCharacter = Mid$(upperBase, 4, 1)
            If finalCharacter = ChrW$(&HB9) Or _
               finalCharacter = ChrW$(&HB2) Or _
               finalCharacter = ChrW$(&HB3) Then
                IsReservedWindowsName = True
            End If
        End If
    End If
End Function

Private Sub ValidateTemplateBookmarks(ByVal doc As Object)
    Dim names As Variant
    Dim item As Variant
    If doc.TrackRevisions Then
        Err.Raise vbObjectError + 3332, , _
            "Turn off Track Changes in OfferLetterTemplate.docx."
    End If
    If doc.Revisions.Count > 0 Then
        Err.Raise vbObjectError + 3332, , _
            "Accept or reject all tracked revisions in the offer template."
    End If
    If doc.Comments.Count > 0 Then
        Err.Raise vbObjectError + 3332, , _
            "Remove all comments from the offer template."
    End If
    names = Array("OfferID", "CandidateName", "JobTitle", _
        "DepartmentName", "WorkLocation", "EmploymentType", _
        "OfferDateDisplay", "StartDateDisplay", _
        "CompensationDisplay", "HiringManagerName")
    For Each item In names
        If Not doc.Bookmarks.Exists(CStr(item)) Then
            Err.Raise vbObjectError + 3332, , _
                "Required Word bookmark is missing: " & item
        End If
    Next item
End Sub

Private Sub ValidateGeneratedDocument(ByVal doc As Object)
    If doc.TrackRevisions Or doc.Revisions.Count > 0 Or _
       doc.Comments.Count > 0 Then
        Err.Raise vbObjectError + 3339, , _
            "Generated offer contains tracked changes or comments."
    End If
End Sub

Private Sub WriteRequiredBookmark(ByVal doc As Object, _
                                  ByVal bookmarkName As String, _
                                  ByVal replacementText As String)
    Dim targetRange As Object
    Dim targetStyle As Variant
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 3333, , _
            "Required Word bookmark is missing: " & bookmarkName
    End If
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Or _
       targetRange.InlineShapes.Count > 0 Then
        Err.Raise vbObjectError + 3334, , _
            "Bookmark contains a field, content control, or image: " & bookmarkName
    End If
    targetStyle = targetRange.Style
    targetRange.Text = replacementText
    targetRange.Style = targetStyle
    doc.Bookmarks.Add bookmarkName, targetRange
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 3335, , _
            "Bookmark was not restored: " & bookmarkName
    End If
End Sub

Private Sub ValidateGeneratedOutputs(ByVal outputFormat As String, _
                                     ByVal docxPath As String, _
                                     ByVal pdfPath As String, _
                                     ByVal rowNumber As Long)
    If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
        If Not FileExists(docxPath) Then
            Err.Raise vbObjectError + 3343, , _
                "DOCX output is missing for row " & rowNumber & "."
        End If
        If FileLen(docxPath) = 0 Then
            Err.Raise vbObjectError + 3344, , _
                "DOCX output is empty for row " & rowNumber & "."
        End If
    End If

    If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
        If Not FileExists(pdfPath) Then
            Err.Raise vbObjectError + 3345, , _
                "PDF output is missing for row " & rowNumber & "."
        End If
        If FileLen(pdfPath) = 0 Then
            Err.Raise vbObjectError + 3346, , _
                "PDF output is empty for row " & rowNumber & "."
        End If
    End If
End Sub

Private Sub RequireTargetAvailable(ByVal filePath As String, _
                                   ByVal rowNumber As Long)
    If FileExists(filePath) Then
        Err.Raise vbObjectError + 3336, , _
            "Output already exists for row " & rowNumber & ": " & filePath
    End If
End Sub

Private Function FileExists(ByVal filePath As String) As Boolean
    Dim foundName As String
    Dim checkErrorNumber As Long
    Dim checkErrorDescription As String
    On Error GoTo CheckFailed
    foundName = Dir$(filePath, FILE_ATTRIBUTES)
    FileExists = (Len(foundName) > 0)
    Exit Function
CheckFailed:
    checkErrorNumber = Err.Number
    checkErrorDescription = Err.Description
    Err.Clear
    Err.Raise vbObjectError + 3338, "FileExists", _
        "Could not check path: " & filePath & vbCrLf & _
        "File-system error " & checkErrorNumber & ": " & _
        checkErrorDescription
End Function

Private Sub EnsureFolder(ByVal folderPath As String)
    If Len(Dir$(folderPath, vbDirectory)) = 0 Then MkDir folderPath
    If Len(Dir$(folderPath, vbDirectory)) = 0 Then
        Err.Raise vbObjectError + 3337, , _
            "Could not create output folder: " & folderPath
    End If
End Sub

Private Sub RequireRowUnchanged(ByVal ws As Worksheet, _
                                ByVal rowNumber As Long, _
                                ByVal cachedValues As Variant, _
                                ByVal dataRow As Long)
    Dim columnNumber As Long
    Dim currentValue As Variant
    Dim cachedValue As Variant
    For columnNumber = 1 To COL_OUTPUT_NAME
        currentValue = ws.Cells(rowNumber, columnNumber).Value2
        cachedValue = cachedValues(dataRow, columnNumber)
        If IsError(currentValue) Or IsError(cachedValue) Then
            Err.Raise vbObjectError + 3340, , _
                "Excel row changed or contains an error: row " & rowNumber
        End If
        If VarType(currentValue) <> VarType(cachedValue) Or _
           StrComp(CStr(currentValue), CStr(cachedValue), _
                   vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 3340, , _
                "Excel row changed after preflight: row " & rowNumber
        End If
    Next columnNumber
End Sub

Private Sub RequireSourceUnchanged(ByVal filePath As String, _
                                   ByVal expectedSize As Double, _
                                   ByVal expectedModified As Date)
    If Not FileExists(filePath) Then
        Err.Raise vbObjectError + 3341, , _
            "Offer template is no longer available: " & filePath
    End If
    If CDbl(FileLen(filePath)) <> expectedSize Or _
       FileDateTime(filePath) <> expectedModified Then
        Err.Raise vbObjectError + 3341, , _
            "Offer template changed after preflight. Stop and review it again."
    End If
End Sub

Private Sub SaveDoneStatus(ByVal wb As Workbook, _
                           ByVal ws As Worksheet, _
                           ByVal rowNumber As Long)
    Dim saveErrorNumber As Long
    Dim saveErrorDescription As String
    ws.Cells(rowNumber, COL_OFFER_STATUS).Value2 = STATUS_DONE
    On Error GoTo SaveFailed
    wb.Save
    On Error GoTo 0
    Exit Sub
SaveFailed:
    saveErrorNumber = Err.Number
    saveErrorDescription = Err.Description
    Err.Clear
    ws.Cells(rowNumber, COL_OFFER_STATUS).Value2 = STATUS_READY
    On Error GoTo 0
    Err.Raise vbObjectError + 3342, "SaveDoneStatus", _
        "The offer files were created, but DONE was not saved for row " & _
        rowNumber & ". Excel error " & saveErrorNumber & ": " & _
        saveErrorDescription
End Sub

The example uses Word’s documented bookmark checks, DOCX saving, and PDF export.

Review every generated offer before release

Candidate identity

Verify OfferID, candidate name, and the intended recipient before any delivery.

Role and location

Confirm job title, department, work location, employment type, and hiring manager.

Dates and terms

Compare offer date, start date, compensation wording, conditions, and expiry language with the approved source.

Document output

Open each DOCX and PDF. Inspect pagination, headers, footers, tables, fonts, links, and all pages.

Authority and signing

Confirm the approver and use the organization’s required signature or e-signature process.

Release record

Record the reviewer, approved version, recipient, delivery method, time, correction history, and retention decision outside OfferStatus.

Know what the free example does not guarantee

No employment decision

The macro cannot approve a candidate, role, compensation package, clause, or exception.

No legal determination

Local requirements, notices, approvals, and retention rules still need qualified review.

No signature proof

The example does not sign or authenticate an offer and does not insert signature images.

No cryptographic template check

Size and modification time detect ordinary changes but do not prove file identity.

No atomic file-and-status commit

Files and the Excel DONE status are separate operations; reconcile partial results after a failure.

No automatic delivery

Email, portals, identity checks, acceptance tracking, and e-signature remain separate workflows.

Use Factory for repeatable offer-letter batches

DocxForge Pro Factory maps many reviewed Excel rows to one standard Word template and creates a separate document for each row. That is the offer-letter pattern described here.

Where Factory fits

Use it when every candidate follows the same approved offer template. If one candidate needs an offer, agreement, checklist, and other different documents from different templates, use Contractor instead.

DocxForge Pro Factory offer-letter Excel data screen
Review candidate rows and mapped offer fields.
DocxForge Pro Factory offer-letter documents screen
Confirm the one-template batch and separate outputs.
Generate standardized offer letters with Factory7 days free, then $38 every 3 months • 14-day refund after purchase.
Many candidate rowsOne approved templateSeparate filesLocal processing
Start Free 7-Day Trial

Frequently asked questions

Is this a Factory workflow?

Yes. Each READY row represents one candidate, all rows use one approved OfferLetterTemplate.docx, and each row produces its own DOCX and/or PDF. That is Factory logic.

Should dates and compensation be Excel numbers?

No for this example. Store OfferDateDisplay, StartDateDisplay, and CompensationDisplay as reviewed document-ready text so Word receives the intended wording rather than an Excel serial or raw number.

Can DepartmentName be blank?

Yes. The column and Word bookmark must still exist, but the value may be blank when the approved template allows it. All other document fields in the example are required for READY rows.

Does DONE mean the candidate offer is approved?

No. DONE confirms that the requested files were created and the workbook status was saved. HR still has to verify the terms, authority, applicable review, signing process, recipient, and release.

Does the VBA insert a signature image?

No. The free example intentionally creates unsigned drafts. Apply an approved signing or e-signature process after HR review instead of treating an image as proof of authorization.

When should Contractor be used instead?

Use Contractor when one candidate or employee case must generate several different documents from different Word templates. Use Factory when many candidate rows use one standard offer-letter template.

Turn reviewed candidate rows into consistent offer-letter draftsUse one approved Word template locally. 7 days free, then $38 every 3 months • 14-day refund.
Factory workflowOne templateDOCX + PDFHR release gate
Start Free 7-Day Trial