VBA中为邮件HTMLbody属性赋值时HTML代码双引号的正确使用方法

核心规则 #

VBA字符串中如果需要包含双引号，必须用两个连续的双引号做转义，单个双引号会被VBA识别为字符串的结束标记，直接触发语法错误。

你现有代码的两处错误 #

• HTML中a标签的href属性双引号未做转义处理
• 字符串拼接时漏写VBA连接符&，错误地把& vbCrLf写到了HTML字符串内部

修正后的完整代码 #

Sub enviar_email()
    Dim linha As Integer
    Dim Objeto_outlook As Object
    Dim Email As Object
    ' 建议把Outlook对象创建移到循环外，减少重复创建开销
    Set Objeto_outlook = CreateObject("Outlook.Application")
    
    For linha = 3 To 13
        Set Email = Objeto_outlook.createitem(0)
        
        texto = "<!DOCTYPE html><html><body><p>Hi All,<br><br>" & vbCrLf & _
        "Find in " & vbCrLf & _
        "<a href=""https://alcoainc.sharepoint.com/sites/GSSFinance/Accoutingprocess/SitePages/Home.aspx?RootFolder=%2Fsites%2FGSSFinance%2FAccoutingprocess%2FShared%20Documents%2F2021%20Chargeback%20%2D%20ADI%27s%2F11%2ENovember%2FEurope%2FNorway&FolderCTID=0x0120000D0C1EF00C57724DB137DE9812EE80D4&View=%7B87CB7C73%2DFB6E%2D415F%2D90A5%2D623BE45360BC%7D"">Sharepoint</a>" & vbCrLf & _
        "ADIs regarding November chargeback entries to be posted in your region.<br>Could you please post the entries in GL by Tuesday(Nov 16th)?<br><br>If you identify any issue that should be adjusted or any remap to be done, please let us know.<br>Definitions:IICS - transactions between different Countries;<br>Intercompany – transactions between different Legal Entities inside the same Country;<br>Intracompany - transactions between the same Legal Entities and the same Countries.<br>Note that all ADIs were created considering EParent rules.<br><br>Below is your help chain:<br>" & vbCrLf & _
        "Accounting inquires -  Brunorio, Fernanda P.A.; Perna, Denise C.S.<br>Plan inquires - Moreira, Leonardo; Silva, Juliana C.<br>Thank you,<br>Célio Melo.</p></body></html>"

        Email.display
        Email.To = Cells(linha, 7).Value
        Email.cc = Cells(linha, 8).Value
        Email.Subject = Cells(linha, 6).Value
        Email.HTMLbody = texto
    Next linha
    ' 用完释放对象
    Set Email = Nothing
    Set Objeto_outlook = Nothing
End Sub

更简便的替代方案 #

如果HTML内容较长、修改频率高，无需硬编码到VBA里，可以把完整的HTML代码直接粘贴到Excel的空白单元格（比如Z1单元格），赋值时直接读取单元格内容即可，完全不需要处理双引号转义：

Email.HTMLbody = Range("Z1").Value

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