Excel VBA邮件宏问题：附件嵌入正文及格式调整需求

Let's work through your issues step by step—your current macro relies on plain text Body which strips signature formatting, adds attachments to the separate attachment pane instead of the body, and uses a layout that doesn't match your needs. Here's a revised, reliable solution:

Key Fixes & Improvements #

• Switch to HTMLBody instead of plain text Body to preserve signature font/color formatting
• Embed attachments directly into the email body (not just the attachment bar)
• Reorder content to your desired flow: Email Body → Embedded Attachment → Signature
• Replace flaky SendKeys with direct property setting for TITUS classification (far more stable)

Revised VBA Code #

Sub SendFormattedEmail()
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    Set wkb = Excel.Workbooks("macro.xlsm")
    Set wks = wkb.Worksheets("settings")
    
    Dim OApp As Object, OMail As Object
    Dim signatureHTML As String
    Dim attachmentPath As String
    Dim cid As String ' Unique ID to reference embedded attachment in HTML
    
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
    
    ' First display the email to load your default signature (in HTML format)
    With OMail
        .Display
        ' Capture the HTML version of your signature (preserves all formatting)
        signatureHTML = .HTMLBody
        .Close olSave ' Close temporarily to rebuild the email cleanly
    End With
    
    ' Reset the mail item to construct our desired layout
    Set OMail = OApp.CreateItem(0)
    attachmentPath = ActiveWorkbook.FullName
    
    With OMail
        .To = "mymail@mail.com"
        .Subject = "Type your email subject here"
        
        ' Add the attachment and mark it as embeddable with a unique CID
        cid = "embedded_workbook_" & Format(Now, "YYYYMMDDHHMMSS")
        .Attachments.Add attachmentPath, olByValue, 0
        .Attachments(.Attachments.Count).PropertyAccessor.SetProperty _
            "http://schemas.microsoft.com/mapi/proptag/0x3712001F", cid
        
        ' Build the HTML body in your desired order: Body → Attachment → Signature
        .HTMLBody = "<p>My email body</p>" & _
                    "<br><img src='cid:" & cid & "' alt='Embedded Workbook'>" & _
                    "<br>" & signatureHTML
        
        ' Set TITUS Classification directly (replace with your org's actual property ID if needed)
        ' Use Outlook Spy to find the correct property schema if this doesn't work
        .PropertyAccessor.SetProperty _
            "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/TITUS/Classification", _
            "Your TITUS Classification Value"
        
        .Display ' Optional: Remove this line if you want to send without preview
        .Send
    End With
    
    ' Clean up objects to avoid memory leaks
    Set OMail = Nothing
    Set OApp = Nothing
End Sub

Breakdown of Changes #

1. Signature Format Preservation:

   • We briefly display a blank email to load your default signature, then capture its HTML code instead of plain text. This ensures all font styles, colors, and line breaks from your Outlook signature are retained.

2. Embedded Attachment:

   • We assign a unique Content ID (CID) to the attachment, then reference that ID in the HTML body with <img src='cid:XXX'>. Outlook will display the Excel file as an embedded icon in the body instead of the separate attachment bar.

3. Layout Control:

   • The HTMLBody is built explicitly in the order you want: your custom body text first, then the embedded attachment, then your formatted signature.

4. TITUS Classification (No More SendKeys):

   • SendKeys is unreliable because it depends on window focus. Instead, we use PropertyAccessor to directly set the TITUS classification property. If the schema string here doesn't work for your org, use Outlook Spy to inspect a manually classified email and copy the correct property path.

5. Stability:

   • We clean up Outlook objects properly to prevent memory leaks, and avoid relying on brittle UI interactions.

Troubleshooting Tips #

• If the embedded attachment doesn't show up: Double-check that the CID in the HTML matches exactly what you assigned to the attachment property.
• If signature formatting is still off: Confirm your Outlook signature is set to use HTML format (Outlook Options → Mail → Compose messages in this format: HTML).
• For TITUS issues: Reach out to your org's IT team if you can't find the correct property schema string.

内容的提问来源于stack exchange，提问作者Mr.Riply