如何通过VBA在批量Outlook邮件中插入Excel表头及对应行表格？

要实现这个需求，我们可以改用HTML格式邮件来生成带表头和当前行的表格，纯文本模式下很难规整展示表格结构。以下是修改后的完整代码：

Sub PLGBarcodeFile()
    Dim EApp As Object
    Set EApp = CreateObject("Outlook.Application")
    
    Dim EItem As Object
    Dim path As String
    'path = 你的附件路径
    
    Dim NameList As Range
    Set NameList = Range("A2", Range("A2").End(xlDown))
    
    Dim Name As Range
    Dim headerRow As Range
    Dim currentDataRow As Range
    Dim htmlTable As String
    
    Application.ScreenUpdating = False ' 关闭屏幕刷新提升运行效率
    
    ' 获取表头行（默认表头在第1行，自动识别最后一列）
    Set headerRow = Range("A1", Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
    
    For Each Name In NameList
        ' 获取当前数据行（自动识别该行最后一列）
        Set currentDataRow = Range(Name, Name.End(xlToRight))
        
        ' 拼接生成HTML表格
        htmlTable = "<table border='1' cellpadding='4' cellspacing='0' style='border-collapse:collapse;'>"
        ' 添加表头行
        htmlTable = htmlTable & "<tr>"
        Dim headerCell As Range
        For Each headerCell In headerRow
            htmlTable = htmlTable & "<th style='background-color:#f0f0f0;'>" & headerCell.Value & "</th>"
        Next headerCell
        htmlTable = htmlTable & "</tr>"
        ' 添加当前数据行
        htmlTable = htmlTable & "<tr>"
        Dim dataCell As Range
        For Each dataCell In currentDataRow
            htmlTable = htmlTable & "<td>" & dataCell.Value & "</td>"
        Next dataCell
        htmlTable = htmlTable & "</tr></table>"
        
        Set EItem = EApp.CreateItem(0)
        With EItem
            .To = Name.Offset(0, 8)
            .Subject = "Evaluate information regarding barcodes for " & Name.Offset(0, 1)
            '.Attachments.Add path
            .BodyFormat = olFormatHTML ' 设置邮件为HTML格式
            .HTMLBody = "<p>Dear " & Name.Offset(0, 9) & "</p>" & _
                        "<p>Standard Text<br>" & _
                        "Standard Text</p>" & _
                        "<p>Standard Text<br>" & _
                        "PStandard Text</p>" & _
                        "<p>Standard Text<br>" & _
                        "Standard Text<br>" & _
                        "Standard Text<br>" & _
                        "Standard Text<br>" & _
                        "Standard Text " & Name.Offset(0, 1) & "</p>" & _
                        "<p>" & htmlTable & "</p>" & _
                        "<p>Standard Text<br>" & _
                        "Standard Text<br>" & _
                        "Standard Text<br>" & _
                        "Standard Text</p>"
            .Display
        End With
    Next Name
    
    Application.ScreenUpdating = True
End Sub

关键修改说明： #

1. 切换HTML格式：通过.BodyFormat = olFormatHTML将邮件改为HTML模式，用.HTMLBody构建内容，支持表格等格式元素。
2. 自动获取数据范围：
   • 表头行自动识别第1行的所有有效列
   • 当前数据行自动读取循环到的行的所有有效列
3. 表格样式优化：添加边框、内边距和表头背景色，让表格更易读。
4. 效率优化：临时关闭Excel屏幕刷新，避免循环中频繁界面卡顿。

内容的提问来源于stack exchange，提问作者Esben Holm Hjørringgaard