Document Automation

How to Generate Customer Letters in Batches from a Spreadsheet

Turn independent spreadsheet rows into separate customer letters by validating identity, address text, letter content, output format, release status, and filenames before Word starts.

One row per letter
One standard template
Unique output names
DOCX / PDF / both
This workflow creates files only. Delivery consent, address verification, retention, and communication logging remain separate controls.

Quick answer: use one controlled row for every customer letter

Excel owns the letter record

Store a stable LetterID, customer and address text, salutation, date display, subject, body, closing, requested output, status, and unique filename.

Word owns the approved layout

Keep branding, fixed notices, typography, margins, headers, footers, and eight declared bookmarks in one standard template.

Factory workflow

Many independent customer rows pass through one standard letter template. Contractor is reserved for one case that creates several different documents from different templates.

Define the customer-letter contract before batching

Purpose and audience

Use one template for one approved letter purpose. Do not mix unrelated notices merely because they share a recipient table.

Recipient identity

Keep LetterID separate from CustomerName and OutputName so corrections do not silently change record identity.

Address responsibility

Define who verifies postal data, allowed blank lines, country-specific formatting, and returned-mail handling.

Content ownership

Approve the fixed template language and decide who may supply or change the per-row BodyText.

Release state

READY permits generation, HOLD blocks it, and DONE records a successfully saved output.

Delivery boundary

Generation creates files. Emailing, printing, mailing, consent, and delivery logs require separate controls.

Create the Letters sheet in one exact A–M order

Required headers: A — LetterID; B — CustomerName; C — AddressLine1; D — AddressLine2; E — CityRegionPostalDisplay; F — Salutation; G — LetterDateDisplay; H — SubjectLine; I — BodyText; J — ClosingName; K — OutputFormat; L — LetterStatus; M — OutputName.

A: stable text ID

Format LetterID as Text before entry or import. Use letters, digits, underscore, or hyphen and preserve leading zeros.

B–F: recipient block

Keep each component explicit. AddressLine2 may be blank; other mapped values are required and single-line.

G: approved date display

Store the visible letter date as text instead of relying on an Excel numeric date and cell formatting.

H–J: letter content

Subject and closing are single-line. BodyText may contain paragraph breaks but no tabs or unsupported control characters.

K–L: output and status

Use DOCX, PDF, or BOTH exactly. Use READY, HOLD, or DONE exactly.

M: unique safe stem

Store the output name without an extension. The macro checks duplicates, Windows characters, device names, length, and existing targets.

Build one Word template with eight uniform bookmarks

LetterID

Place the record identity where reviewers can verify the output against Excel.

CustomerName and AddressBlock

The macro builds the address block from the three address fields and omits the optional second line when blank.

Salutation and LetterDate

Keep punctuation and fixed labels outside bookmarks unless the source contract owns them.

SubjectLine

Apply one uniform Word style across the complete bookmark range.

BodyText

Reserve a plain-text bookmark that may expand to several paragraphs. Do not place fields, content controls, or images inside it.

ClosingName

Use the approved closing identity; a typed name is not a digital signature or proof of authorization.

Complete the full preflight before Word starts

Exact headers

Reject missing, reordered, misspelled, or space-padded A–M headers.

Complete rows

Reject blank rows inside the data range and errors in required fields.

Document-ready text

Require text storage for LetterID and LetterDateDisplay and exact untrimmed contract values.

Unique identity and filenames

Reject duplicate LetterID and case-insensitive duplicate OutputName before generating anything.

Existing targets

Check every requested DOCX/PDF once during preflight and again immediately before the current letter.

Template destinations

Open the template read-only and confirm all eight required bookmarks before processing the batch.

Free VBA example: generate separate customer letters

Save Excel as .xlsm. Put CustomerLetterTemplate.docx in Templates beside the workbook. The macro creates GeneratedLetters, validates every row first, caches the approved values, opens a fresh read-only template for each READY row, and records DONE after saving.

Two-pass control

All rows, filenames, targets, formats, statuses, and template bookmarks are checked before the first customer letter is created.

Style-aware bookmarks

Text replacement restores the bookmark and reapplies its uniform Word style.

Output choice

DOCX keeps the editable file, PDF retains only the PDF after export, and BOTH keeps both files.

Single-writer scope

