Help with VBA to send mail

[micko1]

Not sure what hapened, thought I posted this last night but can't see it anywhere. So here we go again.
I have a sub form who's record source is a query. The query gets all contractors who's renewal date for insurance falls between Date()+7 and Date()+30. There is also a column that is a YES/NO to record when the email is sent. All is working fine as far as looping through all records in the query and sending the email but when it records the YES/NO for email sent it changes all records in the table "contractors" to YES. Not sure how to get it to just change the records that are displayed in the query.
Apparently due to the fact that I have not had 10 posts I am unable to have anything referencing emails on this forum. I have removed all references and hope there is enough info for you to make a call.
Thankyou in advance for taking time to assist. Any help would be greatly appreciated.

Public Sub Sendwcompmonth()
'Provides the Send automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strAddress As String
Dim strMsg As String
Dim ingCounter As Integer
Dim intCount As Integer
strSubject = "Workers Compensation due for Renewal"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryWorkCompDueMonth")
 
 
'Count of unsent
intCount = DCount("[ID]", "[Contractors]" _
, "[wcompsentmonth]=0")
'If count of unsent  is zero then the procedure will not run
'If count of unsent  is greater than zero, msgbox will prompt
'to send mail.
    If intCount = 0 Then
        MsgBox ("You have " & intCount & " Workers Comp due Month to send.") _
        , vbInformation, "System Information"
        Exit Sub
    Else
rst.MoveFirst
Do Until rst.EOF
    strAddress = rst![address]
    strlMsg = " To Whom it may concern" & "" & Chr(10) & Chr(10) _
    & " Your company " & rst![ContractorName] & " has contracted to our company in the past. " & Chr(10) & Chr(10) _
    & " According to our records your Workers compensation is due for renewal on " & rst![WorkersComp] & " and we have not yet received an updated certificate of currency.." & Chr(10) & Chr(10) _
    & "Please forward your insurance details to:" & Chr(10) & Chr(10) _
    & "mail address goes here" & Chr(10) & Chr(10) _
    & "This is an automated message sent as a reminder to your company and Ours of pending insurance renewals. Please ensure we recieves a copy of your renewals ASAP." & Chr(10) & Chr(10) _
    & " Thank you for your co-operation in this matter." & Chr(10) & Chr(10) & Chr(10) & Chr(10) _
    & "CONFIDENTIALITY CAUTION - This page and any accompanying documents contain privileged and confidential information for the specific individual to whom it is addressed, and are protected by law. If you have received this communication in error, please desist from reading the contents, notify us immediately and destroy the communication "
 
    'USER DETAILS
    DoCmd.SendObject , , acFormatRTF, strAddress, _
    , , strSubject, strlMsg, False, False
 
 
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
'Run update to update the check box
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE contractors SET contractors.wcompsentmonth = -1 WHERE (((contractors.wcompsentmonth)=0))"
    DoCmd.SetWarnings True
    MsgBox "All Workers Compensation due in one month have been sent", vbInformation, "Thank You"
   End If
End Sub

 

[pbaldy]

Presuming the field is available via the query, within the loop:

rst.Edit
rst!wcompsentmonth = True
rst.Update

will update each record as it is mailed.

 

[micko1]

Paul
Thankyou very much for your assistance. YES that works fine. Not sure if I will ever get all of this. I have only been looking at access for a short time and it is a bit mind boggling.
Thanks again for your assistance much appreciated.

Mick

 

[pbaldy]

Happy to help Mick. There is certainly a lot to learn, and half the time 5 ways to accomplish something.