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
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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Don't know why code does not copy?

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
.
This is the macro I use :

Code: 
Option Explicit


Sub EmailWithOutlook()
    Dim oApp As Object
    Dim oMail As Object
    Dim WB As Workbook
    Dim FileName As String
    Dim wSht As Worksheet
    Dim shtName As String
    Dim ThisFile As String
    Dim oByValue
    
    ThisFile = "C:\Users\My\Desktop\gun_maintenance.pdf"    '<-- This line is required to add a document as attachment to email.
                                                            '<-- change path as required
                                                            
    Application.ScreenUpdating = False


    'Create and show the Outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    With oMail
        
        'I need this to pull the "to" from B1
        .To = Sheets("Sheet1").Range("B1").Value                          '<--- Settings Sheet name here is Sheet1. Change as needed
        
        'I need this to pull the "Subject" from Settings:A2
        .Subject = Sheets("Sheet1").Range("B2").Value                       '<--- Settings Sheet name here is Sheet1. Change as needed
        
        'I need this to pull the "body" from Settings:B3
        .Body = Sheets("Sheet1").Range("B3").Value                          '<--- Settings Sheet name here is Sheet1. Change as needed
        
        .Attachments.Add ThisFile   '<-- This line is required to add a document as attachment to email.
        
        'NEXT LINE: add the image in hidden manner, position at 0 will make it hidden. This line required for image to show.
        .Attachments.Add "C:\Users\My\Desktop\pics\Bear-stuffs.jpg", oByValue, 0
    
        'Now add it to the Html body using image name
        'change the src property to 'cid:your image filename'
        'it will be changed to the correct cid when its sent.
        .HTMLBody = .HTMLBody & "<br><B>Embedded Image:</B><br>" _
                    & "<img src='cid:Bear-stuffs.jpg'" & "width='375' height='486'><br>" _
                    & "<br>Best Regards, <br>Your name here</font></span>"
        .Display
    End With


    'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub
 
Upvote 0
Ref: Logit;4902238.

Thank you for your reply, I have added my references to your code and all is fine except the picture appears as an Attachment to the email - I am looking to add the picture to the email body.

Kind Regards Brian A
 
Upvote 0
Thank you again, I have managed merge both codes to achieve my aim, I will post when I have fully tested for information if you would like.
Note updated link not functioning this end

Kind Regards
Brian A
 
Upvote 0
.
One last try for the download :

https://www.amazon.com/clouddrive/share/sLB2wClyqeKS8qtHcNAVFS60VXbEn5iTewcz6dTopAx

You'll need to copy and paste into your browser, instead of simply clicking on the link. I think maybe the FORUM is acting up ? I've noticed it is extremely
slow here. I tried both previously provided links here and they work on this end. Sorry for the confusion.

If you've managed to get your project working with the supplied macro code, please do post for review and use by others. Very helpful.
 
Upvote 0
.
This is REALLY frustrating when the FORUM won't cooperate. I'm having all kinds of difficulty here now. But I refuse to let it win.

https : //www.amazon.com/clouddrive/share/rcH47PRDVrdeT2MBXGtY5AUythgyJ9datwkGREjmuaA

Again, the above is the url to the download. I've placed an extra space before and after the colon symbol : . Hopefully this will not allow it to become
a clickable link again. Obviously if this works, you'll need to copy/paste into your browser then remove the two extra spaces before pressing ENTER.

:eeek:
 
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