Insert image in msg.body

owen4512

Board Regular
Joined
Dec 10, 2014
Messages
71
Hi all,

I'm looking to insert an image from file source "D:\users\Owen\Desktop\Test\IMG_123.jpg" into my msg.body. Is there a way this can be done?

Code:
Sub Send_email()


   Dim sh As Worksheet
   Set sh = ThisWorkbook.Sheets("email")
    
   Dim Outlook As Object
   Dim msg As Object
    
   Set Outlook = CreateObject("outlook.application")
   
   Dim i As Integer
   Dim last_row As Integer
   Dim p As String
    
   last_row = Application.WorksheetFunction.CountA(sh.Range("B:B"))
    
   For i = 2 To last_row
   Set msg = Outlook.createitem(0)
    If sh.Range("E" & i) = "" Then
    
   msg.To = sh.Range("B" & i).Value
   msg.bcc = sh.Range("C" & i).Value
   msg.Subject = "Request Status"


    If sh.Range("D" & I) = "Declined" Then
       If sh.Range("G" & I) = "Brand1" Then
          msg.body = "Thank you for contacting Brand1" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value

       ElseIf sh.Range("G" & I) = "Brand2" Then
        msg.body = "Thank you for contacting Brand2" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
       ElseIf sh.Range("G" & I) = "Brand3" Then
          msg.body = "Thank you for contacting Brand3" & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
       End If
   ElseIf sh.Range("D" & I) = "Approved" Then
       If sh.Range("G" & I) = "Brand1" Then
          msg.body = "Dear " & sh.Range("A" & I).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
       ElseIf sh.Range("G" & I) = "Brand2" Then
          msg.body = "Dear " & sh.Range("A" & I).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
       ElseIf sh.Range("G" & I) = "Brand3" Then
          msg.body = "Dear " & sh.Range("A" & I).Value & vbCrLf & vbCrLf & "I'm contacting you in regards to " & sh.Range("F" & I).Value
       End If
 End If


 msg.display
   If sh.Range("E" & i).Value = "Sent" Then
    Else
    sh.Range("E" & i) = "Sent"
    End If
    End If
    
Next i




MsgBox "Email has been sent"
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
One way would be to first attach the image to the email, and then assign HTML code to the HTMLBody property of the MailItem object. Here's an example where the image is embedded at the end of the email body...


1) Attach the image to the email...


Code:
msg.Attachments.Add "D:\users\Owen\Desktop\Test\IMG_123.jpg"


2) Assign one or more paragraphs to the body of the email, and embed the image at the end (Note: You'll need to remove the spaces after each angled bracket (ie. < )...


Code:
msg.HTMLBody = "< p>This is the first paragraph...< /p>" & _
            "< p>This is the second paragraph...< /p>" & _
            "< img src=""cid:IMG_123.jpg"">"


Hope this helps!
 
Last edited:
Upvote 0
Perhaps I've misunderstood, but i'm not sure exactly where i should add this piece of code? This is what I've done;

Code:
If sh.Range("N" & i) = "Declined" Then
        msg.Attachments.Add "[COLOR=#333333]D:\users\Owen\Desktop\Test\IMG_123.jpg[/COLOR]"
        msg.HTMLBody = "< p>This is the first paragraph...</p>" & _
                        "<p>This is the second paragraph...</p>" & _
                        "<img src=""cid:[COLOR=#333333]IMG_123.jpg[/COLOR]"">"
 
Last edited:
Upvote 0
ignore the above code it seems to have jumbled up


Code:
If sh.Range("N" & i) = "Declined" Then   
 msg.Attachments.Add "D:\users\Owen\Desktop\Test\IMG_123.jpg"
        msg.HTMLBody = "< p>This is the first paragraph...< /p>" & _
                                  "< p>This is the second paragraph...< /p>" & _
                                  "< img src=""cid:IMG_123.jpg"">"
 
Last edited:
Upvote 0
ignore the above code it seems to have jumbled up


Code:
If sh.Range("N" & i) = "Declined" Then   
 msg.Attachments.Add "D:\users\Owen\Desktop\Test\IMG_123.jpg"
        msg.HTMLBody = "< p>This is the first paragraph...< /p>" & _
                                  "< p>This is the second paragraph...< /p>" & _
                                  "< img src=""cid:IMG_123.jpg"">"


Update : This has now been resolved. I missed the .jpg :/
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top