Lotus Notes邮件VBA添加HTML签名显示路径问题求助

问题现象 #

• Lotus Notes已配置自动添加HTML签名，手动新建邮件时签名正常显示
• 通过VBA宏发送邮件时，签名区域仅显示HTML文件的本地路径（例如C:\User1\Email signatures - 2023\AF.html）

问题原因 #

当前代码直接将CalendarProfile配置文档中Signature字段存储的HTML文件路径拼接至邮件正文文本中，而Lotus Notes手动创建邮件时会自动解析该路径并加载HTML内容，VBA直接赋值文本不会触发这一解析逻辑，导致路径被当作纯文本显示。

解决方案 #

需要手动读取HTML签名文件的内容，然后将其以HTML格式插入邮件正文。修改步骤如下：

1. 新增读取HTML文件内容的逻辑
2. 将邮件正文设置为HTML格式，合并原正文与HTML签名内容
3. 替换原代码中直接拼接签名路径的部分

修改后的完整代码 #

'Public Sub SendNotesMail(Subject as string, attachment as string,
'recipient as string, bodytext as string,saveit as Boolean)
'This public sub will send a mail and attachment if neccessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.
Public Sub SendNotesMail(Subject As String, Attachment As String, Attachment1 As String, Recipient As String, ccRecipient1 As String, ccRecipient2 As String, ccRecipient3 As String, ccRecipient4 As String, BodyText As String, SaveIt As Boolean)
'Set up the objects required for Automation into lotus notes
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'THe current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    Dim signaturePath As String
    Dim signatureHTML As String
    Dim fso As Object
    Dim ts As Object
    
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    'Next line only works with 5.x and above. Replace password with your password
    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    'Open the mail database in notes
    Set Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If
    
    '获取签名文件路径
    signaturePath = Maildb.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
    
    '读取HTML签名文件内容
    If signaturePath <> "" Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        If fso.FileExists(signaturePath) Then
            Set ts = fso.OpenTextFile(signaturePath, 1, False, -2) '以UTF-8编码读取
            signatureHTML = ts.ReadAll
            ts.Close
        End If
        Set ts = Nothing
        Set fso = Nothing
    End If
    
    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.CopyTo = Array(ccRecipient1, ccRecipient2, ccRecipient3, ccRecipient4)
    MailDoc.Subject = Subject
    
    '将原正文转为HTML格式并合并签名
    Dim fullHTML As String
    fullHTML = "<html><body>" & Replace(BodyText, vbNewLine, "<br>") & "<br><br>" & signatureHTML & "</body></html>"
    MailDoc.HTMLBody = fullHTML '设置邮件为HTML格式
    
    MailDoc.SAVEMESSAGEONSEND = SaveIt
     'Set up the embedded object and attachment and attach it
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attchment")
    End If
    Set EmbedObj = Nothing
    If Attachment1 <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment1")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment1")
        MailDoc.CREATERICHTEXTITEM ("Attchment1")
    End If
    
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.send 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
    
MsgBox "Email inviata con successo."

End Sub

关键修改说明 #

• 新增Scripting.FileSystemObject读取HTML签名文件的内容，避免直接使用路径文本
• 使用MailDoc.HTMLBody属性设置邮件为HTML格式，将原正文文本换行符替换为<br>标签，确保格式一致
• 合并原正文与HTML签名内容，保持手动发邮件时的排版逻辑

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