How to Create Photo-Based Property Condition Reports

Sub GeneratePropertyConditionReports()
    Dim xlApp As Application
    Dim xlWb As Workbook
    Dim xlWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim photoPath As String
    Dim imgFullName As String
    Dim wordApp As Object ' Word.Application
    Dim doc As Object ' Word.Document
    Dim outputFolder As String
    Dim docFolder As String
    Dim pdfFolder As String
    Dim logFile As String
    Dim logHandle As Integer
    
    '--- Settings ------------------------------------------------------------
    Const TEMPLATE_PATH As String = "C:\Templates\ConditionReportTemplate.docx"
    Const PHOTO_FOLDER As String = "C:\InspectionPhotos"
    Const OUTPUT_ROOT As String = "C:\GeneratedReports"
    Const LOG_PATH As String = OUTPUT_ROOT & "\generation_log.txt"
    
    '--- Ensure output folders exist ------------------------------------------
    docFolder = OUTPUT_ROOT & "\WORD"
    pdfFolder = OUTPUT_ROOT & "\PDF"
    If Dir(OUTPUT_ROOT, vbDirectory) = "" Then MkDir OUTPUT_ROOT
    If Dir(docFolder, vbDirectory) = "" Then MkDir docFolder
    If Dir(pdfFolder, vbDirectory) = "" Then MkDir pdfFolder
    
    '--- Open log -----------------------------------------------------------
    logHandle = FreeFile
    Open LOG_PATH For Append As #logHandle
    Print #logHandle, "--- Generation started " & Now & " ---"
    
    '--- Reference the active workbook ---------------------------------------
    Set xlApp = Application
    Set xlWb = xlApp.ActiveWorkbook
    Set xlWs = xlWb.Sheets(1) ' assume first sheet holds data
    lastRow = xlWs.Cells(xlWs.Rows.Count, "A").End(xlUp).Row
    
    '--- Create a single Word application instance to reuse -----------------
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = False
    
    '--- Loop through each record -------------------------------------------
    For i = 2 To lastRow ' skip header row
        Dim unitID As String
        Dim address As String
        Dim photoFile As String
        unitID = CStr(xlWs.Cells(i, "A").Value)
        address = CStr(xlWs.Cells(i, "B").Value)
        photoFile = CStr(xlWs.Cells(i, "C").Value) ' exact filename or base name
        imgFullName = PHOTO_FOLDER & "\" & photoFile
        
        ' Verify photo exists
        If Dir(imgFullName) = "" Then
            Print #logHandle, "Row " & i & ": Missing image " & imgFullName
        End If
        
        ' Open the template for this record
        Set doc = wordApp.Documents.Open(TEMPLATE_PATH, ReadOnly:=False)
        
        ' Replace text placeholders safely
        If doc.Bookmarks.Exists("UnitID") Then
            doc.Bookmarks("UnitID").Range.Text = unitID
        End If
        If doc.Bookmarks.Exists("Address") Then
            doc.Bookmarks("Address").Range.Text = address
        End If
        
        ' Insert photo if it exists
        If Dir(imgFullName) <> "" Then
            If doc.Bookmarks.Exists("Photo1") Then
                Dim picShape As Object
                Set picShape = doc.InlineShapes.AddPicture(FileName:=imgFullName, LinkToFile:=False, SaveWithDocument:=True)
                picShape.LockAspectRatio = True
                picShape.Width = wordApp.InchesToPoints(4) ' uniform width of 4 inches
            End If
        End If
        
        ' Save DOCX
        Dim docPath As String
        docPath = docFolder & "\Report_" & unitID & ".docx"
        doc.SaveAs2 docPath, FileFormat:=12 ' wdFormatXMLDocument
        
        ' Export PDF
        Dim pdfPath As String
        pdfPath = pdfFolder & "\Report_" & unitID & ".pdf"
        doc.ExportAsFixedFormat OutputFileName:=pdfPath, ExportFormat:=17 ' wdExportFormatPDF
        
        ' Close the document for this record
        doc.Close False
        Set doc = Nothing
    Next i
    
    ' Clean up Word application
    wordApp.Quit
    Set wordApp = Nothing
    
    Print #logHandle, "--- Generation completed " & Now & " ---"
    Close #logHandle
    MsgBox "Report generation finished. Check the log at " & LOG_PATH, vbInformation
End Sub