0

How can I embed a photo at the end of an email? 

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
            .Display
        End With
        signature = OutMail.body

        With OutMail

            .To = cell.Value
            .CC = "xxxxx@gmail.com; xxxxxx@hotmail.com"
            .Subject = "Hello"
            .body = "Dear " & Cells(cell.Row, "A").Value _
                  & vbNewLine & _
                  vbNewLine & _
                  vbNewLine & _
                    "Body" & _
                   signature

            sourcefile = "XXXXXXX"
            sourcefile1 = "XXXXXX"
            .Attachments.Add sourcefile
            .Attachments.Add sourcefile1

            .Send

        End With
        Set OMail = Nothing
        Set OApp = Nothing

        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

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

The code works.

I would like to embed a Xmas image (JPEG). I have done a dig around the internet however am unable to figure it out what command function I can use to embed it.

Community
  • 1
  • 1
Adrian Yip
  • 1
  • 1
  • 1
    Remark out all the error handling stuff and then see what error you get on what line, and then include that in your question. – braX Dec 11 '19 at 05:43
  • duplicate of https://stackoverflow.com/questions/38905847/embedding-image-in-outlook-with-vba – RowanC Dec 11 '19 at 06:03
  • Body content disappeared after inserting .Attachments.Add emails .Attachments.Add "C:\Users\VAS PT 04\Desktop\12345678.jpeg", olByValue, 0 .HTMLBody = " " – Adrian Yip Dec 11 '19 at 06:27

1 Answers1

0

You'll need to use the HTMLBody property of the email, as plain text wont let you embed an image.

You will also need to know the filename (not only path) of your image. An example of code that might work is:

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
    .Display
    End With
        signature = OutMail.body


    With OutMail

        .To = cell.Value
        .CC = "xxxxx@gmail.com; xxxxxx@hotmail.com"
        .Subject = "Hello"

        imgpath = "c:\XXXXXX\"
        sourcefile = "XXXXXXX"
        sourcefile1 = "XXXXXX"
        .Attachments.Add imgpath & sourcefile
        .Attachments.Add imgpath & sourcefile1
        .HTMLBody = "<body><p>Dear " & Cells(cell.Row, "A").Value _
              & "<br/>& _
                "Body" & _
               signature & _
               "<IMG src=""""cid:""" & sourcefile & """ width=200></body>"

        .Send

End With
Set OMail = Nothing
Set OApp = Nothing


    On Error GoTo 0
    Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
RowanC
  • 1,611
  • 10
  • 16
  • Hi sorry I have never really used html before, the HTMLBody does not seem to working when i subbed my variables in – Adrian Yip Dec 11 '19 at 06:42
  • You'll need to check that it's valid html. Perhaps put a watch on the .send, and put the .htmlbody contents into a checker (or here?). Example - I have a

    open tag above, but no

    close tag     – RowanC Dec 11 '19 at 06:57
  • hi sorry, would you be able to speak simple english, I have got a few paragraphs to chuck in for the body however have no idea what these syntax ,
    , mean hence have 0 idea where to put in my contents and how to separate them into paragraphs thanks a lot! #newtovba     – Adrian Yip Dec 11 '19 at 07:06
  • For basic HTML see here: https://www.w3schools.com/html/ the email must start with and end with everything in the middle is standard html – RowanC Dec 11 '19 at 07:11