The example blocks existing files but assumes one process owns the output folder. Review partial output before retrying after an error.

Excel VBA — customer letter batch generatorOne template • separate outputs
Option Explicit

Private Const DATA_SHEET As String = "Letters"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "CustomerLetterTemplate.docx"
Private Const OUTPUT_FOLDER As String = "\GeneratedLetters"
Private Const FIRST_DATA_ROW As Long = 2

Private Const COL_LETTER_ID As Long = 1
Private Const COL_CUSTOMER_NAME As Long = 2
Private Const COL_ADDRESS_1 As Long = 3
Private Const COL_ADDRESS_2 As Long = 4
Private Const COL_CITY_POSTAL As Long = 5
Private Const COL_SALUTATION As Long = 6
Private Const COL_LETTER_DATE As Long = 7
Private Const COL_SUBJECT As Long = 8
Private Const COL_BODY As Long = 9
Private Const COL_CLOSING_NAME As Long = 10
Private Const COL_OUTPUT_FORMAT As Long = 11
Private Const COL_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_DOCUMENT_DEFAULT As Long = 16
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 GenerateCustomerLetterBatch()
    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 letterId As String
    Dim customerName As String
    Dim addressLine1 As String
    Dim addressLine2 As String
    Dim cityPostal As String
    Dim salutation As String
    Dim letterDateText As String
    Dim subjectText As String
    Dim bodyText As String
    Dim closingName As String
    Dim outputFormat As String
    Dim letterStatus As String
    Dim outputName As String
    Dim addressBlock As String
    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 currentLetterId 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 letters."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 3301, , _
            "Open a writable workbook before generating 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 Letters 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 Letters sheet is empty."
    End If
    If lastCell.Row < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 3304, , _
            "The Letters sheet has no customer 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
    EnsureFolder outputFolder

    Set idRows = CreateObject("Scripting.Dictionary")
    idRows.CompareMode = vbBinaryCompare
    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

        letterId = ReadRequiredArrayText(dataValues(dataRow, COL_LETTER_ID), _
            "LetterID", rowNumber)
        customerName = ReadRequiredArrayText( _
            dataValues(dataRow, COL_CUSTOMER_NAME), "CustomerName", rowNumber)
        addressLine1 = ReadRequiredArrayText( _
            dataValues(dataRow, COL_ADDRESS_1), "AddressLine1", rowNumber)
        addressLine2 = ReadOptionalArrayText( _
            dataValues(dataRow, COL_ADDRESS_2), "AddressLine2", rowNumber)
        cityPostal = ReadRequiredArrayText( _
            dataValues(dataRow, COL_CITY_POSTAL), _
            "CityRegionPostalDisplay", rowNumber)
        salutation = ReadRequiredArrayText( _
            dataValues(dataRow, COL_SALUTATION), "Salutation", rowNumber)
        letterDateText = ReadRequiredDisplayText( _
            dataValues(dataRow, COL_LETTER_DATE), _
            "LetterDateDisplay", rowNumber)
        subjectText = ReadRequiredArrayText( _
            dataValues(dataRow, COL_SUBJECT), "SubjectLine", rowNumber)
        bodyText = ReadRequiredBodyText( _
            dataValues(dataRow, COL_BODY), "BodyText", rowNumber)
        closingName = ReadRequiredArrayText( _
            dataValues(dataRow, COL_CLOSING_NAME), "ClosingName", rowNumber)
        outputFormat = ReadRequiredArrayText( _
            dataValues(dataRow, COL_OUTPUT_FORMAT), "OutputFormat", rowNumber)
        letterStatus = ReadRequiredArrayText( _
            dataValues(dataRow, COL_STATUS), "LetterStatus", rowNumber)
        outputName = ReadRequiredArrayText( _
            dataValues(dataRow, COL_OUTPUT_NAME), "OutputName", rowNumber)

        RequireTextStorage dataValues(dataRow, COL_LETTER_ID), _
            "LetterID", rowNumber
        ValidateIdentifier letterId, "LetterID", rowNumber
        If idRows.Exists(letterId) Then
            Err.Raise vbObjectError + 3311, , _
                "Duplicate LetterID in rows " & idRows(letterId) & _
                " and " & rowNumber & ": " & letterId
        End If
        idRows.Add letterId, rowNumber

        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
        ValidateOutputFormat outputFormat, rowNumber
        ValidateLetterStatus letterStatus, rowNumber

        If letterStatus = 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 customer letters were found."
    End If

    ' 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
        letterStatus = CStr(dataValues(dataRow, COL_STATUS))
        If letterStatus = STATUS_READY Then
            letterId = CStr(dataValues(dataRow, COL_LETTER_ID))
            currentLetterId = letterId
            customerName = CStr(dataValues(dataRow, COL_CUSTOMER_NAME))
            addressLine1 = CStr(dataValues(dataRow, COL_ADDRESS_1))
            addressLine2 = CStr(dataValues(dataRow, COL_ADDRESS_2))
            cityPostal = CStr(dataValues(dataRow, COL_CITY_POSTAL))
            salutation = CStr(dataValues(dataRow, COL_SALUTATION))
            letterDateText = CStr(dataValues(dataRow, COL_LETTER_DATE))
            subjectText = CStr(dataValues(dataRow, COL_SUBJECT))
            bodyText = NormalizeBodyText(CStr(dataValues(dataRow, COL_BODY)))
            closingName = CStr(dataValues(dataRow, COL_CLOSING_NAME))
            outputFormat = CStr(dataValues(dataRow, COL_OUTPUT_FORMAT))
            outputName = CStr(dataValues(dataRow, COL_OUTPUT_NAME))
            addressBlock = BuildAddressBlock(addressLine1, addressLine2, cityPostal)
            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, "LetterID", letterId
            WriteRequiredBookmark doc, "CustomerName", customerName
            WriteRequiredBookmark doc, "AddressBlock", addressBlock
            WriteRequiredBookmark doc, "Salutation", salutation
            WriteRequiredBookmark doc, "LetterDate", letterDateText
            WriteRequiredBookmark doc, "SubjectLine", subjectText
            WriteRequiredBookmark doc, "BodyText", bodyText
            WriteRequiredBookmark doc, "ClosingName", closingName
            doc.SaveAs2 workingDocxPath, WD_FORMAT_DOCUMENT_DEFAULT

            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, True, 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

            ws.Cells(rowNumber, COL_STATUS).Value = STATUS_DONE
            wb.Save
            generatedCount = generatedCount + 1
        End If
    Next dataRow

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

    MsgBox generatedCount & " customer letter(s) generated.", _
        vbInformation, "Customer 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(currentLetterId) > 0, _
            "Current LetterID: " & currentLetterId & vbCrLf, "") & _
        "Error " & errorNumber & ": " & errorMessage & vbCrLf & _
        "Review GeneratedLetters before retrying. Existing files are not deleted.", _
        vbCritical, "Customer 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("LetterID", "CustomerName", "AddressLine1", _
        "AddressLine2", "CityRegionPostalDisplay", "Salutation", _
        "LetterDateDisplay", "SubjectLine", "BodyText", "ClosingName", _
        "OutputFormat", "LetterStatus", "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
    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, False
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
    ReadOptionalArrayText = CStr(rawValue)
    If Len(ReadOptionalArrayText) > 0 Then
        ValidateOuterSpaces ReadOptionalArrayText, fieldName, rowNumber
        ValidateSingleLine ReadOptionalArrayText, fieldName, rowNumber
        ValidateControlCharacters ReadOptionalArrayText, fieldName, _
            rowNumber, False
    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 Function ReadRequiredBodyText(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
    ReadRequiredBodyText = CStr(rawValue)
    If Len(ReadRequiredBodyText) = 0 Then
        Err.Raise vbObjectError + 3323, , _
            fieldName & " is required in row " & rowNumber & "."
    End If
    ValidateOuterSpaces ReadRequiredBodyText, fieldName, rowNumber
    ValidateControlCharacters ReadRequiredBodyText, fieldName, _
        rowNumber, True
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, _
                                      ByVal allowLineBreaks As Boolean)
    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
            If allowLineBreaks And _
               (characterCode = 10 Or characterCode = 13) Then
                ' Allowed paragraph separator in BodyText.
            Else
                Err.Raise vbObjectError + 3324, , _
                    fieldName & " contains an unsupported control character in row " & _
                    rowNumber & "."
            End If
        End If
    Next position
