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.
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.
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.
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 SubWord 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.


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.