Automatically update a query based on sent emails

[A1CLowe]

I have a project that helps me track overdue training and notify the members when their training shows up on my overdue query. The query is pretty simple with only Name, Last Training, Due Date and Email fields. The code used is pulled from "send automatic email" by Blues66 and tweaked just the way I need it. However, what I would like is for it to update another record set that logs when and who the emails were sent to. Something like Name (Field 0) Email (Field 1) Contents (Field 2) I've seen something like this in another em ailer code, but this code was a bit out there for me as I'm pretty new to access. If someone can point me in the right direction that would be great. Thanks in advance!

p.s. I am using Access 2007 on my workstation. I also cannot post the link to the code I use yet. Just search for that post. It's from this forum.

 

[A1CLowe]

I decided to just copy and paste the code here rather than have someone look it up. I also cannot post my database because it is 5MB max filesize is 2MB.

Private Sub Command81_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Overdue", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(3)) = False Then
sToName = .Fields(3)
sSubject = "HOT!!! Overdue AO/RO Col Training!: " & .Fields(2)
sMessageBody = "Email Body Text " & vbCrLf & _
"Name: " & .Fields(0) & vbCrLf & _
"Last Training: " & .Fields(1) & vbCrLf & _
"Due Date: " & .Fields(2)

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

 

[A1CLowe]

I ended up learning myself of how to mix both of the codes together. It was alot easier than I thought. :banghead: Here's what I have. If you need help with putting this in your own project look up the original post I mentioned in my first post by Blues66.

Private Sub Command81_Click()
Dim stSQL As String
Dim MyDb As DAO.Database
Dim Rs As DAO.Recordset
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
'Query definition to capture sent email contents
stSQL = "SELECT * FROM tblEmailSent;"
Set MyDb = CurrentDb()
Set Rs = MyDb.OpenRecordset(stSQL)
Set rsEmail = MyDb.OpenRecordset("Overdue", dbOpenSnapshot)
 
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(3)) = False Then
sToName = .Fields(3)
sSubject = "HOT!!! Overdue AO/RO Col Training!: " & .Fields(2)
sMessageBody = "Email Body Text " & vbCrLf & _
"Name: " & .Fields(0) & vbCrLf & _
"Last Training: " & .Fields(1) & vbCrLf & _
"Due Date: " & .Fields(2)
 
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With
'Store the email contents in table
With Rs
.AddNew
Rs![DateSent] = Now
Rs![Recipients] = sToName
Rs![Subject] = sSubject
Rs![Contents] = sMessageBody
.Update
End With
'Clean up
Set Rs = Nothing
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

Hope this helped!!
I'll also answer any questions that I can for you with this code to help you in incorporating this into your own DB.