send email with picture in body using excell macro

[flavio]

Hi guys

this has probably been asked a million times before and for that I apologise; I did looked in google for examples on how to do this and did not find an answer to my issue.

I managed to find a macro @ Mail a message to each person in a range that works perfect KUDOS to Ron but only thing missing is on how to make it add a picture in the email body as well.

here is my worksheet example

and here is my code so far

Sub Double_reward_points()
Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strbody As String
     For Each cell In Range("E2:E20")
         strbody = strbody & cell.Value & vbNewLine
     Next
     
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Loyalty Double Reward Points at Eden Wine Bar"
                .Body = "Dear " & Cells(cell.Row, "A").Value & vbNewLine & vbNewLine & strbody
                .Attachments.Add ("C:\Pictures\poster.png")
                .Send  'Or use Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

In resume I want to click on a button seen in cell range D to run macro
macro should send email if column C has yes text on it
and skip if No
Then should grab text from body in column E
then add picture in attachments

so far so good it all works fine ( tested) however I cant find a way to also add same picture in attachment to display in email body also

What am I missing ?
Hope this makes sense

Thank you so much in advance
Flavio

 

[Trevor G]

You would use the body text as HTMLBody to place the picture in the email something like this

Sub ExportChart()
Sheets(1).ChartObjects.("Chart 1").Chart.Export "C:\Chart1.png"
End Sub

Then:

Sub testSendObj()
Dim olApp As Object
Dim olMail As Object
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)

With olMail
.Display
.To = "Email Address"
.Body="Some text"
.HTMBody = .HTMBody & "< img src='C:\Chart1.png' >"
End With
End Sub

I have had to add a space between < img and also png' >", please remove the space's

 

[flavio]

You would use the body text as HTMLBody to place the picture in the email something like this



Then:


I have had to add a space between < img and also png' >", please remove the space's

Hi Trevor
Thank you for replying so fast however im still a newbie at macros so forgive me for this noobish question.
Which part in the code should I add the new code?

Many thx
Flavio

 

[flavio]

still cant figure it out :/

added code in my code and it brakes it

dont know what am i missing

Please help

Many thanks in advance
Flavio