Excel / VBA: Can't Add image to body of outlook msg.

countryfan_nt

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
765
Hello friends, Hope all is well! Please help me. The code is supposed to insert an image from a folder and place it in the body
of the email / outlook. I am getting a grey box instead.

Please help me fix the below VBA code.

Thank you Very much in advance!

Capture.PNG



VBA Code: 
Sub SENDMAIL()
  
Dim rng As Range
Set rngT = Sheets("staff").Range("A1:D1")
' Only send the visible cells in the selection.
Dim pic As String

LR = Sheets("db").Range("H1").Value
Set rng = Sheets("staff").Range("A1:D17118").SpecialCells(xlCellTypeVisible)

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

'Adjust this according to your table gets the last row in the Sheet named "DB" column "A"
    ToAddress = Sheets("DB").Range("f1").Value
pic = "C:\Users\nawaf.altaher\Desktop\Labs\" & Sheets("DB").Range("J1").Value

    On Error Resume Next
    With OutMail
        .To = ToAddress
        .CC = ccAddress
        .BCC = ""
        .Subject = "Medical Insurance Renewal."
       .Attachments.Add pic, 1, 0
       .HTMLBody = "<html><p></p>" & _
                "<img src=""PIC""height=520 width=750>"
                        
      .Display
    On Error GoTo 0

 ' Try to send
    On Error Resume Next
   
    Set OutMail = Nothing
    Set OutApp = Nothing

End With
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi countryfan_nt
try this code, pay attention to the comments I added in the macro
VBA Code: 
Sub SENDMAIL2()
 'https://www.mrexcel.com/board/threads/excel-vba-cant-add-image-to-body-of-outlook-msg.1225426/
 
Dim rng As Range
Set rngT = Sheets("staff").Range("A1:D1")
' Only send the visible cells in the selection.
Dim pic As String

LR = Sheets("db").Range("H1").Value
Set rng = Sheets("staff").Range("A1:D17118").SpecialCells(xlCellTypeVisible)

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

'Adjust this according to your table gets the last row in the Sheet named "DB" column "A"
    ToAddress = Sheets("DB").Range("f1").Value
MyPath = "C:\Users\nawaf.altaher\Desktop\Labs\" ' <<==== SEPARATE path from File name
pic = Sheets("DB").Range("J1").Value & ".jpg" ' <<==== ADD file extension, adjust if needed

    On Error Resume Next
    With OutMail
        .To = ToAddress
        .CC = ccAddress
        .BCC = ""
        .Subject = "Medical Insurance Renewal."
      .Attachments.Add MyPath & pic, 1, 0 '<<==== Full path + file name + extension
       .HTMLBody = "<html><p></p>" & _
                "<img src='" & pic & " '" & "height=520 width=750>" '<<==== FILE NAME only
                        
      .Display
    On Error GoTo 0

 ' Try to send
    On Error Resume Next
  
    Set OutMail = Nothing
    Set OutApp = Nothing

End With
End Sub
 
Upvote 0
Solution
Sequoyah said:
Hi countryfan_nt
try this code, pay attention to the comments I added in the macro
VBA Code: 
Sub SENDMAIL2()
 'https://www.mrexcel.com/board/threads/excel-vba-cant-add-image-to-body-of-outlook-msg.1225426/
 
Dim rng As Range
Set rngT = Sheets("staff").Range("A1:D1")
' Only send the visible cells in the selection.
Dim pic As String

LR = Sheets("db").Range("H1").Value
Set rng = Sheets("staff").Range("A1:D17118").SpecialCells(xlCellTypeVisible)

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

'Adjust this according to your table gets the last row in the Sheet named "DB" column "A"
    ToAddress = Sheets("DB").Range("f1").Value
MyPath = "C:\Users\nawaf.altaher\Desktop\Labs\" ' <<==== SEPARATE path from File name
pic = Sheets("DB").Range("J1").Value & ".jpg" ' <<==== ADD file extension, adjust if needed

    On Error Resume Next
    With OutMail
        .To = ToAddress
        .CC = ccAddress
        .BCC = ""
        .Subject = "Medical Insurance Renewal."
      .Attachments.Add MyPath & pic, 1, 0 '<<==== Full path + file name + extension
       .HTMLBody = "<html><p></p>" & _
                "<img src='" & pic & " '" & "height=520 width=750>" '<<==== FILE NAME only
                       
      .Display
    On Error GoTo 0

 ' Try to send
    On Error Resume Next
 
    Set OutMail = Nothing
    Set OutApp = Nothing

End With
End Sub
Click to expand...
Now that's awesome! Really appreciate it, btw your description and comments made ONE huge difference!
 
Upvote 0
Hi,
Can you please send the Excel sheet ?
What was on the DB sheet and the Staff sheet ? The picture you wanted to send by email is on what range ?
I'm having kinda the same issue but still have not found a solution yet.

Thank you :)
 
Upvote 0
Accalio07 said:
Hi,
Can you please send the Excel sheet ?
What was on the DB sheet and the Staff sheet ? The picture you wanted to send by email is on what range ?
I'm having kinda the same issue but still have not found a solution yet.

Thank you :)
Click to expand...
Hi share with me your email address privately
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA to save file to SharePoint and attach to outlook template saved in SharePoint
Replies
1
Views
65
Micron
M
S
  • Question Question
VBA sending an email of two conditions are met
Replies
5
Views
137
mumps
mumps
K
  • Solved
Macro : Email Worksheet As PDF
Replies
3
Views
419
Domenic
D
O
  • Question Question
Copy Range into Email Body as chart
Replies
0
Views
202
O177812
O
J
  • Question Question
VBA code to create an email not importing names in "To, CC, or BCC"
Replies
3
Views
336
Alex Blakenburg
A

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

Share this page

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