Solved

Excel VBA send email - not working

Posted on 2015-01-29
10
1,338 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
  • 5
  • 4
10 Comments
 
LVL 46

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 46

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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 46

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 29

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 46

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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