Logging an E-mail That has been sent via Access

[iheartvba]

Hi I have been able to successfully send e-mails using MS Outlook via MS Access. However I can't seem to find any error logging techniques. What I want to do is create a log of any e-mails actually sent. I have no way of knowing whether an e-mail has or has not been sent, e.g. Line 34 in the below code will run regardless of whether the e-mail has been sent or not.


Thank You

P.S. Just for reference here's my code

Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
With oMail
.To = "iheartvba@vbassocool.com"
.Subject = "HO Report From Office Number " & strOfficeID & " From " & (Me.cboStartDate + 1) & " To " & (Me.tbEndDate - 1)
.Attachments.Add ("C:\HoXpt\qryXptTblChangeLogIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblClientAddressesIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblClientDetailsIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblClientDetailsUPD.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblCollectionsIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblCollectionsUPD.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblFFRBankDetailsIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblFFRBkChangeLogIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblFFRChangeLogIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblFFRIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblJobDetailsIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblJobDetailsUPD.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblJobPeriodsChangeLogIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblJobPeriodsIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblPendingItemsIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblPendingsChangeLogIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblTimeCardIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblVoidIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblYearsPerReceiptChangeLogIMP.txt")
.Attachments.Add ("C:\HoXpt\qryXptTblYearsPerReceiptIMP.txt")
.Send
End With
Set oMail = Nothing
Set oLook = Nothing
DoCmd.OpenQuery "qryAppXptDt"
MsgBox "Head Office Report Has Been Exported and E-mailed For The Period" & vbCrLf & (Me.cboStartDate + 1) & vbCrLf & "to" & vbCrLf & (Me.tbEndDate - 1)
End Sub

[PianoMan64]

Hey iheartvba,

There are a few ways you can go about this:

1. You can simply create a table that when you issue the send command, that it will create a record in a table that indicates that a particular email was sent, based off of any information that you had in the objects of your email.

2. You can simply connect using ODBC to the sent folder of your local outlook client and verify that an email went out with a specific email address and so on.

It's really all up to how you want to track it, and where you want to check for errors.

[PianoMan64]

Hey iheartvba,

There are a few ways you can go about this:

1. You can simply create a table that when you issue the send command, that it will create a record in a table that indicates that a particular email was sent, based off of any information that you had in the objects of your email.

2. You can simply connect using ODBC to the sent folder of your local outlook client and verify that an email went out with a specific email address and so on.

It's really all up to how you want to track it, and where you want to check for errors.

Hope that helps,

Joe P.

[iheartvba]

Solution 2; SQL Error

Thanks Joe.

I don't know how Solution 1 works so I went with Solution 2. I am encountering the follwoing error: Run-Time error '-2147217900 (80040e14)': Invalid SQL statement; expectde 'DELETE', 'INSERT', 'PROCEEDURE', 'SELECT' or 'UPDATE'.

My SQL string is as Follows

'strSubject is the Subject of the e-mail
'olkSentItems is Outlook Sent Items table linked to Access
'Subject is the subject field in the linked outlook table 
strSql = "SELECT Subject FROM olkSentItems WHERE Subject = '" & strSubject & "'"
rst.Open "strSql", cnn, adOpenDynamic, adLockOptimistic

Line 5 of the above code is where the error occurs

P.S. i don't know if this is relevant but, The objective of this code is to see if the record set is empty, if so then the user will be prompted to rerun the proceedure, otherwise the date of the sent e-mail will be logged in a table. I should not have too much of a problem with that once this SQL string starts working.

Thank You

[iheartvba]

Hi,
Just learned that you can get the row count of a list box using the listCount function. So I have just created a list box with the query listed in Post #4 above and if the listCount function gives a value greater than zero it means the e-mail has been sent and therefore access can run a query to Log that fact!

yeah