Embedding Photo With HTML Using Excel to Outlook VBA

[jamesmev]

Sub SendByOne()
Dim c As Range
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim i As Integer
On Error Resume Next


For Each c In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells
picName = c.Offset(0, 2).Value
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem
.SentOnBehalfOfName = "CUSTOMER"
.To = c.Offset(0, 3).Value
.CC = c.Offset(0, 4).Value
.Subject = c.Offset(0, 1).Value
.Attachments.Add picName


.HTMLBody = " "
.HTMLBody = .HTMLBody & c.Offset(0, 5).Value & "</b>"
.HTMLBody = .HTMLBody & "<br> " & c.Offset(0, 7).Value
.HTMLBody = .HTMLBody & "<br><b><font size='3'>" & c.Offset(0, 8).Value & "</font>"

.HTMLBody = .HTMLBody & "<br> <br> <br> <br> </b> Dear " & c.Offset(0, 5).Value



.Display 'to display first
'.Send 'to send it in background
End With
Next c

End Sub

 

[Domenic]

After you've assigned the full path and filename to picName, first extract the filename as follows...

    Dim tempFilename As String
    tempFilename = Dir(picName, vbNormal)

Then, after you've attached the image file to the email, you can embed it something like this...

    .HTMLBody = .HTMLBody & "<br><img src=""cid:" & tempFilename & """>"

Hope this helps!

 

[jamesmev]

After you've assigned the full path and filename to picName, first extract the filename as follows...

    Dim tempFilename As String
    tempFilename = Dir(picName, vbNormal)

Then, after you've attached the image file to the email, you can embed it something like this...

    .HTMLBody = .HTMLBody & "<br><img src=""cid:" & tempFilename & """>"

Hope this helps!

When Running this in my file it attaches the image to the file. I am needing this to appear in the body.
There is a red x in the body where the image should be.

Any Suggestions?

 

[Domenic]

Try stepping through your code using the F8 key, until you've reached the line after the filename has been assigned to tempFilename. Then, move your cursor over the variable tempFilename. You should see the filename, without the path, assigned to it? Is this the case?

 

[jamesmev]

Try stepping through your code using the F8 key, until you've reached the line after the filename has been assigned to tempFilename. Then, move your cursor over the variable tempFilename. You should see the filename, without the path, assigned to it? Is this the case?

I was able to get the image to embed - However - the image needs to be at the end of the message. The message above the image disappeared and only the message below the image was shown.
Any suggestions?

 

[Domenic]

Can you post the line or lines that you're using for .HTMLBody?

 

[jamesmev]

Sub SendByOne()
Dim c As Range
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim i As Integer
On Error Resume Next
Dim tempFilename As String




For Each c In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells
picName = c.Offset(0, 2).Value
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
With OutLookMailItem
.SentOnBehalfOfName = "EMAIL LOCATION"
.To = c.Offset(0, 3).Value
.CC = c.Offset(0, 4).Value
.Subject = c.Offset(0, 1).Value

.Attachments.Add picName
.Attachments.Add "C:\Users\USER\Pictures\My_Demo.jpg"


.HTmlbody = .HTmlbody & "<br><br>"
.HTmlbody = .HTmlbody & c.Offset(0, 5).Value & "</b>"
.HTmlbody = .HTmlbody & "<br> " & c.Offset(0, 7).Value
.HTmlbody = .HTmlbody & "<br><b><font size='3'>" & c.Offset(0, 8).Value & "</font>"

.HTmlbody = .HTmlbody & "<br> <br> <br> <br> </b> Good day " & c.Offset(0, 5).Value
'Introduction
.HTmlbody = .HTmlbody & "<br><br>We would like to thank you for your interest in learning more about our Business to Business platform. Below is a link to our interactive demo. On this demo you will be able to review every aspect of the site."

.HTmlbody = .HTmlbody & "<br><br>Once you review the demo at your own pace you can then signup on the site by clicking the Signup button all without having to leave the demo!"
.HTmlbody = .HTmlbody & "<br><br> Also note, we value your feedback so if there is anything that you see or don't see that will add value to your business we want to know. Click on the Provide Feedback button, this will allow for us to continue to grow the site with you the customer mind."


.HTmlbody = "<IMG src=""cid:My_Demo.jpg"" width=500>"
.HTmlbody = .HTmlbody & "<br> <a href = ""http://www.url.com/mydemo""> MyDEMO </a>"
'start

.HTmlbody = .HTmlbody & "<br></i><br>Thank you for your continued partnership through this challenging times."
.HTmlbody = .HTmlbody & "<br> <b> Sales & Service Company"

' .HTmlbody = .HTmlbody & "<br>If you would like to embed my latest video you can find it here"
'.HTmlbody = .HTmlbody & "<br> Check it out <a href = ""URL HERE""> HERE </a>"
.Display 'to display first
'.Send 'to send it in background
End With
Next c

End Sub

 

[Domenic]

It should be as follows...

.HTMLBody = .HTMLBody & "<IMG src=""cid:My_Demo.jpg"" width=500>"

Easy to overlook...

 

[jamesmev]

Thank you - that worked perfectly. I can't believe i missed that simple thing... 2 pair of eyes!