Use VBA Code Mail via Outlook

B

Brian R Armstrong

Board Regular
Joined
Jun 5, 2007
Messages
92
Appreciate some help please - I use the code below to email from Excel to Outlook, which works fine.

I would like to add a Picture to the message and I added 2 lines (2) marked xxxx
I now get an error message

Some error occurred 438
Object doesn't support this property or method.

Can anybody spot my error or amend code if possible
Excel 2016, Windows 10

Many Thanks Brian A


Option Explicit
Sub Mail_with_outlook2() ' use this

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

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

strto = Cells(FormulaCell.Row, "j").Value 'email address
strcc = " "
strbcc = ""
strsub = "XXXXXX"
strbody = "Hello " & Cells(FormulaCell.Row, "B").Value & vbNewLine & vbNewLine & _
"Some txt ……………………..." & vbNewLine & vbNewLine & _
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody

xxxx 'Add file to the email
xxxx .Picture.Add ("C:\Users\Main User\Documents\ROLL-MEMBER.jpg - photos")

.Display ' or use .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Use this syntax for a file or image:

Code: 
.Attachments.Add ("C:\Users\mmickle1\Pictures\Bugs_Bunny.png")

If you want to attach current workbook you could use:

Code: 
  .Attachments.Add ActiveWorkbook.FullName
I just tested it. Seems to be working.
 
Upvote 0
Matt, thank you for your reply, works as an attachment, I would prefer a picture following the email text.

Is this possible

Kind Regards Brian A
 
Upvote 0
.htmlBody = "< html>< p>This is a picture.< /p>" & _
"< img src='C:\Users\mmickle1\Pictures\Bugs_Bunny.png' height=480 width=360>"

I have added an extra space after "<" this needs to be removed for it to work properly....
 
Last edited:
Upvote 0
mrmmickle1 said:
.htmlBody = "< html>< p>This is a picture.< /p>" & _
"< img src='C:\Users\mmickle1\Pictures\Bugs_Bunny.png' height=480 width=360>"

I have added an extra space after "<" this needs to be removed for it to work properly....
Click to expand...

Matt, added new line, now get blank picture?, path appears to be correct
 
Upvote 0
Not sure what the issue would be. I tested with a picture local to my User Profile and sent it to a co-worker. He received the full picture. Not a blank outline or broken .jpg link.
 
Upvote 0
Matt thank you for your continued interest in this appreciate if you can look the code I am using to see if you can spot my error.
I have add the line marked xx

Sub Mail_with_outlook2()

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
xx Dim htmlbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strto = Cells(FormulaCell.Row, "j").Value 'email address
strcc = " " 'email address
strbcc = ""
strsub = "Loyalty Club"
strbody = "Hello " & Cells(FormulaCell.Row, "B").Value & vbNewLine & vbNewLine & _
"Thank you for…………………………... & vbNewLine & vbNewLine

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody

'Add a file to the mail??

xx htmlBody = "<html><p>This is a picture.</p>" & _
xx "<img src='C:\Users\Main User\Documents\Voucher 1'png' height=480 width=360>"

.Display 'Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub


Again many thanks fro your time

Brian A
 
Upvote 0
I would like to solve this with your help
I am trying to add a picture to the body of an email
My simplified code, as follows runs to routine but no picture is shown in the email body
I have tried several file names \paths without success

Can anybody see the error? appreciate if you can

Kind Regards Brian A

Option Explicit
Sub Mail_with_outlook2() ' use this

Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Dim htmlbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strto = ""
strcc = ""
strbcc = ""

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = ""
htmlbody = "<img src='C:\Users\Main User\Pictures\Screenshots\Screenshot (2)'png' height=480 width=360>"

.Display ' or use .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 
Upvote 0
Sorry to keep on with this, but I really would like to solve this.

I have located 2 vba codes to send emails from my Worksheet, I send 20 – 30 emails per week - Both send emails but not as I would like.
The first code attaches a picture in the email body, but does not fill the email "to" or "Subject".
The second code attaches a Picture as an Attachment and completes the "to" "subject" details.
I am trying to combine the two codes to achieve Fill the To, Subject, cc etc and add a Picture to the email body i.e. not an Attachment.
Appreciate if anyone can help please
Brian A

Code: 
Sub EmbedPicture()
    Const MyPath = "C:\Users\Main User\pictures\"
    Const MyPicture = "Tesr.png"
    With CreateObject("Outlook.Application").CreateItem(0)
        .Attachments.Add MyPath & MyPicture
        .htmlbody = "<html><p>This is a picture</p>" & _
        "<img src=cid:" & Replace(MyPicture, " ", "%20") & " height=290 width=580>" & _
        "<p>Best Regards,</p>"
        .Display
    End With
End Sub

Code: 
Sub Mail_with_outlook2()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim htmlbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strto = Cells(FormulaCell.Row, "j").Value 'customer email address
    strcc = "" ' copy email to 1
    strbcc = "" ' copy email to 2
    strsub = "The Roll Call - Loyalty Club" 'email title
    strbody = "Hello " & Cells(FormulaCell.Row, "B").Value & vbNewLine & vbNewLine & _
    "Thank you for .............................." & vbNewLine & vbNewLine & _
    "I'm delighted ....................................."  & vbNewLine & vbNewLine & "Make sure ........................." _
    & vbNewLine & vbNewLine & "Best Wishes" & vbNewLine & vbNewLine & "UserName"_
    & vbNewLine & vbNewLine
    
    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Attachments.Add "C:\Users\Main User\Pictures\Tesr.png" ' - photos"
       
    .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 
Upvote 0
Resending code
Code: 
Sub EmbedPicture()
    Const MyPath = "C:\Users\Main User\pictures\"
    Const MyPicture = "Tesr.png"
    With CreateObject("Outlook.Application").CreateItem(0)
        .Attachments.Add MyPath & MyPicture
        .htmlbody = "<html><p>This is a picture</p>" & _
        "<img src=cid:" & Replace(MyPicture, " ", "%20") & " height=290 width=580>" & _
        "<p>Best Regards,</p>"
        .Display
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Add Outlook "Confidential" Sensitivity Label
Replies
2
Views
201
Aviles
A
S
  • Question Question
VBA sending an email of two conditions are met
Replies
5
Views
285
mumps
mumps
H
  • Question Question
Macro to email Report
Replies
2
Views
588
howard
H
C
  • Solved
Adding an editable pop-up
Replies
3
Views
640
ChaosPup
C
S
  • Solved
Tweak to VBA email code
Replies
3
Views
806
rollis13
rollis13

Forum statistics

Threads
1,224,755
Messages
6,180,751
Members
452,996
Latest member
nelsonsix66

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