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
 
Sort by date Sort by votes

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I am trying to set the FONT on email sent via this code

The font size element works and the Font face element sets the email (Outlook) font style but not the body text?

I have searched and tried many variations without success - is there a simple solution

Kind Regards Brian A

Code: 
Sub EmbedPicture()
    Const MyPath = "C:\Users\Main User\pictures\"
    Const MyPicture = "Voucher1.png"
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    
    With CreateObject("Outlook.Application").CreateItem(0)
    strto = "Test1" 'email address
    strsub = "Test 2"
    .To = strto
    .Subject = strsub
        .htmlbody = "<html><b><font face=Blackadder ITC><font size=2><p>Bill and Ben</p>"
        
        .Display
        
    End With
End Sub
 
Upvote 0
Hope this is better]]BrianA
Sub EmbedPicture()
Const MyPath = "C:\Users\Main User\pictures"
Const MyPicture = "Voucher1.png"
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String

With CreateObject("Outlook.Application").CreateItem(0)
strto = "Test1" 'email address
strsub = "Test 2"
.To = strto
.Subject = strsub
.htmlbody = "<html><b><font face=Blackadder ITC><font size=2><p>Bill and Ben</p>"

.Display

End With
End Sub
 
Upvote 0
.
Code: 
Option Explicit


Sub EmbedPicture()
    'Const MyPath = "C:\Users\Main User\pictures\"
    'Const MyPicture = "Voucher1.png"
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    
    strbody = "" & _
          "This is a test." & "

" & _
          "Bill and Ben" & _
          ""


    
    With CreateObject("Outlook.Application").CreateItem(0)
    strto = "Test1" 'email address
    strsub = "Test 2"
    .To = "Test@yahoo.com"
    .Subject = "HTML"
    .htmlbody = strbody
    .Display
    End With


End Sub

Do a search for " vba macro html adjust font "
 
Last edited:
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,754
Messages
6,180,749
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