Trying to insert picture imagine into email via vba

[benwork]

Hi all,

Im trying to send an email of a report with the image attached in the body.

I've got it working but people are having issues when reading the html on their phones so I need it to be an image.

I've tried googling and getting help but I cant get anything

The image is located Sheets("Product compliance").Range("B2:w121")

Any help would be much appreciated. Im pretty green with vba.


Cheers

Ben




Sub email()
'send out the email
Dim Rng As Range, rnga As Range, rngb As Range, rngc As Range
Dim OutApp As Object
Dim OutMail As Object
Dim signature As String
Dim tstamp As String

' report location
Set Rng = Nothing
Set Rng = Sheets("Product compliance").Range("B2:w121")

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
' Subject location
tstamp = Sheets("Save and Send").Range("D5")
With OutMail
.Display
End With
signature = OutMail.HTMLBody

' change change email list here

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = tstamp
.HTMLBody = RangetoHTML(Rng) & _
vbNewLine & signature
.Attachments.Add Sheets("Save and Send").Range("D4") & Sheets("Save and Send").Range("D23") ' attaching the pdf

'.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[benwork]

Thanks again rick

I unfortunately have another issue. The image appears in the email before I send it, but as soon as I send it, the recipients do not receive the image?

Even when I go to my sent items, the body is empty.

 

[RickXL]

Hi,

Mine works every time I send it but not every time I save a draft copy.

Try saving a draft and also try removing the pasted picture and paste another one in that should test the next question.

I suppose your email settings allow pictures to be sent? It is not set to text only for instance? Mine is set to HTML.

If you can see the picture in the email then I think it has to be email settings.

If you can't send pictures then you might have to attach it, instead. You have the filename for that.

 

[clane1]

BenWork, I am hoping you can tell me what you were able to do to fix this issue:

I unfortunately have another issue. The image appears in the email before I send it, but as soon as I send it, the recipients do not receive the image?

Even when I go to my sent items, the body is empty.


I am using Outlook 2010 and I can see the image in the body of the e-mail and once I hit send, it's like the picture is deleted out and the recipient receives a blank e-mail.

 

[aayaanmayank]

Than

Hi,

I think this should do what you want:

Sub email()
  
    '===================================================
    ' Export Range as PNG file
    '===================================================
    '''' Set Range you want to export to file
    Dim r As Range
    Dim co As ChartObject
    Dim picFile As String
    Set r = Worksheets("Product compliance").Range("B2:W121")
  
    ''' Copy range as picture onto Clipboard
    r.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    picFile = Environ("Temp") & "\TempExportChart.png"
  
    ''' Create an empty chart with exact size of range copied
    Set co = r.Parent.ChartObjects.Add(Left:=r.Left, Top:=r.Top, Width:=r.Width, Height:=r.Height)
    With co
        ''' Paste into chart area, export to file, delete chart.
        .Chart.Paste
        .Chart.Export picFile
        .Delete
    End With
  

    '===================================================
    ' Create Email and Import Picture
    '===================================================
    'send out the email
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(olMailItem)
  
    Dim signature As String
    Dim tstamp As String
    Dim strBody As String
  
    ' Subject location
    tstamp = Sheets("Save and Send").Range("D5")
    OutMail.Display
    signature = OutMail.HTMLBody
  
    ' change change email list here
    strBody = "<body> <h2>Report</h2> <img src=""" & picFile & """ style=""width:304px;height:228px""></body>"
  
    On Error Resume Next
    With OutMail
        .To = "xxx@yyy.com"
        .CC = ""
        .BCC = ""
        .Subject = tstamp
        .HTMLBody = strBody & vbNewLine & signature
        .Attachments.Add Sheets("Save and Send").Range("D4") & Sheets("Save and Send").Range("D23") ' attaching the pdf
    End With
    Kill picFile
    On Error GoTo 0
  
    'Tidy Up
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set co = Nothing
    Set r = Nothing

End Sub

The first part
1. Selects your range
2. Pastes it as an image into a chart object
3. Saves the chart object as a temporary .png file

The second part is basically your original macro with the .HTMLBody now changed to embed the previously created image file.
The temporary file is then deleted.

Thanks for this code, i know its very old but it helped me. but i am facing two issue.
I need the image in center of email and i am also getting border line only on right side of the picture.
Can you please help me with these two issue. i will be greatfull.