Was Email sent or cancelled?

[MJAmici]

I am not an experienced programmer but I need to resolve a problem I am having with an Excel Office 2003 VBA Macro.

I have some code (see below) that will send an email just fine while avoiding the Outlook security message. Unfortunately, our GAL is somewhat messed up, so if the code doesn't get a clean address is stops the email and presents the GAL asking the user to pick the address they want to send. All this is okay, but I can't tell under this circumstance if the user sent the email or pressed the x button to close the window.

So, I need to do one of two things. Either control the window closing like with Thisworkbook before_close, OR an IF THEN test to determine if the email was sent.

I really hope some one can help, and thank you in advance.

Michael

Dim OutApp As Object
Dim outmail As Object

Set OutApp = CreateObject("Outlook.Application")
Set outmail = OutApp.CreateItem(0)

With outmail
.To = "email@address.com"
.CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
.Display
'work around for the Outlook security warning message
Application.Wait (10)
SendKeys "^{Enter}", 5
End With

Set outmail = Nothing
Set OutApp = Nothing

 

[CautionMP]

MJAmici,
Try this

...
End With

If outmail.sent Then
   'The message was sent
Else
   'The message was not sent, something should be done
End If

Set outmail = Nothing
...

The Sent property should return true or false depending on whether the message was sent.

Hope this helps,
CMP

 

[MJAmici]

Hi,

Thanks for replying. I tried the code but it always comes back 'false' whether I send or not. Any ideas?

Thanks again.

Michael

 

[CautionMP]

It might be beacuse you never 'send' the message, try adding

...
Application.Wait (10)
SendKeys "^{Enter}", 5
[i].Send[/i]
End With
...

We (my organization) rolled back the Outlook security warning update so I cannot recreate exactly what is happening with your code so I cannot test this to see if it will work.

Since the code still maintains a pointer to the message (outmail) I believe the code will still be able to send the message, even if the user chose to cancel the message window.

Then again, if the email address is bad, it could create and endless loop that the user will never be able to escape, thus signaling it is time for both the user and the developer to go home for the day.