Excel VBA通过Gmail发邮件：图片无法嵌入正文问题求助

要让图片显示在邮件正文里，不能直接用本地文件路径——收件方的邮件客户端无法访问你的本地文件。必须把图片作为嵌入式附件，通过Content-ID（CID）在HTML正文中引用它，具体修改方案如下：

核心修改点 #

• 将目标图片添加为嵌入式附件，并指定唯一的CID标识
• 在HTMLBody的<img>标签中，用cid:你的CID值替换本地文件路径

修改后的完整代码 #

Sub email_CDO()
    Dim month As String, page_name As String ' 补充声明未定义的变量
    Dim wb As Workbook, iRow As Long
    Dim iMsg As Object, iConf As Object, Flds As Object
    Dim imgPath As String, imgCID As String
    
    Set wb = ThisWorkbook
    imgPath = "C:\Users\foodpanda\Desktop\Email\Storage\email.jpg" ' 要嵌入正文的图片路径
    imgCID = "email_main_image" ' 自定义唯一CID，避免特殊字符
    
    For iRow = 2 To wb.Sheets("client_list").Range("A1").CurrentRegion.Rows.Count
        ' 示例：从A列获取page_name，根据实际数据源调整
        page_name = wb.Sheets("client_list").Cells(iRow, 1).Value
        month = "2024年XX月" ' 替换为实际月份值
        
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
        
        iConf.Load -1 ' 加载CDO默认配置
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "waiyan01@gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "foukxizyifqoshhu"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
            .Update
        End With
        
        With iMsg
            Set .Configuration = iConf
            .To = wb.Sheets("client_list").Cells(iRow, 2).Value
            .CC = ""
            .BCC = ""
            .ReplyTo = "waiyan01@gmail.com"
            .From = "waiyan01@gmail.com"
            .Subject = "--" & " " & month & " " & page_name
            
            ' 添加嵌入式图片附件并设置CID
            .AddAttachment imgPath
            .Attachments(.Attachments.Count).Fields("urn:schemas:mailheader:content-id") = "<" & imgCID & ">"
            .Attachments(.Attachments.Count).Fields.Update
            
            ' HTML正文通过CID引用图片
            .HTMLBody = "Hello " & page_name & _
                "<br><br>--" & _
                "<br><br>--" & _
                "<br><br>--" & _
                "<img src='cid:" & imgCID & "' alt='邮件内嵌图片'>" & _
                "<br><br><font size='1' color='grey'>替换为你的免责声明文本</font>"
            
            If .To <> "" Then
                ' 单独添加普通附件（非内嵌）
                .AddAttachment "C:\Users\Desktop\Email\Storage\example.jpg"
                .Send
            End If
        End With
        
        ' 释放对象资源
        Set iMsg = Nothing
        Set iConf = Nothing
        Set Flds = Nothing
    Next iRow
   
    MsgBox "所有邮件已发送完成！"
End Sub

关键说明 #

1. CID规则：CID需是唯一标识，设置时要用<>包裹，HTML引用时只写cid:xxx（不带<>）
2. 变量适配：代码中补充了page_name和month的定义，你需要根据实际数据源调整它们的取值逻辑
3. 附件区分：嵌入式附件用于正文显示，普通附件单独调用AddAttachment即可，二者互不干扰
4. Gmail权限：确保你的Gmail账号开启了SMTP服务，若开启两步验证需使用应用专用密码而非原密码

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