Outlook Mail Warning from Excel

[breakoutfoo]

Hi,

Is it possible to return the Yes/No result of the outlook mail warning to excel VBA so that I may display a 'Sent' or 'Not sent' message to the user?

Thanks

Andy

 

[Andrew Fergus]

Hi Andy

Assuming you are using code similar to that found here:
http://www.freevbcode.com/ShowCode.Asp?ID=159

If the user selects 'No' the code goes into error and you can trap that. On my PC I get an error number 287 (Application-defined or object-defined error). If the error is trapped then you can return a message saying the message wasn't sent, but if you don't get into the error section then you can put up an "all ok" message.

Following is a modified version of the code from the above link that shows my test:

Public Sub SendOutlookMail()
 
On Error GoTo errorHandler
 
Dim oLapp As Object
Dim oItem As Object
 
Set oLapp = CreateObject("Outlook.application")
Set oItem = oLapp.createitem(0)
 
With oItem
   .Subject = "Test subject"
   .To = "me@mydomain.com"
   .body = "Test message"
   .Send
End With
 
Set oLapp = Nothing
Set oItem = Nothing
 
MsgBox "The message was sent!", vbInformation, "Success!"
 
Exit Sub
 
errorHandler:
Set oLapp = Nothing
Set oItem = Nothing
If Err.Number = 287 Then
    MsgBox "The e-mail was not sent", vbCritical, "User error!"
Else
    MsgBox Err.Description, vbCritical, "Error : " & Err.Number
End If
 
End Sub

Andrew

 

[Mike Blackman]

Hi Both,

Not sure how much the code differs but I normally go for Ron de Bruins mailers found here and with these the user doesn't get the option to send the mail, it just goes. You don't get the "Another Application is trying to access Outlook" message.

Not sure if this helps or not

 

[Andrew Fergus]

Hi Mike

I too use third party software like 'Express Click Yes' and can confirm it works well (link : http://www.contextmagic.com/express-clickyes/).

Using such a solution was also my initial reaction but that isn't what the OP asked for......

Cheers
Andrew

 

[Mike Blackman]

Hi Andrew,

Not a problem, I was only speculating, I've not come across Express Click Yes before and have very minimal experience of using Ron's code but on the three or four times I've used it I've seen that you don't get that anoying box pop up.

I shall check your link out.

Cheers.