alexfwalker81
Member
- Local time
- Yesterday, 17:50
- Joined
- Feb 26, 2016
- Messages
- 107
How can I adjust this code to loop through each row in a query and send an email for each row?
Code:
Public Function SendEmail()
Dim rsID As DAO.Recordset
Dim rsDT As DAO.Recordset
Dim rsDR As DAO.Recordset
Dim rsCD As DAO.Recordset
Dim rsCN As DAO.Recordset
Dim rsPN As DAO.Recordset
Dim rsPR As DAO.Recordset
Dim rsAD As DAO.Recordset
Dim rsCM As DAO.Recordset
Dim db As DAO.Database
Dim mail As CDO.MESSAGE
Dim config As CDO.Configuration
Dim latest_entry_ID As String
Dim latest_entry_DT As String
Dim latest_entry_DR As String
Dim latest_entry_CD As String
Dim latest_entry_CN As String
Dim latest_entry_PN As String
Dim latest_entry_PR As String
Dim latest_entry_AD As String
Dim latest_entry_CM As String
latest_entry_ID = "SELECT qry_latest_entry.[ID] FROM qry_latest_entry"
latest_entry_DT = "SELECT qry_latest_entry.[DepositType] FROM qry_latest_entry"
latest_entry_DR = "SELECT qry_latest_entry.[DateReceived] FROM qry_latest_entry"
latest_entry_CD = "SELECT qry_latest_entry.[ChequeDate] FROM qry_latest_entry"
latest_entry_CN = "SELECT qry_latest_entry.[ChequeNumber] FROM qry_latest_entry"
latest_entry_PN = "SELECT qry_latest_entry.[PayeeName] FROM qry_latest_entry"
latest_entry_PR = "SELECT qry_latest_entry.[PayeeReference] FROM qry_latest_entry"
latest_entry_AD = "SELECT qry_latest_entry.[AmountDeposited] FROM qry_latest_entry"
latest_entry_CM = "SELECT qry_latest_entry.[Comments] FROM qry_latest_entry"
Set db = CurrentDb
Set rsID = db.OpenRecordset(latest_entry_ID)
Set rsDT = db.OpenRecordset(latest_entry_DT)
Set rsDR = db.OpenRecordset(latest_entry_DR)
Set rsCD = db.OpenRecordset(latest_entry_CD)
Set rsCN = db.OpenRecordset(latest_entry_CN)
Set rsPN = db.OpenRecordset(latest_entry_PN)
Set rsPR = db.OpenRecordset(latest_entry_PR)
Set rsAD = db.OpenRecordset(latest_entry_AD)
Set rsCM = db.OpenRecordset(latest_entry_CM)
Set mail = CreateObject("CDO.Message")
Set config = CreateObject("CDO.Configuration")
config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
config.Fields(cdoSMTPServer).Value = "10.0.0.102"
config.Fields(cdoSMTPServerPort).Value = 25
config.Fields.Update
Set mail.Configuration = config
If DCount("[ID]", "qry_latest_entry_is_cheque") = 0 Then
With mail
.To = "blah"
.From = "blah"
.Subject = "BACS Received from " & rsPN![PayeeName] & " - " & rsPR![PayeeReference] & " - " & rsAD![AmountDeposited]
.TextBody = "Deposit Receipt ID: " & rsID![ID] & vbCrLf & "Deposit Type: " & rsDT![DepositType] & vbCrLf & "Date Received: " & rsDR![DateReceived] & vbCrLf & "Payee Name: " & rsPN![PayeeName] & vbCrLf & "Payee Reference: " & rsPR![PayeeReference] & vbCrLf & "Amount Deposited: " & rsAD![AmountDeposited] & vbCrLf & "Comments: " & rsCM![Comments]
.Send
End With
Else
With mail
.To = "blah"
.From = "blah"
.Subject = "Cheque Received from " & rsPN![PayeeName] & " - " & rsPR![PayeeReference] & " - " & rsAD![AmountDeposited]
.TextBody = "Deposit Receipt ID: " & rsID![ID] & vbCrLf & "Deposit Type: " & rsDT![DepositType] & vbCrLf & "Date Received: " & rsDR![DateReceived] & vbCrLf & "Date on Cheque: " & rsCD![ChequeDate] & vbCrLf & "Cheque Number" & rsCN![ChequeNumber] & vbCrLf & "Payee Name: " & rsPN![PayeeName] & vbCrLf & "Payee Reference: " & rsPR![PayeeReference] & vbCrLf & "Amount Deposited: " & rsAD![AmountDeposited] & vbCrLf & "Comments: " & rsCM![Comments]
.Send
End With
End If
Set config = Nothing
Set mail = Nothing
End Function
