excelvbaoutlook

How do I remove the border in VBA for a HTML body image?


I am trying to produce an email with both an attachment (which is working fine) and a image created from a set of cells in Excel (this is sort of working), although whatever I do it always puts a grey border around the image which I have tried to remove with style='border:0' and variants but nothing works. Any suggestions? Code segments below:

Dim rngToPicture As Range
   Dim outlookApp As Object
   Dim Outmail As Object
   Dim strTempFilePath As String
   Dim strTempFileName As String
   Dim strPDFPath As String

   strPDFPath = "https://fcx365.sharepoint.com/Sites/STOTEC/ProjectFiles/Assignments/240925%20%2D%20Shift%20Report%20Refresh/" _
        & saveName & ".pdf"
    
   strTempFileName = "RangeAsPNG"
    
   Set rngToPicture = Worksheets("Email Template").Range("A1:P74")
   Set outlookApp = CreateObject("Outlook.Application")
   Set Outmail = outlookApp.CreateItem(olMailItem)
  
With Outmail
    .To = Join(Application.Transpose(Worksheets("Lists").Range("I2:I24")), ";")
    .Subject = "Shift Report"
        
    Call createPNG(rngToPicture, strTempFileName)
        
    strTempFilePath = Environ$("temp") & "\" & strTempFileName & ".png"
    .Attachments.Add strTempFilePath, olByValue, 0
    .Attachments.Add strPDFPath
    .HTMLBody = "<img src='cid:" & strTempFileName & ".png' style='border:0'>"
    .Display
    .Send

    End With

    Set Outmail = Nothing
    Set outlookApp = Nothing
    Set rngToPicture = Nothing

Sub createPNG(ByRef rngToPicture As Range, nameFile As String)

Dim Email_Pic As String

Email_Pic = rngToPicture.Parent.Name

'Delete the existing PNG file of same name, if exists
On Error Resume Next
    Kill Environ$("temp") & "\" & nameFile & ".png"
On Error GoTo 0

'Copy the range as picture
rngToPicture.CopyPicture

'Paste the picture in Chart area of same dimensions
With ThisWorkbook.Worksheets(Email_Pic).ChartObjects.Add(rngToPicture.Left, rngToPicture.Top, rngToPicture.Width, rngToPicture.Height)
    .Activate
    .Chart.Paste
    'Export the chart as PNG File to Temp folder
    .Chart.Export Environ$("temp") & "\" & nameFile & ".png", "PNG"
End With
Worksheets(Email_Pic).ChartObjects(Worksheets(Email_Pic).ChartObjects.Count).Delete

End Sub

blue border is part of my Excel sheet that I want to keep but grey border is added to the image


Solution

  • You need to remove the border before exporting the chart. In your With block, include this line:

    .Chart.ChartArea.Format.Line.Visible = msoFalse


    As a side note, if you plan to ask more questions on StackOverflow in the future, I strongly recommend learning about Minimal Reproducible Examples:

    https://stackoverflow.com/help/minimal-reproducible-example