Excel VBA send email - not working

Hi experts,

I'm using the code below to try to send an email using VBA code. And the display function works fine, but when I change the code to .Send, the sub finishes and never sends the email. I'm working on a company laptop for a big company. Do you think they have security settings in outlook that are preventing the message to send?

Sub Mail_workbook_Outlook_1()
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    SendKeys "^{ENTER}"
    With OutMail
        .to = "my work email address"
        .CC = ""
        .BCC = ""
        .Subject = "Test email for report"
        .Body = "Test email."
        .Attachments.Add ActiveWorkbook.FullName
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
JC_LivesAsked:
Who is Participating?
 
JC_LivesConnect With a Mentor Author Commented:
I can't attach because of the confidentiality, but thanks to both of you for your help.

I was able to make it send by putting this line after .Display:

        SendKeys "^{ENTER}"

I guess this is a workaround to bypass whatever security settings were preventing the code from sending the email.

Thanks again.
0
 
Martin LissOlder than dirtCommented:
Comment out the On Error Resume Next and see if you get an error.
0
 
JC_LivesAuthor Commented:
Yes - some kind of error pops up and then disappears, and I can't see what the error is. It highlights the .Send line in yellow.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Martin LissConnect With a Mentor Older than dirtCommented:
Try this and then you should be able to determine the error.

Sub Mail_workbook_Outlook_1()
    Dim OutApp As Object
    Dim OutMail As Object

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

    'On Error Resume Next
    On Error GoTo Error_Routine

    SendKeys "^{ENTER}"
    With OutMail
        .to = "my work email address"
        .CC = ""
        .BCC = ""
        .Subject = "Test email for report"
        .Body = "Test email."
        .Attachments.Add ActiveWorkbook.FullName
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    Exit Sub

Error_Routine:

    Msgbox "Error " & err.number & " " & err.description
End Sub

Open in new window

0
 
JC_LivesAuthor Commented:
Ok, got the error message!

Error 287 Application-defined or object-defined error

(I also commented out the line SendKeys "^{ENTER}", I think it was pressing enter on the error message)
0
 
Martin LissOlder than dirtCommented:
If you aren't aware, your code originally comes from Ron deBruin's site. I have used it frequently without error so I don't know why you are having a problem.

Let me also suggest that you look at my article on debugging. It might help you find out what's going on.
0
 
JC_LivesAuthor Commented:
You're giving up!? How am I supposed to fix this by reading some debugging article if you can't.

Dear experts, this question is still open, thanks so much if you can help.
0
 
gowflowConnect With a Mentor Commented:
Well you have 3 errors there

1) first one it is this
SendKeys "{ENTER}"

not this
SendKeys "^{ENTER}"

2) Second you are attaching the current workbook that is opened you should perform a save prior to this instruction
.Attachments.Add ActiveWorkbook.FullName

3) and most important is an error generated by the mere fact that the TO is not an email address
.to = "my work email address"

If your replace your .send by .display you should get the email pop-up infront of you.

This code should work
Sub Mail_workbook_Outlook_1()
     Dim OutApp As Object
     Dim OutMail As Object

     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)
     ActiveWorkbook.Save
     'On Error Resume Next
     SendKeys "{ENTER}"
     With OutMail
         .to = "my work email address"
         .CC = ""
         .BCC = ""
         .Subject = "Test email for report"
         .Body = "Test email."
         .Attachments.Add ActiveWorkbook.FullName
         If InStr(1, .to, "@") = 0 Then
            .display
         Else
            .send
         End If
     End With
     'On Error GoTo 0

     Set OutMail = Nothing
     Set OutApp = Nothing
 End Sub

Open in new window



gowflow
0
 
Martin LissOlder than dirtCommented:
You're giving up!? How am I supposed to fix this by reading some debugging article if you can't.
Well I can't debug it because I don't have your project.  So what I'm doing, if I may paraphrase a well-known proverb is to teach you how to debug so you will know how to debug for a lifetime.

If you attach your project files maybe I can reproduce the error.
0
 
JC_LivesAuthor Commented:
The experts' answers didn't solve my problem which was I needed the code to send an email. I shared a solution with my comment. But the expert comments did help, so my thanks to both of them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.