0

I'm trying to distribute emails using an Outlook oft template.
On the oft template, at a specific location, I want to attach a jpg file which I have created from Excel range.

Dim OutApp As Object
Dim OutMail As Object
Dim str_jpeg_file as String
str_jpeg_file  = "B:\temp\test.jpg"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItemFromTemplate(ThisWorkbook.Path & "\Test.oft")
With OutMail
    .To = "test@abcd.com"
    .CC = ""
    .BCC = ""
    .Subject = "Test mail"
    .SentOnBehalfOfName = "zyz@abcd"
    .Attachments.Add str_jpeg_file, 1, 0
    .HTMLBody = Replace(.HTMLBody, "##IMAGE_PLACEHOLDER##", "<img src=""cid:test.jpg""height=520 width=750>")
    '.Send
    .display
End With

Edit:

jpg file path updated i.e. str_jpeg_file

Community
  • 1
  • 1
1S1a4m9
  • 27
  • 7
  • What about the [Attachments.Add method](https://learn.microsoft.com/en-us/office/vba/api/outlook.attachments.add)? If you do this `"
    "` the `jpg_file_path` will point to your local computer and the one who gets the mail cannot access it because on his computer it does not exist.     – Pᴇʜ Jan 29 '20 at 07:47
  • Attachments.Add method will attach the file to email but the requirement here is to Add jpg file into the email body at specific location. Actually,`"
    "` this line here is adding the image into the email body at top on the email body     – 1S1a4m9 Jan 29 '20 at 09:14
  • But you need to attach the file first before you can embed it in the HTML body because of the reason I mentioned in my first comment! You need to send the image along with your email otherwise the recepient doesn't have it and cannot see it. – Pᴇʜ Jan 29 '20 at 09:16
  • Does this answer your question? [Embed picture in outlook mail body excel vba](https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba) – Pᴇʜ Jan 29 '20 at 09:17
  • [Embed picture in outlook mail body excel vba](https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba) This will work for complete fresh email but for existing oft template it will not work. it will override the existing email body. – 1S1a4m9 Jan 29 '20 at 10:14
  • 1
    You will of course need to have eg. a placeholder in your template that you can replace with your `` tag then. So read the `.HTMLBody` replace your placeholder and write it back to `.HTMLBody`. Syntax similar to: `.HTMLBody = Replace(.HTMLBody, strFind, strNew)`. But note that your `src=` must look like `"` starting with `cid:` and only the filename not the complete path as in the link. – Pᴇʜ Jan 29 '20 at 10:19
  • how do we have placeholder in oft template ? – 1S1a4m9 Jan 29 '20 at 11:01
  • 1
    Just write a **unique** text into your template that you can then replace: Eg write in your templete `##IMAGE_PLACEHOLDER##` and then replace that later with your `` in your VBA code. – Pᴇʜ Jan 29 '20 at 11:05
  • Thank you, i'm getting close now. The object is inserted where i want but it is giving an error saying _The Linked image cannot be displayed, the file may have been moved, renamed, or deleted. verify that the link points to the correct file and location_ – 1S1a4m9 Jan 29 '20 at 12:56
  • I have added this line to attached the file `.Attachments.Add str_jpeg_file, 1, 0` – 1S1a4m9 Jan 29 '20 at 12:57
  • Please [edit] your question and show how your code looks like now. – Pᴇʜ Jan 29 '20 at 12:58
  • @Pᴇʜ, i have updated the line on the edit – 1S1a4m9 Jan 30 '20 at 05:47
  • What is the value of `str_jpeg_file` ? – Siddharth Rout Jan 30 '20 at 05:52
  • `.Attachments.Add str_jpeg_file, 1, 0` This method has a drawback. If you are sending this email to non outlook clients (for example gmail) then this will not look nice. All the images will pile up at the bottom. You will have to upload the image in a free file sharing server(Ex: imgur) and then use the link in the html body. This way the mailer can be viewed in any of the email client. – Siddharth Rout Jan 30 '20 at 05:54
  • `str_jpeg_file` is the path for the file. Also, my all clients are outlook clients. – 1S1a4m9 Jan 30 '20 at 06:08
  • `str_jpeg_file is the path for the file.` I understand that. I just didn't see you setting it's value and hence wanted to reconfirm. What problem are you facing with the above code? – Siddharth Rout Jan 30 '20 at 06:17
  • **The Linked image cannot be displayed, the file may have been moved, renamed, or deleted. verify that the link points to the correct file and location** i'm getting this error on the image object with red cross. – 1S1a4m9 Jan 30 '20 at 06:21
  • What is the exact value of `str_jpeg_file`? Tell us the result of `Debug.Print str_jpeg_file`. In this code above this variable is never set, so it is empty. Please make sure you include all the necessary information and code. – Pᴇʜ Jan 31 '20 at 07:21
  • value updated for `str_jpeg_file` – 1S1a4m9 Jan 31 '20 at 12:46

1 Answers1

1

To give you a full working example:

Option Explicit

Sub test()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim str_jpeg_file As String
    str_jpeg_file = "C:\Temp\test.png"
    Set OutApp = CreateObject("Outlook.Application")
    'Set OutMail = OutApp.CreateItemFromTemplate(ThisWorkbook.Path & "\Test.oft")
    'instead of a template I create a new mail
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "test@abcd.com"
        .CC = ""
        .BCC = ""
        .Subject = "Test mail"
        .SentOnBehalfOfName = "zyz@abcd"
        .Attachments.Add str_jpeg_file, 1, 0
        'first we write some placeholder text so we can replace it
        .HTMLBody = "lalala ##IMAGE_PLACEHOLDER## lala"

        'replace
        .HTMLBody = Replace(.HTMLBody, "##IMAGE_PLACEHOLDER##", "<img src=""cid:test.png""height=256 width=256>")
        '.Send
        .display
    End With
End Sub

Note that I used a new email (no template) because it is easier to show here.

And it works perfectly:

enter image description here

So if it doesn't work for you either your image file is no valid image or you did something else wrong like typos etc. or your template is somehow the issue. Check again with the code above.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73