How to Automate Tenant Notice Letters from Excel
Turn reviewed notice rows into separate Word and PDF drafts with one controlled template, exact dates and deadlines, guarded filenames, and a delivery review gate.
Quick answer: separate notice generation from service and proof
Use one TenantNotices row per notice and one approved Word template for one exact NoticeType + Jurisdiction combination. Run a separate batch whenever either value changes. Keep authorization, service, delivery evidence, and case decisions outside NoticeStatus.
The workbook supplies reviewed wording and display values. A qualified reviewer must confirm the correct template, grounds, required language, dates, deadline calculation, delivery method, and evidence for the actual jurisdiction and case.
Create the TenantNotices sheet in one exact A–M order
A NoticeID; B TenantName; C PropertyAddress; D Jurisdiction; E NoticeType; F DeliveryMethod; G NoticeDateDisplay; H ComplianceDeadlineDisplay; I NoticeBody; J IssuedBy; K OutputFormat; L NoticeStatus; M OutputName.
Identifiers
Store NoticeID as text and keep it unique. Use a separate Windows-safe OutputName without .docx or .pdf.
Tenant and property
Review TenantName and the complete PropertyAddress, including any unit or premises reference required by the notice.
Jurisdiction and type
Use an explicit Jurisdiction and approved NoticeType so the reviewer can select the correct released template.
Dates and deadline
Store NoticeDateDisplay and ComplianceDeadlineDisplay as reviewed text. Do not rely on Excel date formatting or an unverified deadline formula.
Body and issuer
Store NoticeBody as reviewed multiline text and IssuedBy as the authorized name or role intended for the document.
Output and status
Use DOCX, PDF, or BOTH and READY, HOLD, or DONE exactly. Only READY rows enter generation.
Every populated row needs an exact, unique NoticeID and a valid NoticeStatus. HOLD and DONE may leave the remaining fields blank; if they include OutputName, it must still be safe and unique. The complete B–K and M contract applies only to READY.
Control the notice before generating the batch
Released template
Use the template version approved for the exact notice type and jurisdiction. Similar names do not prove equivalent requirements.
Reviewed grounds
Confirm the factual grounds, dates, amounts, events, and source records before putting them into NoticeBody.
Deadline verification
Calculate and review the deadline outside this macro using the applicable rule, event date, calendar, and service method.
Authorized issuer
Confirm who may issue, sign, approve, or serve the notice. A printed name does not establish authority.
Delivery plan
Treat DeliveryMethod as a planned or recorded value. The generated document does not prove posting, mailing, hand delivery, or receipt.
Records and privacy
Restrict tenant data, generated drafts, evidence, corrections, and delivery records to approved systems and retention rules.
Build one controlled Word notice template
Ten exact bookmarks
Create NoticeID, TenantName, PropertyAddress, Jurisdiction, NoticeType, DeliveryMethod, NoticeDateDisplay, ComplianceDeadlineDisplay, NoticeBody, and IssuedBy.
Stable bookmark ranges
Keep one style inside each text bookmark and exclude nested fields, content controls, images, and paragraph markers.
Approved fixed wording
Keep mandatory labels, standard clauses, instructions, and fixed disclosures in the released template.
Clean review state
Turn off Track Changes, accept or reject revisions, and remove comments before generation.
Release control
Approve the actual DOCX separately. A matching filename and bookmark list do not authenticate its legal wording.
Signature boundary
The free example creates unsigned drafts. Apply the approved authorization and signing process after review.
Preflight every notice before Word starts
Workbook gate
Require a writable .xlsm workbook, an unprotected TenantNotices sheet, and exact A–M headers.
Status gate
Validate NoticeStatus first. Apply the complete notice and output contract only to READY rows.
Uniqueness gate
Reject duplicate NoticeID values and case-insensitive duplicate OutputName values.
Target gate
Stop if a requested DOCX or PDF already exists. SaveAs2 can overwrite, so the explicit check remains necessary.
Template gate
Check the ten bookmarks, revisions, and comments, then require every READY row to share one Jurisdiction and NoticeType.
Change gate
Recheck the current Excel row plus template size and modification time before generating each notice.
Free Excel VBA example: generate guarded tenant notice drafts
Save the workbook as .xlsm. Create the TenantNotices sheet, put TenantNoticeTemplate.docx in Templates beside the workbook, and run GenerateTenantNoticeBatch. Results go to GeneratedTenantNotices.
Option Explicit
Private Const DATA_SHEET As String = "TenantNotices"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "TenantNoticeTemplate.docx"
Private Const OUTPUT_FOLDER As String = "\GeneratedTenantNotices"
Private Const FIRST_DATA_ROW As Long = 2
Private Const COL_NOTICE_ID As Long = 1
Private Const COL_TENANT_NAME As Long = 2
Private Const COL_PROPERTY_ADDRESS As Long = 3
Private Const COL_JURISDICTION As Long = 4
Private Const COL_NOTICE_TYPE As Long = 5
Private Const COL_DELIVERY_METHOD As Long = 6
Private Const COL_NOTICE_DATE As Long = 7
Private Const COL_COMPLIANCE_DEADLINE As Long = 8
Private Const COL_NOTICE_BODY As Long = 9
Private Const COL_ISSUED_BY As Long = 10
Private Const COL_OUTPUT_FORMAT As Long = 11
Private Const COL_NOTICE_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 GenerateTenantNoticeBatch()
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 noticeId As String
Dim tenantName As String
Dim propertyAddress As String
Dim jurisdiction As String
Dim noticeType As String
Dim deliveryMethod As String
Dim noticeDateText As String
Dim complianceDeadlineText As String
Dim noticeBody As String
Dim issuedBy As String
Dim outputFormat As String
Dim noticeStatus 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 currentNoticeId As String
Dim batchJurisdiction As String
Dim batchNoticeType 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 tenant notices."
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 tenant notices."
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 TenantNotices 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 TenantNotices sheet is empty."
End If
If lastCell.Row < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 3304, , _
"The TenantNotices sheet has no tenant notice 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
noticeStatus = ReadRequiredArrayText( _
dataValues(dataRow, COL_NOTICE_STATUS), "NoticeStatus", rowNumber)
ValidateNoticeStatus noticeStatus, rowNumber
noticeId = ReadRequiredArrayText(dataValues(dataRow, COL_NOTICE_ID), _
"NoticeID", rowNumber)
RequireTextStorage dataValues(dataRow, COL_NOTICE_ID), _
"NoticeID", rowNumber
ValidateIdentifier noticeId, "NoticeID", rowNumber
If idRows.Exists(noticeId) Then
Err.Raise vbObjectError + 3311, , _
"Duplicate NoticeID in rows " & idRows(noticeId) & _
" and " & rowNumber & ": " & noticeId
End If
idRows.Add noticeId, rowNumber
If noticeStatus = STATUS_READY Then
tenantName = ReadRequiredArrayText( _
dataValues(dataRow, COL_TENANT_NAME), _
"TenantName", rowNumber)
propertyAddress = ReadRequiredArrayText( _
dataValues(dataRow, COL_PROPERTY_ADDRESS), "PropertyAddress", rowNumber)
jurisdiction = ReadRequiredArrayText( _
dataValues(dataRow, COL_JURISDICTION), _
"Jurisdiction", rowNumber)
noticeType = ReadRequiredArrayText( _
dataValues(dataRow, COL_NOTICE_TYPE), _
"NoticeType", rowNumber)
If readyCount = 0 Then
batchJurisdiction = jurisdiction
batchNoticeType = noticeType
ElseIf StrComp(jurisdiction, batchJurisdiction, _
vbTextCompare) <> 0 Or _
StrComp(noticeType, batchNoticeType, _
vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 3347, , _
"READY rows mix different jurisdictions or notice types. " & _
"Run separate batches with the approved template for each."
End If
deliveryMethod = ReadRequiredArrayText( _
dataValues(dataRow, COL_DELIVERY_METHOD), _
"DeliveryMethod", rowNumber)
noticeDateText = ReadRequiredDisplayText( _
dataValues(dataRow, COL_NOTICE_DATE), _
"NoticeDateDisplay", rowNumber)
complianceDeadlineText = ReadRequiredDisplayText( _
dataValues(dataRow, COL_COMPLIANCE_DEADLINE), _
"ComplianceDeadlineDisplay", rowNumber)
noticeBody = ReadRequiredMultilineText( _
dataValues(dataRow, COL_NOTICE_BODY), _
"NoticeBody", rowNumber)
issuedBy = ReadRequiredArrayText( _
dataValues(dataRow, COL_ISSUED_BY), _
"IssuedBy", 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 noticeStatus = 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 tenant notices 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
noticeStatus = CStr(dataValues(dataRow, COL_NOTICE_STATUS))
If noticeStatus = STATUS_READY Then
RequireRowUnchanged ws, rowNumber, dataValues, dataRow
RequireSourceUnchanged templatePath, templateSize, templateModified
noticeId = CStr(dataValues(dataRow, COL_NOTICE_ID))
currentNoticeId = noticeId
tenantName = CStr(dataValues(dataRow, COL_TENANT_NAME))
propertyAddress = CStr(dataValues(dataRow, COL_PROPERTY_ADDRESS))
jurisdiction = CStr(dataValues(dataRow, COL_JURISDICTION))
noticeType = CStr(dataValues(dataRow, COL_NOTICE_TYPE))
deliveryMethod = CStr(dataValues(dataRow, COL_DELIVERY_METHOD))
noticeDateText = CStr(dataValues(dataRow, COL_NOTICE_DATE))
complianceDeadlineText = CStr(dataValues(dataRow, COL_COMPLIANCE_DEADLINE))
noticeBody = NormalizeMultilineText( _
CStr(dataValues(dataRow, COL_NOTICE_BODY)))
issuedBy = CStr(dataValues(dataRow, COL_ISSUED_BY))
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, "NoticeID", noticeId
WriteRequiredBookmark doc, "TenantName", tenantName
WriteRequiredBookmark doc, "PropertyAddress", propertyAddress
WriteRequiredBookmark doc, "Jurisdiction", jurisdiction
WriteRequiredBookmark doc, "NoticeType", noticeType
WriteRequiredBookmark doc, "DeliveryMethod", deliveryMethod
WriteRequiredBookmark doc, "NoticeDateDisplay", noticeDateText
WriteRequiredBookmark doc, "ComplianceDeadlineDisplay", complianceDeadlineText
WriteRequiredBookmark doc, "NoticeBody", noticeBody
WriteRequiredBookmark doc, "IssuedBy", issuedBy
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
currentNoticeId = vbNullString
MsgBox generatedCount & " tenant notice(s) generated.", _
vbInformation, "Tenant notice 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(currentNoticeId) > 0, _
"Current NoticeID: " & currentNoticeId & vbCrLf, "") & _
"Error " & errorNumber & ": " & errorMessage & vbCrLf & _
"Review GeneratedTenantNotices before retrying. Existing files are not deleted.", _
vbCritical, "Tenant notice 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("NoticeID", "TenantName", "PropertyAddress", _
"Jurisdiction", "NoticeType", "DeliveryMethod", _
"NoticeDateDisplay", "ComplianceDeadlineDisplay", "NoticeBody", "IssuedBy", _
"OutputFormat", "NoticeStatus", "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 Function ReadRequiredMultilineText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
Dim position As Long
Dim characterCode As Long
Dim meaningfulText 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
ReadRequiredMultilineText = CStr(rawValue)
If Len(ReadRequiredMultilineText) = 0 Then
Err.Raise vbObjectError + 3323, , _
fieldName & " is required in row " & rowNumber & "."
End If
meaningfulText = Replace(ReadRequiredMultilineText, vbCr, vbNullString)
meaningfulText = Replace(meaningfulText, vbLf, vbNullString)
If Len(Trim$(meaningfulText)) = 0 Then
Err.Raise vbObjectError + 3323, , _
fieldName & " must contain meaningful text in row " & _
rowNumber & "."
End If
ValidateOuterSpaces ReadRequiredMultilineText, fieldName, rowNumber
For position = 1 To Len(ReadRequiredMultilineText)
characterCode = AscW(Mid$(ReadRequiredMultilineText, position, 1))
If characterCode >= 0 And characterCode < 32 And _
characterCode <> 10 And characterCode <> 13 Then
Err.Raise vbObjectError + 3324, , _
fieldName & " contains an unsupported control character in row " & _
rowNumber & "."
End If
Next position
End Function
Private Function NormalizeMultilineText(ByVal valueText As String) As String
NormalizeMultilineText = Replace(valueText, vbCrLf, vbLf)
NormalizeMultilineText = Replace(NormalizeMultilineText, vbCr, vbLf)
NormalizeMultilineText = Replace(NormalizeMultilineText, vbLf, vbCr)
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 ValidateNoticeStatus(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, , _
"NoticeStatus 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 TenantNoticeTemplate.docx."
End If
If doc.Revisions.Count > 0 Then
Err.Raise vbObjectError + 3332, , _
"Accept or reject all tracked revisions in the tenant notice template."
End If
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 3332, , _
"Remove all comments from the tenant notice template."
End If
names = Array("NoticeID", "TenantName", "PropertyAddress", _
"Jurisdiction", "NoticeType", "DeliveryMethod", _
"NoticeDateDisplay", "ComplianceDeadlineDisplay", _
"NoticeBody", "IssuedBy")
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 tenant notice 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, , _
"Tenant notice template is no longer available: " & filePath
End If
If CDbl(FileLen(filePath)) <> expectedSize Or _
FileDateTime(filePath) <> expectedModified Then
Err.Raise vbObjectError + 3341, , _
"Tenant notice 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_NOTICE_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_NOTICE_STATUS).Value2 = STATUS_READY
On Error GoTo 0
Err.Raise vbObjectError + 3342, "SaveDoneStatus", _
"The tenant notice 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, authorize, serve, and record each notice separately
Tenant and premises
Match NoticeID, tenant name, property address, unit reference, notice type, and intended recipient.
Grounds and wording
Verify every fact, amount, event, clause, required disclosure, attachment reference, and jurisdiction-specific statement.
Dates and deadline
Independently confirm the notice date, triggering event, deadline calculation, weekends, holidays, and service-related timing.
Authority and signature
Confirm the issuer, reviewer, signing method, and any owner, manager, agent, witness, or notarization requirement.
DOCX and PDF
Open every requested file. Check all pages, layout, headers, footers, metadata, blank fields, and rendering differences.
Delivery evidence
Record the actual method, server or sender, addresses, tracking, timestamps, returned mail, receipt, affidavit, and retention separately.
Know what the free example does not guarantee
No legal determination
The macro does not decide whether the notice type, grounds, wording, or deadline is valid.
No proof of service
DeliveryMethod is text in the document, not evidence that delivery occurred or satisfied local requirements.
No deadline engine
ComplianceDeadlineDisplay is supplied text and is not calculated or verified by legal rules.
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 case action
Email, mailing, posting, service, tracking, escalation, filing, and enforcement remain separate workflows.
Use Factory for standardized tenant notice batches
DocxForge Pro Factory maps many reviewed Excel rows to one standard Word template and creates a separate output for each row. That is the tenant-notice pattern described here.
Use it when every READY row follows the same released notice template. If one tenancy case needs several different documents from different templates, use Contractor instead.


Frequently asked questions
Is this a Factory workflow?
Yes. Each READY row is one notice, all rows use one approved TenantNoticeTemplate.docx, and every row produces its own DOCX and/or PDF.
Can HOLD and DONE rows be incomplete?
Yes. They still need an exact, unique NoticeID and a valid NoticeStatus. If OutputName is present, it must be safe and unique. The complete field contract applies only to READY rows.
Can NoticeBody contain several paragraphs?
Yes. Store it as text with ordinary line breaks. The macro normalizes CRLF, LF, and CR for Word and rejects an empty narrative made only of spaces or line breaks.
Does DONE prove that the notice was delivered?
No. DONE confirms that requested files exist, are nonempty, and the workbook status was saved. DeliveryMethod is planned or recorded text; proof of service and delivery evidence require a separate controlled record.
Does the VBA decide whether a notice is legally valid?
No. The template, grounds, wording, dates, deadline, method, authorization, and service requirements must be reviewed for the applicable jurisdiction and situation.
When should Contractor be used instead?
Use Contractor when one tenancy case needs several different documents from different templates, such as a notice, cover letter, service declaration, and internal checklist. Use Factory for many notice rows using one standard template.