By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Logging an E-mail That has been sent via Access

100+
P: 171
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
Expand|Select|Wrap|Line Numbers
  1. Dim strEmail As String
  2. Dim strMsg As String
  3. Dim oLook As Object
  4. Dim oMail As Object
  5. Set oLook = CreateObject("Outlook.Application")
  6. Set oMail = oLook.CreateItem(0)
  7. With oMail
  8. .To = "iheartvba@vbassocool.com"
  9. .Subject = "HO Report From Office Number " & strOfficeID & " From " & (Me.cboStartDate + 1) & " To " & (Me.tbEndDate - 1)
  10. .Attachments.Add ("C:\HoXpt\qryXptTblChangeLogIMP.txt")
  11. .Attachments.Add ("C:\HoXpt\qryXptTblClientAddressesIMP.txt")
  12. .Attachments.Add ("C:\HoXpt\qryXptTblClientDetailsIMP.txt")
  13. .Attachments.Add ("C:\HoXpt\qryXptTblClientDetailsUPD.txt")
  14. .Attachments.Add ("C:\HoXpt\qryXptTblCollectionsIMP.txt")
  15. .Attachments.Add ("C:\HoXpt\qryXptTblCollectionsUPD.txt")
  16. .Attachments.Add ("C:\HoXpt\qryXptTblFFRBankDetailsIMP.txt")
  17. .Attachments.Add ("C:\HoXpt\qryXptTblFFRBkChangeLogIMP.txt")
  18. .Attachments.Add ("C:\HoXpt\qryXptTblFFRChangeLogIMP.txt")
  19. .Attachments.Add ("C:\HoXpt\qryXptTblFFRIMP.txt")
  20. .Attachments.Add ("C:\HoXpt\qryXptTblJobDetailsIMP.txt")
  21. .Attachments.Add ("C:\HoXpt\qryXptTblJobDetailsUPD.txt")
  22. .Attachments.Add ("C:\HoXpt\qryXptTblJobPeriodsChangeLogIMP.txt")
  23. .Attachments.Add ("C:\HoXpt\qryXptTblJobPeriodsIMP.txt")
  24. .Attachments.Add ("C:\HoXpt\qryXptTblPendingItemsIMP.txt")
  25. .Attachments.Add ("C:\HoXpt\qryXptTblPendingsChangeLogIMP.txt")
  26. .Attachments.Add ("C:\HoXpt\qryXptTblTimeCardIMP.txt")
  27. .Attachments.Add ("C:\HoXpt\qryXptTblVoidIMP.txt")
  28. .Attachments.Add ("C:\HoXpt\qryXptTblYearsPerReceiptChangeLogIMP.txt")
  29. .Attachments.Add ("C:\HoXpt\qryXptTblYearsPerReceiptIMP.txt")
  30. .Send
  31. End With
  32. Set oMail = Nothing
  33. Set oLook = Nothing
  34. DoCmd.OpenQuery "qryAppXptDt"
  35. MsgBox "Head Office Report Has Been Exported and E-mailed For The Period" & vbCrLf & (Me.cboStartDate + 1) & vbCrLf & "to" & vbCrLf & (Me.tbEndDate - 1)
  36. End Sub
  37.  
Nov 24 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 374
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.
Nov 24 '08 #2

Expert 100+
P: 374
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.
Nov 24 '08 #3

100+
P: 171
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

Expand|Select|Wrap|Line Numbers
  1. 'strSubject is the Subject of the e-mail
  2. 'olkSentItems is Outlook Sent Items table linked to Access
  3. 'Subject is the subject field in the linked outlook table 
  4. strSql = "SELECT Subject FROM olkSentItems WHERE Subject = '" & strSubject & "'"
  5. rst.Open "strSql", cnn, adOpenDynamic, adLockOptimistic
  6.  
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
Nov 24 '08 #4

100+
P: 171
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
Nov 27 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.