End Sub

Private Function NormalizeBodyText(ByVal valueText As String) As String
    NormalizeBodyText = Replace(valueText, vbCrLf, vbLf)
    NormalizeBodyText = Replace(NormalizeBodyText, vbCr, vbLf)
    NormalizeBodyText = Replace(NormalizeBodyText, vbLf, vbCr)
End Function

Private Function BuildAddressBlock(ByVal addressLine1 As String, _
                                   ByVal addressLine2 As String, _
                                   ByVal cityPostal As String) As String
    BuildAddressBlock = addressLine1
    If Len(addressLine2) > 0 Then
        BuildAddressBlock = BuildAddressBlock & vbCr & addressLine2
    End If
    BuildAddressBlock = BuildAddressBlock & vbCr & cityPostal
End Function

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 ValidateLetterStatus(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, , _
            "LetterStatus 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
    names = Array("LetterID", "CustomerName", "AddressBlock", _
        "Salutation", "LetterDate", "SubjectLine", "BodyText", "ClosingName")
    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 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 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

Word VBA references: Bookmarks.Add, Document.SaveAs2, and Document.ExportAsFixedFormat.

Recover without deleting evidence of a partial run

Read the LetterID

The error message identifies the current letter when generation has entered the second pass.

Inspect the output folder

Some earlier rows may already be DONE and saved. Do not delete or regenerate them blindly.

Check the workbook status

The macro saves after each successful letter. Confirm which rows show DONE before changing anything.

Correct one cause

Fix the source, template, permissions, or output conflict that stopped the run.

Use a fresh output name

Do not overwrite an earlier letter to force a retry. Version or archive according to policy.

Rerun READY rows

DONE and HOLD rows are skipped. Existing target checks protect ordinary retries in a single-writer folder.

Run a six-letter pilot before the production batch

1. Typical address

Verify every bookmark, filename, DOCX, and PDF.

2. Blank AddressLine2

Confirm the address block has no empty gap.

3. Long customer and subject

Check wrapping, alignment, and page breaks.

4. Multi-paragraph body

Confirm paragraph styles and spacing in Word and PDF.

5. DOCX, PDF, and BOTH

Verify exactly which files remain for each mode.

6. Existing filename

Confirm the batch stops before overwriting the target.

Generate repeat customer letters with Factory

DocxForge Pro Factory fits many independent spreadsheet rows, one standard customer-letter template, and one separate output per selected row.

Review the source rows

Confirm customer fields, document-ready values, selected records, and output names.

Review the template

Confirm the approved standard letter and its mapped destinations.

Run a pilot first

Generate representative customers before increasing batch size.

Where it does not fit

Contractor is for one case that creates several different documents from different Word templates.

DocxForge Pro Factory Excel data for customer letter batches
Review customer rows and document-ready values.
DocxForge Pro Factory settings with one customer letter template
Confirm one standard letter template and output settings.
Turn approved customer rows into repeatable Word and PDF lettersTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Factory workflowCustomer lettersLocal processingDOCX + PDF
Start Free 7-Day Trial

Frequently asked questions

Is this a Word Mail Merge workflow?

No. The VBA example creates one fresh document from one standard bookmark template for each READY row. Native Mail Merge is a valid alternative when one combined merge result and its field behavior meet the requirement.

Why are dates and addresses stored as document-ready text?

Excel numeric values and cell appearance do not guarantee the exact text Word receives. Explicit display fields preserve approved visible dates, postal formatting, leading zeros, and punctuation.

Can BodyText contain multiple paragraphs?

Yes. The helper allows line breaks in BodyText and normalizes them to Word paragraph marks. Other mapped fields are deliberately restricted to one line.

Will the macro overwrite an existing customer letter?

It checks requested target files before Word starts and again immediately before each letter. The example assumes one writer owns the output folder; concurrent processes require additional coordination.

Does this workflow send email or postal mail?

No. It creates local DOCX/PDF files only. Delivery, consent, address verification, retention, and communication logging remain separate business processes.

When should I use Contractor instead of Factory?

Use Factory for many independent customer rows and one standard letter template. Use Contractor when one customer case must create several different documents from different Word templates.

Standardize recurring customer-letter batches without rebuilding each fileRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
One row per letterOne approved templateSeparate output filesReviewable workflow
Start Free 7-Day Trial