Mail Merge with Images vs Template-Based Generation with Photo Folders
Compare two valid image workflows without treating either as magic. Word Mail Merge can resolve record-specific image fields. A template-based generator can combine a controlled photo folder with one fresh template per row and embed each image before DOCX/PDF export.
Quick answer: choose by path ownership and output control
Choose Mail Merge when Word fields are already the approved document model and full paths can remain stable. Choose template-based generation when the project should travel as one workbook, one template, one photo folder, and deterministic outputs.
Choose Mail Merge
Use it for a familiar Word merge with a controlled data source, correctly constructed fields, stable paths, modest image rules, and an operator who can verify field updates before PDF export.
Choose a photo-folder generator
Use it when Excel should store file names only, every photo must pass preflight, images must be embedded into separate documents, and output names or recovery rules need explicit control.
Pilot both approaches with the same six records: normal photo, missing photo, wide photo, tall photo, duplicate output name, and final PDF.
Define the two workflows before comparing them
Word Mail Merge with images
Word connects to a data source and executes a merge. Image workflows typically combine a record value with an INCLUDEPICTURE field. Field construction, quoting, path escaping, record selection, updating, and final embedding all matter.
Template-based photo-folder generation
Code or a dedicated tool reads a row, resolves PhotoFile inside one approved folder, opens a fresh standard template, inserts the photo at a known destination, and saves that record as separate DOCX/PDF files.
Mail Merge delegates record expansion to Word fields. Template-based generation makes the row loop, file lookup, insertion, naming, and save sequence explicit.
Official references: Microsoft’s Word field-code list includes INCLUDEPICTURE, and MailMerge.Execute runs a configured merge.
Compare the six decisions that affect real output
Path storage
Mail Merge may carry a full or constructed path in fields. The folder model stores a file name and joins it to one project-relative root.
Portability
Machine-specific paths require remapping. A workbook, template, Photos folder, and output folder kept together are easier to move as one project.
Missing-photo behavior
Either workflow can fail or show stale content if validation is weak. A dedicated preflight can stop before Word when any required file is absent.
Image placement
Mail Merge depends on field results and the surrounding layout. Direct insertion uses a named bookmark and an explicit maximum width and height.
Final embedding
Linked or field-based results need careful finalization. Direct insertion can use LinkToFile:=False so each generated DOCX contains its own copy.
Recovery and naming
Mail Merge excels at Word’s built-in record flow. A row loop makes per-record names, collision checks, DONE status, checkpoints, and resume rules easier to expose.
Use Mail Merge only with an explicit image-field contract
Create real Word fields
Field braces must be inserted with Word’s field controls, not typed as ordinary characters. Inspect field codes before trusting the visible result.
Quote and test paths
Test spaces, apostrophes, long names, local folders, and the exact path format produced by the data source.
Update every record
Verify that each merged record resolves its own image. A successful merge does not by itself prove that photos differ correctly across recipients.
Finalize before PDF
Review the merged document, update the necessary fields, confirm the images, and test PDF pagination. Do not assume print preview repairs all field results.
{ INCLUDEPICTURE "{ MERGEFIELD PhotoPath }" }
Create both pairs of field braces with Ctrl+F9 in Word Desktop; typing ordinary braces does not create fields. Use Alt+F9 to switch between field codes and results. Merge a small test set, select the resulting field, press F9, and confirm that every record displays its own photo before PDF export.
Build the photo-folder workflow as a portable project
Keep the workbook, template, folders, and rules together. Excel stores final text values and a file name, not an unrestricted path.
Workbook
Save a writable .xlsm file with a Data sheet and explicit READY/DONE progress fields.
Template
Place PhotoRecordTemplate.docx inside Templates\ and open a fresh read-only instance for each record.
Photo folder
Place approved JPG, JPEG, PNG, BMP, or GIF files inside Photos\. Store only PhotoFile, such as client-0042.jpg, in Excel.
Output folder
Use an empty local GeneratedDocuments\ folder. Existing DOCX or PDF names block the run.
Allowed file names
Reject separators, drive letters, wildcards, and parent-folder references so a row cannot escape the approved photo root.
Image quality contract
Approve source pixel dimensions and compression separately. The macro preserves aspect ratio and fits a box; it does not manufacture missing detail.
DocumentID | ClientName | DocumentDateDisplay | AmountDisplay | PhotoFile | OutputName | Status | GenerationStatus | GeneratedAt
Give the Word template one tested photo destination
Four text bookmarks
Create DocumentID, ClientName, DocumentDateDisplay, and AmountDisplay around uniformly styled placeholder text.
One photo bookmark
Create RecordPhoto around one inline placeholder image. Its tested dimensions become the maximum insertion box.
No nested dynamic objects
The example rejects fields or content controls inside mapped ranges. Floating images need a different layout and insertion routine.
Maximum-content test
Approve long text, wide and tall photos, page breaks, headers, footers, tables, DOCX reopening, and final PDF output.
Official references: Bookmarks.Exists and Bookmarks.Add.
Free VBA example: generate from one controlled photo folder
This is the template-based side of the comparison, not a Mail Merge macro. It preflights every eligible row, caches the first 100 validated records, embeds one row-specific photo, writes temporary DOCX/PDF files, and records progress every 20 completed documents.
Full preflight
Required values, unique IDs, cleaned output names, photo-file safety, photo existence, and existing outputs are checked before Word opens.
Embedded photo
InlineShapes.AddPicture uses the verified local path with linking disabled, preserves aspect ratio, and fits the template’s tested box.
Fresh template per row
Each record starts from a new read-only template instance, preventing one row’s text or photo from leaking into the next.
Controlled recovery
Temporary names, second collision checks, DONE status, timestamps, and workbook checkpoints make partial results easier to reconcile.
Option Explicit
Private Const DATA_SHEET As String = "Data"
Private Const TEMPLATE_RELATIVE_PATH As String = "\Templates\PhotoRecordTemplate.docx"
Private Const OUTPUT_RELATIVE_FOLDER As String = "\GeneratedDocuments"
Private Const PHOTO_RELATIVE_FOLDER As String = "\Photos"
Private Const FIRST_DATA_ROW As Long = 2
Private Const BATCH_LIMIT As Long = 100
Private Const CHECKPOINT_EVERY As Long = 20
Private Const EXPORT_PDF As Boolean = True
Private Const HEADER_DOCUMENT_ID As String = "DocumentID"
Private Const HEADER_CLIENT_NAME As String = "ClientName"
Private Const HEADER_DATE_DISPLAY As String = "DocumentDateDisplay"
Private Const HEADER_AMOUNT_DISPLAY As String = "AmountDisplay"
Private Const HEADER_PHOTO_FILE As String = "PhotoFile"
Private Const HEADER_OUTPUT_NAME As String = "OutputName"
Private Const HEADER_STATUS As String = "Status"
Private Const HEADER_GENERATION_STATUS As String = "GenerationStatus"
Private Const HEADER_GENERATED_AT As String = "GeneratedAt"
Private Const READY_STATUS As String = "READY"
Private Const DONE_STATUS As String = "DONE"
Private Const BOOKMARK_DOCUMENT_ID As String = "DocumentID"
Private Const BOOKMARK_CLIENT_NAME As String = "ClientName"
Private Const BOOKMARK_DATE_DISPLAY As String = "DocumentDateDisplay"
Private Const BOOKMARK_AMOUNT_DISPLAY As String = "AmountDisplay"
Private Const BOOKMARK_PHOTO As String = "RecordPhoto"
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_COLLAPSE_START As Long = 1
Private Const WD_UNDEFINED As Long = 9999999
Private Const MSO_TRUE As Long = -1
Private Const WORD_ALERTS_NONE As Long = 0
Public Sub GenerateControlledDocumentBatch()
Dim wb As Workbook
Dim ws As Worksheet
Dim documentIdColumn As Long
Dim clientNameColumn As Long
Dim dateDisplayColumn As Long
Dim amountDisplayColumn As Long
Dim photoFileColumn As Long
Dim outputNameColumn As Long
Dim statusColumn As Long
Dim generationStatusColumn As Long
Dim generatedAtColumn As Long
Dim lastRow As Long
Dim rowNumber As Long
Dim sourceRow As Long
Dim batchRows(1 To BATCH_LIMIT) As Long
Dim batchDocumentIds(1 To BATCH_LIMIT) As String
Dim batchClientNames(1 To BATCH_LIMIT) As String
Dim batchDateDisplays(1 To BATCH_LIMIT) As String
Dim batchAmountDisplays(1 To BATCH_LIMIT) As String
Dim batchPhotoPaths(1 To BATCH_LIMIT) As String
Dim batchSafeOutputNames(1 To BATCH_LIMIT) As String
Dim batchCount As Long
Dim eligibleCount As Long
Dim completedCount As Long
Dim documentId As String
Dim clientName As String
Dim dateDisplay As String
Dim amountDisplay As String
Dim photoFile As String
Dim photoPath As String
Dim outputName As String
Dim safeOutputName As String
Dim statusText As String
Dim generationStatusText As String
Dim templatePath As String
Dim outputFolder As String
Dim photoFolder As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim runToken As String
Dim seenDocumentIds As Object
Dim seenOutputNames As Object
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
Dim errorNumber As Long
Dim errorMessage As String
Dim checkpointSaveErrorNumber As Long
Dim checkpointSaveErrorMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 2500, , _
"Save the workbook before running the batch."
End If
If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 2501, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 2502, , _
"The workbook is read-only. Open a writable copy before generating files."
End If
Set ws = RequireWorksheet(wb, DATA_SHEET)
If ws.ProtectContents Then
Err.Raise vbObjectError + 2503, , _
"The Data sheet is protected. Unprotect it before generating files."
End If
documentIdColumn = RequireHeaderColumn(ws, HEADER_DOCUMENT_ID)
clientNameColumn = RequireHeaderColumn(ws, HEADER_CLIENT_NAME)
dateDisplayColumn = RequireHeaderColumn(ws, HEADER_DATE_DISPLAY)
amountDisplayColumn = RequireHeaderColumn(ws, HEADER_AMOUNT_DISPLAY)
photoFileColumn = RequireHeaderColumn(ws, HEADER_PHOTO_FILE)
outputNameColumn = RequireHeaderColumn(ws, HEADER_OUTPUT_NAME)
statusColumn = RequireHeaderColumn(ws, HEADER_STATUS)
generationStatusColumn = RequireHeaderColumn(ws, HEADER_GENERATION_STATUS)
generatedAtColumn = RequireHeaderColumn(ws, HEADER_GENERATED_AT)
templatePath = wb.Path & TEMPLATE_RELATIVE_PATH
outputFolder = wb.Path & OUTPUT_RELATIVE_FOLDER
photoFolder = wb.Path & PHOTO_RELATIVE_FOLDER
RequireFile templatePath, "Word template"
RequireFolder outputFolder, "output folder"
RequireFolder photoFolder, "photo folder"
lastRow = LastUsedRowInColumns(ws, Array( _
documentIdColumn, clientNameColumn, dateDisplayColumn, _
amountDisplayColumn, photoFileColumn, outputNameColumn, _
statusColumn, generationStatusColumn, generatedAtColumn))
If lastRow < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 2504, , _
"No data rows were found on the " & DATA_SHEET & " sheet."
End If
Set seenDocumentIds = CreateObject("Scripting.Dictionary")
seenDocumentIds.CompareMode = vbTextCompare
Set seenOutputNames = CreateObject("Scripting.Dictionary")
seenOutputNames.CompareMode = vbTextCompare
For rowNumber = FIRST_DATA_ROW To lastRow
statusText = UCase$(Trim$(CStr(ws.Cells(rowNumber, statusColumn).Value2)))
generationStatusText = UCase$(Trim$(CStr(ws.Cells(rowNumber, generationStatusColumn).Value2)))
If statusText = READY_STATUS And generationStatusText <> DONE_STATUS Then
documentId = RequireCellText(ws, rowNumber, documentIdColumn, HEADER_DOCUMENT_ID)
clientName = RequireCellText(ws, rowNumber, clientNameColumn, HEADER_CLIENT_NAME)
dateDisplay = RequireDisplayText(ws, rowNumber, dateDisplayColumn, HEADER_DATE_DISPLAY)
amountDisplay = RequireDisplayText(ws, rowNumber, amountDisplayColumn, HEADER_AMOUNT_DISPLAY)
photoFile = RequireCellText(ws, rowNumber, photoFileColumn, HEADER_PHOTO_FILE)
outputName = RequireCellText(ws, rowNumber, outputNameColumn, HEADER_OUTPUT_NAME)
RequireSupportedPhotoExtension photoFile, rowNumber
ValidateReplacementText documentId, BOOKMARK_DOCUMENT_ID
ValidateReplacementText clientName, BOOKMARK_CLIENT_NAME
ValidateReplacementText dateDisplay, BOOKMARK_DATE_DISPLAY
ValidateReplacementText amountDisplay, BOOKMARK_AMOUNT_DISPLAY
If seenDocumentIds.Exists(documentId) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The identifier duplicates READY row " & _
seenDocumentIds(documentId) & "."
End If
seenDocumentIds.Add documentId, rowNumber
RejectOutputExtension outputName, rowNumber
safeOutputName = CleanFileName(outputName)
If Len(safeOutputName) = 0 Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The value does not contain a usable Windows file name."
End If
If seenOutputNames.Exists(safeOutputName) Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The cleaned name duplicates READY row " & _
seenOutputNames(safeOutputName) & "."
End If
seenOutputNames.Add safeOutputName, rowNumber
finalDocxPath = outputFolder & "\" & safeOutputName & ".docx"
finalPdfPath = outputFolder & "\" & safeOutputName & ".pdf"
If Len(finalDocxPath) > 218 Or Len(finalPdfPath) > 218 Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The output path is too long for this example workflow."
End If
If FileExists(finalDocxPath) Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The DOCX output already exists: " & finalDocxPath
End If
If EXPORT_PDF And FileExists(finalPdfPath) Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The PDF output already exists: " & finalPdfPath
End If
If Len(photoFile) > 0 Then
photoPath = ResolvePhotoFile(photoFolder, photoFile, rowNumber)
RequireFile photoPath, "photo for row " & rowNumber
Else
photoPath = vbNullString
End If
eligibleCount = eligibleCount + 1
If batchCount < BATCH_LIMIT Then
batchCount = batchCount + 1
batchRows(batchCount) = rowNumber
batchDocumentIds(batchCount) = documentId
batchClientNames(batchCount) = clientName
batchDateDisplays(batchCount) = dateDisplay
batchAmountDisplays(batchCount) = amountDisplay
batchPhotoPaths(batchCount) = photoPath
batchSafeOutputNames(batchCount) = safeOutputName
End If
End If
Next rowNumber
If eligibleCount = 0 Then
MsgBox "No rows are eligible. Use Status = " & READY_STATUS & _
" and leave GenerationStatus different from " & DONE_STATUS & ".", _
vbInformation, "Nothing to generate"
Exit Sub
End If
wb.Save
Set fso = CreateObject("Scripting.FileSystemObject")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = WORD_ALERTS_NONE
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
ValidateTemplate doc
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 1000), "00000000")
For rowNumber = 1 To batchCount
sourceRow = batchRows(rowNumber)
documentId = batchDocumentIds(rowNumber)
clientName = batchClientNames(rowNumber)
dateDisplay = batchDateDisplays(rowNumber)
amountDisplay = batchAmountDisplays(rowNumber)
photoPath = batchPhotoPaths(rowNumber)
safeOutputName = batchSafeOutputNames(rowNumber)
finalDocxPath = outputFolder & "\" & safeOutputName & ".docx"
finalPdfPath = outputFolder & "\" & safeOutputName & ".pdf"
tempDocxPath = outputFolder & "\.__batch_" & runToken & _
"_" & Format$(sourceRow, "000000") & ".docx"
tempPdfPath = outputFolder & "\.__batch_" & runToken & _
"_" & Format$(sourceRow, "000000") & ".pdf"
If FileExists(tempDocxPath) Or FileExists(tempPdfPath) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"A temporary file collision was detected. Run the batch again."
End If
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
WriteTextBookmark doc, BOOKMARK_DOCUMENT_ID, documentId
WriteTextBookmark doc, BOOKMARK_CLIENT_NAME, clientName
WriteTextBookmark doc, BOOKMARK_DATE_DISPLAY, dateDisplay
WriteTextBookmark doc, BOOKMARK_AMOUNT_DISPLAY, amountDisplay
WritePictureBookmark doc, BOOKMARK_PHOTO, photoPath, False
doc.SaveAs2 tempDocxPath, WD_FORMAT_XML_DOCUMENT, False, vbNullString, False
If Not FileExists(tempDocxPath) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"Word did not create the temporary DOCX file."
End If
If EXPORT_PDF Then
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_FORMAT_PDF
If Not FileExists(tempPdfPath) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"Word did not create the temporary PDF file."
End If
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If FileExists(finalDocxPath) Or _
(EXPORT_PDF And FileExists(finalPdfPath)) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"An output appeared after preflight. No existing file was replaced."
End If
fso.MoveFile tempDocxPath, finalDocxPath
tempDocxPath = vbNullString
If EXPORT_PDF Then
fso.MoveFile tempPdfPath, finalPdfPath
tempPdfPath = vbNullString
End If
ws.Cells(sourceRow, generationStatusColumn).Value2 = DONE_STATUS
ws.Cells(sourceRow, generatedAtColumn).Value = Now
completedCount = completedCount + 1
If completedCount Mod CHECKPOINT_EVERY = 0 Then wb.Save
Next rowNumber
wb.Save
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
MsgBox completedCount & " documents completed in this run." & vbCrLf & _
CStr(eligibleCount - completedCount) & _
" eligible rows remain for the next controlled batch.", _
vbInformation, "Batch complete"
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.Quit WD_DO_NOT_SAVE_CHANGES
SafeDeleteTempFile tempDocxPath
SafeDeleteTempFile tempPdfPath
If Not wb Is Nothing Then
Err.Clear
wb.Save
checkpointSaveErrorNumber = Err.Number
checkpointSaveErrorMessage = Err.Description
Err.Clear
End If
On Error GoTo 0
If checkpointSaveErrorNumber <> 0 Then
errorMessage = errorMessage & vbCrLf & _
"Workbook checkpoint could not be saved. " & _
"Recent DONE statuses may be missing." & vbCrLf & _
"Save error " & checkpointSaveErrorNumber & ": " & _
checkpointSaveErrorMessage
End If
MsgBox completedCount & " documents were completed before the stop." & vbCrLf & _
"Review the current row and output folder before resuming." & vbCrLf & _
"Error " & errorNumber & ": " & errorMessage, _
vbCritical, "Batch stopped"
End Sub
Private Sub ValidateTemplate(ByVal doc As Object)
ValidateTextBookmark doc, BOOKMARK_DOCUMENT_ID
ValidateTextBookmark doc, BOOKMARK_CLIENT_NAME
ValidateTextBookmark doc, BOOKMARK_DATE_DISPLAY
ValidateTextBookmark doc, BOOKMARK_AMOUNT_DISPLAY
ValidatePictureBookmark doc, BOOKMARK_PHOTO
End Sub
Private Sub ValidateTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2510, , _
"Required Word bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.Start = targetRange.End Then
Err.Raise vbObjectError + 2511, , _
"Text bookmark must contain a styled placeholder: " & bookmarkName
End If
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2512, , _
"Text bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2513, , _
"Text bookmark contains a Word field or content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 2519, , _
"Text bookmark contains an inline shape: " & bookmarkName
End If
If HasMixedCharacterFormatting(targetRange) Then
Err.Raise vbObjectError + 2514, , _
"Text bookmark uses mixed character formatting: " & bookmarkName
End If
End Sub
Private Sub ValidatePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2515, , _
"Required picture bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2516, , _
"Picture bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2517, , _
"Picture bookmark contains a Word field or content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 1 Then
Err.Raise vbObjectError + 2518, , _
"Picture bookmark may contain one inline placeholder image at most: " & bookmarkName
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim savedFont As Object
Dim savedStyle As Variant
ValidateReplacementText replacementText, bookmarkName
ValidateTextBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
Set savedFont = targetRange.Font.Duplicate
savedStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = savedStyle
targetRange.Font = savedFont
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2520, , _
"Bookmark was not restored: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.Text <> replacementText Then
Err.Raise vbObjectError + 2521, , _
"Bookmark text did not verify: " & bookmarkName
End If
End Sub
Private Sub ValidateReplacementText(ByVal replacementText As String, _
ByVal bookmarkName As String)
If InStr(1, replacementText, vbTab, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbLf, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(11), vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(12), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 2522, , _
"Single-line replacement contains a tab, line break, or page break: " & _
bookmarkName
End If
End Sub
Private Sub WritePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal picturePath As String, _
ByVal allowBlank As Boolean)
Dim targetRange As Object
Dim picture As Object
Dim startPosition As Long
Dim maxWidth As Single
Dim maxHeight As Single
If Len(picturePath) = 0 And Not allowBlank Then
Err.Raise vbObjectError + 2530, , _
"A required picture is blank: " & bookmarkName
End If
ValidatePictureBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.InlineShapes.Count = 1 Then
maxWidth = targetRange.InlineShapes(1).Width
maxHeight = targetRange.InlineShapes(1).Height
Else
maxWidth = 216!
maxHeight = 144!
End If
startPosition = targetRange.Start
targetRange.Delete
targetRange.SetRange startPosition, startPosition
targetRange.Collapse WD_COLLAPSE_START
If Len(picturePath) = 0 Then
doc.Bookmarks.Add bookmarkName, targetRange
Exit Sub
End If
RequireFile picturePath, "photo for bookmark " & bookmarkName
Set picture = doc.InlineShapes.AddPicture( _
picturePath, False, True, targetRange)
picture.LockAspectRatio = MSO_TRUE
If picture.Width > maxWidth Then picture.Width = maxWidth
If picture.Height > maxHeight Then picture.Height = maxHeight
doc.Bookmarks.Add bookmarkName, picture.Range
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2531, , _
"Picture bookmark was not restored: " & bookmarkName
End If
End Sub
Private Function HasMixedCharacterFormatting(ByVal targetRange As Object) As Boolean
With targetRange.Font
HasMixedCharacterFormatting = _
(.Bold = WD_UNDEFINED) Or _
(.Italic = WD_UNDEFINED) Or _
(.Underline = WD_UNDEFINED) Or _
(.Size = WD_UNDEFINED) Or _
(.Color = WD_UNDEFINED)
End With
End Function
Private Function RangeContainsParagraphOrCellMark(ByVal targetRange As Object) As Boolean
Dim rangeText As String
rangeText = targetRange.Text
RangeContainsParagraphOrCellMark = _
(InStr(1, rangeText, vbCr, vbBinaryCompare) > 0) Or _
(InStr(1, rangeText, Chr$(7), vbBinaryCompare) > 0)
End Function
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal worksheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(worksheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 2540, , _
"Required worksheet is missing: " & worksheetName
End If
End Function
Private Function RequireHeaderColumn(ByVal ws As Worksheet, _
ByVal headerText As String) As Long
Dim lastHeaderColumn As Long
Dim columnNumber As Long
Dim matchCount As Long
lastHeaderColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
For columnNumber = 1 To lastHeaderColumn
If StrComp(Trim$(CStr(ws.Cells(1, columnNumber).Value2)), _
headerText, vbTextCompare) = 0 Then
RequireHeaderColumn = columnNumber
matchCount = matchCount + 1
End If
Next columnNumber
If matchCount = 0 Then
Err.Raise vbObjectError + 2541, , _
"Required header missing from row 1: " & headerText
ElseIf matchCount > 1 Then
Err.Raise vbObjectError + 2542, , _
"Duplicate header in row 1: " & headerText
End If
End Function
Private Function LastUsedRowInColumns(ByVal ws As Worksheet, _
ByVal columnsToCheck As Variant) As Long
Dim item As Variant
Dim candidateRow As Long
For Each item In columnsToCheck
candidateRow = ws.Cells(ws.Rows.Count, CLng(item)).End(xlUp).Row
If candidateRow > LastUsedRowInColumns Then
LastUsedRowInColumns = candidateRow
End If
Next item
End Function
Private Function ReadCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
ReadCellText = Trim$(CStr(cellValue))
End Function
Private Function RequireCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
RequireCellText = ReadCellText(ws, rowNumber, columnNumber, headerText)
If Len(RequireCellText) = 0 Then
RaiseRowError rowNumber, headerText, "A value is required for READY rows."
End If
End Function
Private Function RequireDisplayText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
If VarType(cellValue) <> vbString Then
RaiseRowError rowNumber, headerText, _
"Store the final document-ready value as text, not a formatted number or date."
End If
RequireDisplayText = Trim$(CStr(cellValue))
If Len(RequireDisplayText) = 0 Then
RaiseRowError rowNumber, headerText, "A display value is required."
End If
End Function
Private Function ResolvePhotoFile(ByVal photoFolder As String, _
ByVal fileNameOnly As String, _
ByVal rowNumber As Long) As String
Dim cleanedName As String
cleanedName = Trim$(fileNameOnly)
If InStr(cleanedName, "\") > 0 Or InStr(cleanedName, "/") > 0 Or _
InStr(cleanedName, ":") > 0 Or InStr(cleanedName, "*") > 0 Or _
InStr(cleanedName, "?") > 0 Or InStr(cleanedName, "<") > 0 Or _
InStr(cleanedName, ">") > 0 Or InStr(cleanedName, "|") > 0 Or _
InStr(cleanedName, Chr$(34)) > 0 Or cleanedName = "." Or cleanedName = ".." Then
RaiseRowError rowNumber, HEADER_PHOTO_FILE, _
"Use a file name only. Store the photo inside the Photos folder."
End If
ResolvePhotoFile = photoFolder & "\" & cleanedName
End Function
Private Sub RequireSupportedPhotoExtension(ByVal fileNameOnly As String, _
ByVal rowNumber As Long)
Dim dotPosition As Long
Dim extensionText As String
dotPosition = InStrRev(fileNameOnly, ".")
If dotPosition = 0 Then
RaiseRowError rowNumber, HEADER_PHOTO_FILE, _
"The photo file needs a supported extension."
End If
extensionText = LCase$(Mid$(fileNameOnly, dotPosition))
Select Case extensionText
Case ".jpg", ".jpeg", ".png", ".bmp", ".gif"
'Supported by this tested example contract.
Case Else
RaiseRowError rowNumber, HEADER_PHOTO_FILE, _
"Use a tested JPG, JPEG, PNG, BMP, or GIF photo."
End Select
End Sub
Private Sub RejectOutputExtension(ByVal outputName As String, _
ByVal rowNumber As Long)
Dim lowerName As String
lowerName = LCase$(Trim$(outputName))
If Right$(lowerName, 5) = ".docx" Or Right$(lowerName, 4) = ".pdf" Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"Enter the output name without a .docx or .pdf extension."
End If
End Sub
Private Function CleanFileName(ByVal rawName As String) As String
Dim result As String
Dim invalidCharacters As Variant
Dim item As Variant
Dim characterCode As Long
Dim position As Long
result = Trim$(rawName)
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", "\", "|", "?", "*")
For Each item In invalidCharacters
result = Replace(result, CStr(item), "_")
Next item
For position = 1 To Len(result)
characterCode = AscW(Mid$(result, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Mid$(result, position, 1) = "_"
End If
Next position
result = Left$(result, 120)
Do While Len(result) > 0 And _
(Right$(result, 1) = "." Or Right$(result, 1) = " ")
result = Left$(result, Len(result) - 1)
Loop
If IsReservedWindowsName(result) Then result = "_" & result
CleanFileName = result
End Function
Private Function IsReservedWindowsName(ByVal fileName As String) As Boolean
Dim baseName As String
Dim dotPosition As Long
Dim numberValue As Long
baseName = UCase$(fileName)
dotPosition = InStr(1, baseName, ".", vbBinaryCompare)
If dotPosition > 0 Then baseName = Left$(baseName, dotPosition - 1)
Select Case baseName
Case "CON", "PRN", "AUX", "NUL"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(baseName, 3) = "COM" Or Left$(baseName, 3) = "LPT" Then
If Len(baseName) = 4 And IsNumeric(Right$(baseName, 1)) Then
numberValue = CLng(Right$(baseName, 1))
IsReservedWindowsName = (numberValue >= 1 And numberValue <= 9)
End If
End If
End Function
Private Sub RequireFile(ByVal filePath As String, ByVal labelText As String)
If Not FileExists(filePath) Then
Err.Raise vbObjectError + 2550, , _
"Missing " & labelText & ": " & filePath
End If
If FileLen(filePath) = 0 Then
Err.Raise vbObjectError + 2551, , _
"The " & labelText & " is empty: " & filePath
End If
End Sub
Private Sub RequireFolder(ByVal folderPath As String, ByVal labelText As String)
If Not FolderExists(folderPath) Then
Err.Raise vbObjectError + 2552, , _
"Missing " & labelText & ": " & folderPath
End If
End Sub
Private Function FileExists(ByVal filePath As String) As Boolean
On Error Resume Next
FileExists = (Len(Dir$(filePath, vbNormal Or vbReadOnly Or vbHidden Or vbSystem)) > 0)
On Error GoTo 0
End Function
Private Function FolderExists(ByVal folderPath As String) As Boolean
Dim attributes As Long
On Error Resume Next
attributes = GetAttr(folderPath)
FolderExists = (Err.Number = 0) And ((attributes And vbDirectory) = vbDirectory)
Err.Clear
On Error GoTo 0
End Function
Private Sub SafeDeleteTempFile(ByVal filePath As String)
If Len(filePath) = 0 Then Exit Sub
If InStr(1, filePath, "\.__batch_", vbTextCompare) = 0 Then Exit Sub
If FileExists(filePath) Then Kill filePath
End Sub
Private Sub RaiseRowError(ByVal rowNumber As Long, _
ByVal fieldName As String, _
ByVal messageText As String)
Err.Raise vbObjectError + 2560, , _
"Row " & rowNumber & ", " & fieldName & ": " & messageText
End SubOfficial references: InlineShapes.AddPicture, SaveAs2, ExportAsFixedFormat, and Application.DisplayAlerts.
Run the same six-record pilot for a fair comparison
1. Normal landscape photo
Confirm record identity, correct image, readable text, filename, DOCX, and PDF.
2. Tall portrait photo
Verify aspect ratio, maximum height, surrounding spacing, and page breaks.
3. Missing photo
Confirm the workflow stops or reports the exact record instead of silently reusing an earlier image.
4. Similar file names
Use two records whose names differ by one character and confirm that the correct photo reaches each output.
5. Duplicate output name
Confirm preflight prevents overwrite even when the image references are valid.
6. Move the project
Copy the project folder to another approved local location and document exactly which paths or settings require adjustment.
Record setup time, failed rows, correction time, DOCX/PDF consistency, handoff steps, and the number of machine-specific paths.
Use Factory when the folder workflow wins the pilot
DocxForge Pro Factory fits this relationship: many independent Excel rows, one standard Word template, and one separate output per row.
Correct module
Factory is the direct comparison for row-based image generation through one standard template.
Review the rows
Confirm text values, photo file names, output names, and the records selected for generation.
Review the template
Confirm the Word template, image tags, tested photo box, and the required output formats.
Where it does not fit
Contractor is for one case that creates several different documents from different Word templates, not one document per independent row.


Frequently asked questions
Is Mail Merge with INCLUDEPICTURE always less reliable than a photo-folder workflow?
No. Mail Merge can be reliable when the data source, field codes, paths, and image sizes are tightly controlled. The photo-folder workflow becomes easier to validate and hand off when you want Excel to store file names rather than machine-specific full paths.
Should Excel store a full photo path or only a file name?
Use the value required by the chosen workflow. Classic INCLUDEPICTURE commonly resolves a path supplied through fields. The template-based example stores only PhotoFile and combines it with one controlled local Photos folder.
Does a photo folder automatically fix image size or DPI?
No. Folder organization only controls file location. The template or code must still define a tested image box, preserve aspect ratio, avoid enlarging unsuitable source images, and verify the resulting DOCX and PDF.
Why does the VBA example insert the photo instead of running Mail Merge?
The example demonstrates the alternative being compared: one Excel row, one fresh Word template, one embedded photo, and separate DOCX/PDF output. It deliberately avoids combining two different generation models in one macro.
Can the photo-folder workflow use a different image for every row?
Yes. Each READY row supplies a PhotoFile value. Different rows may also reuse the same approved image. Preflight verifies that the file name is safe, the local file exists and is nonempty, and the photo is embedded at the RecordPhoto bookmark.
Should I use Factory or Contractor for this workflow?
Use Factory when many independent Excel rows feed one standard Word template, as in this comparison. Contractor is for one case that creates several different documents from different Word templates.