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


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.