Inserting an update command in an email loop (1 Viewer)

[lucy1216]

I have the following code, thanks to Stretchin'; however I need to update a table for each person emailed.

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
[CODE]
 
In addition, I have text within my database that I want to be the "body" of the email and would prefer to not have it come across on the email as an attachment.  With that being said, how would I get the text to come through as the "body" of the email.

 

[pbaldy]

As answered elsewhere:


At the simplest, right before this line:

rst.MoveNext

Downside being that that the user could not actually send the email. You may be able to use the Edit method of the recordset instead of SQL.

 

[lucy1216]

My apologies..could you walk me through the "edit"?

 

[pbaldy]

They use it here:

http://www.granite.ab.ca/access/email/recordsetloop.htm