Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA send email - not working

Posted on 2015-01-29
10
Medium Priority
?
2,390 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:JC_Lives
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40577500
Comment out the On Error Resume Next and see if you get an error.
0
 

Author Comment

by:JC_Lives
ID: 40577527
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
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 1000 total points
ID: 40577550
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:JC_Lives
ID: 40577567
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40577617
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
 

Author Comment

by:JC_Lives
ID: 40577657
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
 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 1000 total points
ID: 40577791
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40577820
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
 

Accepted Solution

by:
JC_Lives earned 0 total points
ID: 40578113
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
 

Author Closing Comment

by:JC_Lives
ID: 40585854
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question