如何用Excel表格数据生成Outlook邮件？Body无内容报类型不匹配怎么解决

核心错误原因 #

• 语法错误：.Body赋值段中多处错误使用逻辑运算符And拼接列字母与行号i，例如Range("K" And i)。And是逻辑运算符号，会尝试对两侧参数做布尔运算，直接触发类型不匹配错误，正确应使用字符串拼接符&。
• 未定义变量：注释掉的.HTMLBody段中Range(C, i)、Range(S, 1)等写法中的C、S属于未声明变量，并非代表列名的字符串，需要补充引号修正为Range("C" & i)、Range("S1")，同时存在把行号i作为列号使用的Range(i, i)错误写法，应修正为对应列号比如Range("I" & i)。
• 单元格值异常：若引用的单元格存在#N/A、#VALUE!等错误值，直接拼接字符串也会触发类型不匹配。

修复后代码 #

' 建议模块顶部添加Option Explicit强制变量声明
With OutMail
    .To = Range("D" & i).Value
    .CC = ""
    .BCC = ""
    .Subject = ActiveSheet.Name & " Service Insert"
    ' 修复后的HTMLBody示例，按需启用
    '.HTMLBody = "Hi " & Range("C" & i).Value & "<br><br>" & "Please see your Service Insert Below." & "<br><br>" _
    & Range("S1").Value & "<br>" & "Services Played: " & Range("S" & i).Value & "<br>" & "Doubling Services: " & Range("T" & i).Value & "<br>" & "Move Up Services: " & Range("W" & i).Value & " services from " & Range("Y" & i) & "<br>" & "Solo Services: " & Range("Z" & i).Value & "<br><br>" _
    & Range("AA1").Value & "<br>" & "Services Played: " & Range("AA" & i).Value & "<br>" & "Doubling Services: " & Range("AB" & i).Value & "<br>" & "Move Up Services: " & Range("AE" & i).Value & " services from " & Range("AG" & i) & "<br>" & "Solo Services: " & Range("AH" & i).Value & "<br><br>" _
    & "Pay Period Totals" & "<br>" & "Total Leave Used: " & Range("F" & i).Value & "<br>" & "Sick Leave Used: " & Range("I" & i).Value & "<br>" & "Total Doubling Pay: " & Range("K" & i).Value & "<br>" & "Total Move Up Pay: " & Range("L" & i).Value & "<br>" & "Total Solo Pay: " & Range("M" & i).Value & "<br>" & "Total Pay Correction: " & Range("N" & i).Value & "<br>" & "Parking Reimbursement: " & Range("O" & i).Value & "<br>" & "Mileage Reimbursement: " & Range("P" & i).Value & "<br>" & "Travel Reimbursement: " & Range("Q" & i).Value & "<br>" & "Total Additional Pay: " & Range("R" & i).Value & "<br><br>" _
    & "Season Totals" & "<br><br>" & "Total Season Services Used: " & Range("AZ" & i).Value & "<br>" & "Sick Leave Remaining: " & Range("AY" & i).Value & "<br><br>" & "Please let me know if you have any questions or concerns." & "<br><br>" & "Best, "
    
    ' 修复后的Body，将所有And替换为&
    .Body = "Hi " & Range("C" & i).Value & vbNewLine & vbNewLine & "Please see your Service Insert Below." & vbNewLine & vbNewLine _
    & Range("S1").Value & vbNewLine & "Services Played: " & Range("S" & i).Value & vbNewLine & "Doubling Services: " & Range("T" & i).Value & vbNewLine & "Move Up Services: " & Range("W" & i).Value & " services from " & Range("Y" & i) & vbNewLine & "Solo Services: " & Range("Z" & i).Value & vbNewLine & vbNewLine _
    & Range("AA1").Value & vbNewLine & "Services Played: " & Range("AA" & i).Value & vbNewLine & "Doubling Services: " & Range("AB" & i).Value & vbNewLine & "Move Up Services: " & Range("AE" & i).Value & " services from " & Range("AG" & i) & vbNewLine & "Solo Services: " & Range("AH" & i).Value & vbNewLine & vbNewLine _
    & "Pay Period Totals" & vbNewLine & "Total Leave Used: " & Range("F" & i).Value & vbNewLine & "Sick Leave Used: " & Range("I" & i).Value & vbNewLine & "Total Doubling Pay: " & Range("K" & i).Value & vbNewLine & "Total Move Up Pay: " & Range("L" & i).Value & vbNewLine & "Total Solo Pay: " & Range("M" & i).Value & vbNewLine & "Total Pay Correction: " & Range("N" & i).Value & vbNewLine & "Parking Reimbursement: " & Range("O" & i).Value & vbNewLine & "Mileage Reimbursement: " & Range("P" & i).Value & vbNewLine & "Travel Reimbursement: " & Range("Q" & i).Value & vbNewLine & "Total Additional Pay: " & Range("R" & i).Value & vbNewLine & vbNewLine _
    & "Season Totals" & vbNewLine & vbNewLine & "Total Season Services Used: " & Range("AZ" & i).Value & vbNewLine & "Sick Leave Remaining: " & Range("AY" & i).Value & vbNewLine & vbNewLine & "Please let me know if you have any questions or concerns." & vbNewLine & vbNewLine & "Best, "
    
    '.Attachments.Add ActiveWorkbook.FullName
    .Display   'or use .Send
End With
On Error GoTo 0

优化建议 #

• 拼接正文前可用CStr()将单元格值强制转换为字符串，避免空值或错误值引发异常，示例：CStr(Range("K" & i).Value)
• 不要同时启用.Body和.HTMLBody，Outlook会优先展示.HTMLBody内容，不需要的属性保持注释即可。
• 遍历多工作表时，明确指定工作表对象，不要直接使用Range默认取活动工作表的值，避免取错单元格内容。

内容的提问来源于stack exchange，提问作者MacLean Ferguson