Solved Logging Outlook Errors in Access Table (1 Viewer)

pooldead

Registered User.
Local time
Today, 02:55
Joined
Sep 4, 2019
Messages
136
I am building a database that will send emails to users and their managers. I need to have code built in that will log information if Outlook receives an error while trying to send. I am in between beginner and intermediate skill levels with Access and VBA, so I'm not entirely sure where to begin. I've included my function below and would like to place this bit of code with the "On Error GoTo 0" line.

Code:
Public Function SendSDEmail(eSubject As String, eBody As String, eBody2 As String, eTo As String, eCC As String, eBCC As String, sig As String) As Boolean 'function to be used when sending emails out

    Dim OutApp As Object, OutMail As Outlook.MailItem
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    
    With OutMail
        .To = eTo
        .CC = eCC
        .BCC = eBCC
        .SentOnBehalfOfName = ""
        .Subject = eSubject
        .Display
        .HTMLBody = eBody & eBody2 & sig
        .Display
    End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! If you want to "catch" errors to log them, then I think the first thing you'd want to do is not use On Error Resume Next. Also, are you trying to catch the error happening in Outlook? If so, you may have to put your code there instead of in Access.
 

pooldead

Registered User.
Local time
Today, 02:55
Joined
Sep 4, 2019
Messages
136
Thanks for the welcome theDBguy! If my understanding is correct, then the errors would occur while Outlook tries to send the emails. If that is correct and I would need to add the code in Outlook, how would I go about sending that data to an Access table?

As I mentioned, I am fairly new to the more complex coding problems, so I apologize if this is a more simple answer than I realize.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
Thanks for the welcome theDBguy! If my understanding is correct, then the errors would occur while Outlook tries to send the emails. If that is correct and I would need to add the code in Outlook, how would I go about sending that data to an Access table?

As I mentioned, I am fairly new to the more complex coding problems, so I apologize if this is a more simple answer than I realize.
Hi. Just like you can send an email in Outlook from Access, you can also write to a table in Access from Outlook.
 

pooldead

Registered User.
Local time
Today, 02:55
Joined
Sep 4, 2019
Messages
136
Hi. Just like you can send an email in Outlook from Access, you can also write to a table in Access from Outlook.

Okay, that makes sense from a logical viewpoint, but how would I go about referencing the error message itself in the code? Is there a particular command?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
Okay, that makes sense from a logical viewpoint, but how would I go about referencing the error message itself in the code? Is there a particular command?
I'm afraid that might be an Outlook expert question, which I can't claim I am one. Good luck!
 

isladogs

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
Nor me...:D
However I recommend a different method of sending email direct from Access without using Outlook. This approach uses CDO (Collaborative Data Objects) and in my opinion is both simpler and more reliable as no external program is required.

Have a look at my CDO Email Tester example app to try it out https://www.access-programmers.co.uk/forums/showthread.php?t=293368
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:55
Joined
Feb 28, 2001
Messages
27,001
First, I concur with Colin's suggestion regarding CDO. As long as you don't need to do TOO much with the message before you send it, any errors in the CDO operation come back to your context.

Second, the only way to be sure with CDO is to have an error trap enabled in the same context as the code that implements the .Send operation that is the final step of sending a message. If you can trap the error message, you can use the Err object to find out what it was, either numerically or descriptively (or both). If you don't use a trap, there is the problem that you still can check for the Err object but there is no guarantee that what it is showing you at the moment is still synchronized with your thread. It could be a left-over error or it could be an intervening error.

What Colin didn't mention is that if your site has a really ambitious security staff, you might need to warn them that you will be sending SMTP mail from the MSAccess.EXE image, because some stateful firewalls can catch this and stop it. Using Outlook as an applications object, the .Send wouldn't be trapped as unusual because "everyone knows that Outlook sends SMTP mail."

I am not entirely sure, though, that an immediate error wouldn't be caught if you used an Outlook App Object from Access. I have received such errors in my Outlook-based mail app more than once.

And there is this to consider. If the transmission fails at the other end, if you used Outlook you get a returned (deferred) failure message but if you used CDO you will not have a way to receive such a deferred message. You would have to therefore "spoof" the sender address (or the "reply to" address) so that it would return to your Outlook mailbox anyway.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:55
Joined
Jul 9, 2003
Messages
16,245
Helen Fedemma has some nice examples of using Outlook from Access and Access from Outlook on her website here:-

https://www.helenfeddema.com/Code Samples.htm


Some of the stuff is a bit old, but I believe she updates it regularly m, so I think you should find something usable. Not sure she covers exactly what you want, but that's where I would start looking!

Sent from my Pixel 3a using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
@Doc
Hmmm...i'm not sure what you mean by do too much...
I've used CDO for almost 20 years to send some very complex emails including both plain text messages and HTML email with images, formatted text, hyperlinks and signature lines. It can also send emails to multiple addresses and have multiple attachments.
Furthermore you can specify the send address including using a no reply address.

In my apps that use CDO email, I include the option to send a copy to your own email address for reference.

I'm not therefore clear what CDO email can't handle. Do enlighten me!

@UG
I would also recommend Helen Feddema's website for Access to Outlook automation ... and much more. Unfortunately she doesn't seem to be actively updating her site any more and her excellent Access Archon newsletters stopped a few years ago.
 

pooldead

Registered User.
Local time
Today, 02:55
Joined
Sep 4, 2019
Messages
136
@Doc
Hmmm...i'm not sure what you mean by do too much...
I've used CDO for almost 20 years to send some very complex emails including both plain text messages and HTML email with images, formatted text, hyperlinks and signature lines. It can also send emails to multiple addresses and have multiple attachments.
Furthermore you can specify the send address including using a no reply address.

In my apps that use CDO email, I include the option to send a copy to your own email address for reference.

I'm not therefore clear what CDO email can't handle. Do enlighten me!

@UG
I would also recommend Helen Feddema's website for Access to Outlook automation ... and much more. Unfortunately she doesn't seem to be actively updating her site any more and her excellent Access Archon newsletters stopped a few years ago.

I will have to do some more investigating into CDO and see if it will work for my end goals. I'm certainly not opposed to something making things easier!

UG - thanks for the link. I will research through her articles and see if any give additional guidance.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:55
Joined
Feb 28, 2001
Messages
27,001
Colin, it is possible that the limitations we had were environmentally imposed. But certain things were not allowed in such a way that we were unable to send certain messages due to security issues. Have to remember that we were in a Secret site even though the machine we used was outside the Secret firewalls. I'll certainly agree that environment has a lot to do with utility of anything that potentially reaches outside of your site.
 

Users who are viewing this thread

Top Bottom