Solved

Excel VBA send email - not working

Posted on 2015-01-29
10
963 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 45

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 45

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
 

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 45

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 45

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now