如何用VBA实现Outlook邮件内嵌可编辑表格与图表（解决图表不显示）

Hey there! The problem with your current code is that it only copies cell ranges to the temporary HTML file, but charts are separate Shape objects that don't get included when you just copy a cell range. Let's adjust the code to capture both your table and chart, then convert them into editable HTML for Outlook.

Modified Full Code #

Sub Mail()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim wsNightLetter As Worksheet
    Dim wsDistribution As Worksheet
    
    ' Set worksheet references to avoid repeated Activate calls (cleaner code)
    Set wsNightLetter = ThisWorkbook.Worksheets("SS Night Letter")
    Set wsDistribution = ThisWorkbook.Worksheets("Distribution List")
    
    subb = wsNightLetter.Range("B11").Value
    distlist = wsDistribution.Range("C3").Value
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = distlist
        .CC = ""
        .BCC = ""
        .Subject = subb
        ' Pass the specific range we need (A11:K82) to our updated function
        .HTMLBody = RangetoHTMLWithChart(wsNightLetter.Range("A11:K82"))
        .Display
    End With
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set wsNightLetter = Nothing
    Set wsDistribution = Nothing
End Sub

Function RangetoHTMLWithChart(targetRng As Range) As String
    ' Updated by adapting Ron de Bruin's original function to include charts
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    Dim chartObj As ChartObject
    Dim tempImagePath As String
    Dim destCell As Range
    
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".html"
    tempImagePath = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".png"
    
    ' Create a new temporary workbook
    Set TempWB = Workbooks.Add(1)
    Set destCell = TempWB.Sheets(1).Range("A1")
    
    ' Step 1: Copy the target cell range to temp workbook
    targetRng.Copy
    With destCell
        .PasteSpecial Paste:=xlPasteColumnWidths
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .PasteSpecial Paste:=xlPasteFormats
    End With
    
    ' Step 2: Locate and export the chart from the source worksheet
    ' Adjust the chart name/index if your chart has a different name
    For Each chartObj In targetRng.Parent.ChartObjects
        ' Check if the chart is within or adjacent to our target range
        If Not Intersect(chartObj.TopLeftCell, targetRng) Is Nothing Or _
           Not Intersect(chartObj.BottomRightCell, targetRng) Is Nothing Then
            ' Export chart to temporary image
            chartObj.Chart.Export Filename:=tempImagePath, FilterName:="PNG"
            ' Paste the image into the temp workbook, right after the cell range
            Set destCell = TempWB.Sheets(1).Cells(targetRng.Rows.Count + 2, 1)
            TempWB.Sheets(1).Shapes.AddPicture _
                Filename:=tempImagePath, _
                LinkToFile:=msoFalse, _
                SaveWithDocument:=msoTrue, _
                Left:=destCell.Left, Top:=destCell.Top, Width:=-1, Height:=-1
            Exit For ' Stop after finding the relevant chart
        End If
    Next chartObj
    
    ' Step 3: Publish the temp sheet to HTML
    With TempWB.PublishObjects.Add( _
        SourceType:=xlSourceRange, _
        Filename:=TempFile, _
        Sheet:=TempWB.Sheets(1).Name, _
        Source:=TempWB.Sheets(1).UsedRange.Address, _
        HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    
    ' Step 4: Read the HTML content
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTMLWithChart = ts.ReadAll
    ts.Close
    
    ' Clean up formatting for Outlook
    RangetoHTMLWithChart = Replace(RangetoHTMLWithChart, "align=center x:publishsource=", _
                                  "align=left x:publishsource=")
    
    ' Step 5: Clean up temporary files and objects
    TempWB.Close savechanges:=False
    Kill TempFile
    On Error Resume Next ' Ignore error if image file was never created
    Kill tempImagePath
    On Error GoTo 0
    
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    Set destCell = Nothing
    Set chartObj = Nothing
End Function

Key Changes Explained #

• Worksheet References: We use direct worksheet references instead of repeated Activate calls to make the code more reliable and cleaner.
• Chart Handling: The updated RangetoHTMLWithChart function now:
  • Exports the chart from your "SS Night Letter" sheet as a temporary PNG image
  • Pastes this image into the temporary workbook right after your table range
  • Includes the image in the HTML output when publishing the temp sheet
• Cleanup: We add code to delete the temporary image file after use, so you don't have leftover files in your temp folder.

Notes #

• If your worksheet has multiple charts, adjust the For Each chartObj loop to target the specific chart you need (e.g., use chartObj.Name = "YourChartName" instead of checking position).
• The chart will appear as an editable image in Outlook—you can resize it directly in the email body.

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