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
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: