Sending mail using Outlook where the Send method fails

Question

I use this code to send email from Excel:

Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2013
'This example send the last saved version of the Activeworkbook
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .to = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send ' <--------------------------------This is causing troubble
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

The problem is that .Send is not recognized as an object (or method).

Other commands are working (i.e. Display, Save).

I believe this error exists because of security systems at my work. I have even tried using CDO and it is not working ether.

I created a blank workbook, saved it as `C:\myDir\BlankBook.xlsx` and copied your code above into a code module, changing only the `.to` line to my internal work address. Code works with no problems for me. — Skip Intro, Jul 26 '13 at 13:57
Changed the `.to` line to my `gmail` address. Code worked fine, again. — Skip Intro, Jul 26 '13 at 14:00
Hmm strange, i have seen similar answers before. What could cause this to happen on some machines and not others? Both my computers at work is getting this error. — Ole Henrik Skogstrøm, Jul 26 '13 at 14:05
This code also works fine for me. Before your `.Send`, try making `OutApp.Visible = True`. You should not *need* to do this, but it can't hurt to try it. What is the specific error message you receive? — David Zemens, Jul 26 '13 at 14:20
I get the same error message on 'OutApp.Visible = True' and OutMail.Send. The error is: "Object doesn't support this property or method (Error 438)" — Ole Henrik Skogstrøm, Jul 26 '13 at 14:23
This is a bizarre error. You should be able to use late binding as per your code, but lets try early binding and see if error persists. Can you try enabling the reference to the the MS Outlook library? Then `Dim OutApp as Outlook.Application` and `Dim OutMail as MailItem`. — David Zemens, Jul 26 '13 at 14:26
Umm using this configuration i get an error in Set OutMail = OutApp.CreateItem(0). Do you know why? The error is "Object variable not set (Error 91)" — Ole Henrik Skogstrøm, Jul 26 '13 at 14:34
maybe you can post your original code? if you're using the exact code above, I don't think you should see any errors because you have `On Error Resume Next` near the top of your code? (other please correct me if i'm wrong) — Jaycal, Jul 26 '13 at 14:47
Early binding per my previous comment works as expected on my computer. I don't know any reason why that would fail with the `91` error unless you neglected to `Set OutApp = CreateObject("Outlook.Application")` — David Zemens, Jul 26 '13 at 17:56
What version of Outlook? Try `Debug.Print TypeName(OutMail)` or set a watch on `OutMail`: is the correct type of object being created? — Tim Williams, Jul 26 '13 at 17:56
@TimWilliams the `CreateItem(0)` method will create a `MailItem`; that constant `0` should be valid for Outlook through 2013. — David Zemens, Jul 26 '13 at 17:57
@DavidZemens: I realize it should - just wanted to confirm it actually does... — Tim Williams, Jul 26 '13 at 18:07
In my tests I had the same error unless Outlook was open. Do you have multiple Outlook profiles configured? Is one of them set to open as default and does it have a mail account configured? — HK1, Jul 29 '13 at 13:11
Hi, I'm sorry i haven't responded before. Didn't have my work computer before now. I believe this error exists because of security systems at my work. I have even tried using CDO and it is not working ether: http://bytes.com/topic/access/answers/847367-cdo-message-gmail-doesnt-send-attachment — Ole Henrik Skogstrøm, Aug 05 '13 at 07:19

score 4 · Accepted Answer · answered Aug 31 '13 at 14:50

4

Change .Send to .Display and put SendKeys "^{ENTER}" before the With OutMail line.

answered Aug 31 '13 at 14:50

Tim Richards

324
1
3
18

Thank you! A nice workaround :) I edited your answer to reflect what solved my problem. Hope that is ok for you?:) – Ole Henrik Skogstrøm Sep 03 '13 at 08:48
no worries, glad you got it worked out in the end my friend :-) – Tim Richards Sep 05 '13 at 18:46

score -1 · Answer 2 · answered Mar 22 '14 at 21:52

Try this code.

Sub Email_ActiveSheet_As_PDF()

'Do not forget to change the email ID
'before running this code

Dim OutApp As Object
Dim OutMail As Object
Dim TempFilePath As String
Dim TempFileName As String
Dim FileFullPath As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

' Temporary file path where pdf
' file will be saved before
' sending it in email by attaching it.

TempFilePath = Environ$("temp") & "\"

' Now append a date and time stamp
' in your pdf file name. Naming convention
' can be changed based on your requirement.

TempFileName = ActiveSheet.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"

'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName

'Now Export the Activesshet as PDF with the given File Name and path

 On Error GoTo err
With ActiveSheet
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=FileFullPath, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
End With

'Now open a new mail

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

On Error Resume Next
With OutMail
.To = StrToReceipent
.CC = StrCCReceipent
.BCC = StrBCCReceipent
.Subject = StrSubject
.Body = StrBody
    .Attachments.Add FileFullPath '--- full path of the pdf where it is saved
    .Send   'or use .Display to show you the email before sending it.
    .Display
End With
On Error GoTo 0

'Since mail has been sent with the attachment
'Now delete the pdf file from the temp folder

Kill FileFullPath

'set nothing to the objects created
Set OutMail = Nothing
Set OutApp = Nothing

'Now set the application properties back to true
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
MsgBox ("Email has been Sent Successfully")
Exit Sub
err:
    MsgBox err.Description

End Sub

Sending mail using Outlook where the Send method fails

2 Answers2

Linked

Related