Solved

Excel VBA send email - not working

Posted on 2015-01-29
10
1,944 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 48

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 48

Assisted Solution

by:Martin Liss
Martin Liss earned 250 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 48

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

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

631 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