Sending email from excel

I have a code that will send my invoice from excel to outlook and send as a PDF email. The code works perfect except sometimes the email is stuck in outbox,could be number of reasons such as poor internet connection. My question is has anyone found a way to have a message pop up saying email sending then close the message after mail sent? Is there a way to put the number of outbox emails in a cell in excel. IF I could get the number in a cell might find a way for message box.Any suggestions on this?
Thanks

Hello zplugger,

You are a real opportunist!

What you are asking for, might be possible, with VBA Code longer than the full volume of an encyclopedia, and even then it may disappoint you. I suggest you pick up your lazy frozen fingers, and check the Status of your Emails.

You are still young enough after all, and the exercise will do you only good!

Now, go and check your Emails, dammit!

Regards.

What if you just put messagebox after sending mail?
if it reach it then mail is sent, right?

And code will be stoped at that point so you will know.

Dear Mr. zplugger,

You Sir., appears to be consistent at plugging into issues so trivial that it amazes me that the Forum even allows your Posts.

To add to your demise and fruitless quest for a ready solution to your issue at hand, which I blatantly and on purpose refuse to give you the answer, please clean your glasses, put them on, turn on your Computer and access the Internet. If you can manage that, and have successfully done so, and if you are capable of concentrating any further, OUCH!, then please see this link: http://www.mrexcel.com/forum/excel-q...lications.html

Now, don't fall asleep yet, and get your Grandson to read and explain to you, what you have to do. Once Grandson have helped you sorting out that bit, rather get him to contact me, so I can guide him further, instead of me wasting my time with you.

Snoring already. We are very much the same!

Thank You again Mr. Winon
I will check out your link and inform you how I do in couple days. I know you must be younger then I, I can't stay up past 10. LOL

The more I think about it the more I think a macro in outlook to do job. Just a message box prompt after mail is sent and outbox is empty to close outlook.

I dare you! Spot the mistake in my post, above.

Mr. zplugger,

It is all in the mind, if you posses one, time here now is 01:33am, and I am committed to helping other people, so stop bothering me. How did you get pass the Ban of posting on this Forum anyway, you "Monster"

Now, don't fall asleep yet

My dear son, wait 'till you reach my age, perhaps only then you will understand that I am sleeping already!

"Gnight" my Friend.

Now that is really brilliant, if it is your own idea, without anyone else helping you!

But then again, off course you will deny any help,

How about this ?

Sub ReadEmail()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    On Error Resume Next
    With GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderOutbox)
        ReDim Data(.Items.Count + 1, 2)
        For i = 1 To .Items.Count + 1
            Data(i - 1, 0) = .Items(i).SenderEmailAddress
            Data(i - 1, 1) = .Items(i).Subject
        Next
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    Range("A1").Resize(, 2) = Array("FROM", "SUBJECT")
    Range("A2").Resize(UBound(Data, 1) + 1, UBound(Data, 2) + 1) = Data
End Sub

Thanks bakerman2

Ok I get From and Subject in A1, but nothing in A2. I know there is email in outbox

Did you set Reference to Outlook Library ?

How about this ?
Put the code in ThisWorkbook and before closing check will be performed.
If there's still some mail present messagebox will appear and workbook will not close, otherwise workbook closes normally.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next
    With GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderOutbox)
        ReDim Data(.Items.Count + 1, 2)
        For i = 1 To .Items.Count + 1
            Data(i - 1, 0) = .Items(i).SenderEmailAddress
            Data(i - 1, 1) = .Items(i).Subject
        Next
    End With
    If UBound(Data) > 1 Then
        MsgBox "There's still some unsent mail in OutBox !"
        Cancel = True
    End If

End Sub

I have to say this is almost perfect,think I will try and add a yes or no on closing in outbox has mail. Thank You very much for your help on this bakerman2

The more I play with this the better it gets,Outlook can be closed and it works.

You're welcome and thanks for adding rep points.

    If UBound(Data) > 1 Then
        MsgBox "There's still some unsent mail in OutBox !"
        Cancel = True
    Else
       If MsgBox("Close Outlook", vbYesNo) = vbYes Then GetObject("", "Outlook.Application").Quit
    End If

When no mail present you will be prompted to Close Outlook, when Yes clicked Outlook will close and workbook will close.
Otherwise both will stay open.

Try.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next
    With GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderOutbox)
        ReDim Data(.Items.Count + 1, 2)
        For i = 1 To .Items.Count + 1
            Data(i - 1, 0) = .Items(i).SenderEmailAddress
            Data(i - 1, 1) = .Items(i).Subject
        Next
    End With
    If UBound(Data) > 1 Then
        If MsgBox("There's still some unsent mail in OutBox !" & vbNewLine & _
                "Do you want to close workbook and Outlook anyway?", vbYesNo) = vbYes Then
            GetObject("", "Outlook.Application").Quit
            GoTo closeanyway
        Else
            Cancel = True
        End If
    End If
closeanyway:

End Sub

You're welcome again and glad we got it going.