I have code that is sending an email then updating a table with the date/time the email was sent, it is sending the first record and looping the right number of times (there are 3 email messages to be sent so it sends 3) but it is repeating the data in the first record instead of moving to the next record. It is also logging in the tracking table that the message was sent 3 times but for the same record.
Any help is greatly appreciated:
Any help is greatly appreciated:
Code:
Private Sub cmdSendEmail_Approval_Click()
'LOOP STATEMENT
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryEmail_Approval_ALL")
Dim MyTrackingTable As Recordset
Set MyTrackingTable = CurrentDb.OpenRecordset("tblTerminationData_ActionsCompleted")
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rs.EOF = True
'LOOPING ACTION - SEND EMAIL
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
'**************************************************************
'*create string with email address
strID = SelectedTermination
strEmail = strTo
strBody = strConsolidatedBody
'***creates and sends email
With objEmail
.To = strTo
.CC = strCC
.Subject = strSubject
.HTMLBody = strConsolidatedBody
' .Display
.Send
End With
'Add record of send to Actions Compelted table
MyTrackingTable.AddNew
MyTrackingTable("intTerminationID") = strID
MyTrackingTable("intActionID") = 1
MyTrackingTable("dtmActionPerformed") = Now()
MyTrackingTable.Update
'Move to the next record.
rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If
MsgBox "All email messages have been sent."
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub