Private Sub cmdEmailConfirmed_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim oOApp_001 As Outlook.Application
Dim oOMail_001 As Outlook.MailItem
Dim distro As String
Dim strSql As String
Set db = CurrentDb()
'Sets the email distro and subject line to blank
distro = ""
Set oOApp_001 = CreateObject("Outlook.Application")
Set oOMail_001 = oOApp_001.CreateItem(olMailItem)
strSql = "SELECT tblReceiveTemp.ATTReceiveStatus, tblReceiveTemp.SprintReceiveStatus, tblReceiveTemp.VerizonReceiveStatus, tblReceiveTemp.[Level 1Approver Email], tblReceiveTemp.[Level 1Approver]" & _
" FROM tblReceiveTemp " & _
" WHERE tblReceiveTemp.ATTReceiveStatus = 'NR'OR tblReceiveTemp.SprintReceiveStatus = 'NR' OR tblReceiveTemp.VerizonReceiveStatus = 'NR'"
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)
'If recordset is empty, exit
Do While Not rst.EOF
distro = distro & ";" & rst.Fields("[Level 1Approver Email]")
rst.MoveNext
Loop
With oOMail_001
'now use the variable disto with all the email names in the to field of outlook
'.CC =
.BCC = distro
.Subject = "Wireless Telecommunication Approval - Reminder"
.Body = "Body"
.display
.BodyFormat = 1
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Private Sub cmdEmailConfirmed2_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim oOApp_001 As Outlook.Application
Dim oOMail_001 As Outlook.MailItem
Dim distro As String
Dim strSql As String
Set db = CurrentDb()
'Sets the email distro and subject line to blank
distro = ""
Set oOApp_001 = CreateObject("Outlook.Application")
Set oOMail_001 = oOApp_001.CreateItem(olMailItem)
strSql = "SELECT tblReceiveTemp.ATTReceiveStatus, tblReceiveTemp.SprintReceiveStatus, tblReceiveTemp.VerizonReceiveStatus, tblReceiveTemp.[Level 1Approver Email], tblReceiveTemp.[Level 1Approver]" & _
" FROM tblReceiveTemp " & _
" WHERE tblReceiveTemp.ATTReceiveStatus = 'NR'OR tblReceiveTemp.SprintReceiveStatus = 'NR' OR tblReceiveTemp.VerizonReceiveStatus = 'NR'"
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)
'If recordset is empty, exit
Do While Not rst.EOF
distro = distro & ";" & rst.Fields("[Level 1Approver Email]")
rst.MoveNext
Loop
With oOMail_001
'now use the variable disto with all the email names in the to field of outlook
'.CC =
.BCC = distro
.Subject = "Wireless Telecommunication Approval - Reminder 2"
.Body = "Body"
.display
.BodyFormat = 1
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
I want to insert an update command either by using DoCmd.RunSQL, but I am not sure where to put it in the above code so that each record that is emailed gets updated.