Send e mail only once per cycle (1 Viewer)

[hilbertm]

I am using the following code to send e mail notifications if people are within 30 days of needing training, then if the training is over due another e mail gets sent.
Private Sub Form_Current()
If (Forms![frmEmailWarnings]![ TngDueEmailSent] = 0) Then

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("qryTngDue")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , "training Due soon", " training will be due soon. Please get scheduled for this training as soon as possible. ", True
Me. TngDueEmailSent = -1

rsEmail.MoveNext
Loop
Set rsEmail = Nothing

End If

If (Forms![frmEmailWarnings]![ TngOverdueEmailSent] = 0) Then
Set rsEmail = CurrentDb.OpenRecordset("qryTngOverdue")
Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , "training Is Overdue", "You have not attended training in the past year. It is important the training is completed in order to get you current again. Please secure this training as soon as possible. Thank You", True
Me.TngOverdueEmailSent = -1
rsEmail.MoveNext
Loop
Set rsEmail = Nothing
End If
End sub


Basically, what I want is for the code to check the query, then
send an email to all on the query (the query selects all that need the yearly training), then
mark each record’s e mail sent yes/no field to yes after the email is sent, (so the e mail only gets sent once), then
somehow reset the yes/no field to NO after the new cycle starts(after the training is completed) so the e mail can be sent when the training becomes overdue again.

I have about 15 items that I want to send notifications for. My problem is that with the following code the e mail gets sent but the yes/no fields only get updated for the first record in the database


[This message has been edited by hilbertm (edited 12-21-2001).]

 

[BukHix]

Ok this will get you started and if you understand what is happening in the code you will be able to do the do the second email and reverse the check boxes. You just need to insert this into the code that you already have. I put the Do until loop where you had the Me.TngDueEmailSent = -1.

Dim rsRecord As DAO.Recordset

Set rsRecord = CurrentDb.OpenRecordset("qryTngDue")

Do Until rsRecord.EOF
    rsRecord.edit
    rsRecord.Fields("TngDueEmailSent").Value = -1
    rsRecord.Update
    rsRecord.MoveNext
Loop

Set rsRecord = Nothing

Post back if you have more questions.


[This message has been edited by BukHix (edited 12-21-2001).]

 

[hilbertm]

BukHix,
Thanks for the quick answer. Unfortunately I am not that up on VBA. I don’t know where to place your code. I will be studying the help files for now trying to figure it out. Currently my VBA skills are limited, but I am trying to learn.

Thanks again, looking for more help.

Mike

[This message has been edited by hilbertm (edited 12-21-2001).]

 

[BukHix]

Ok lets do this in steps. Here is how the first part should look.

Dim rsEmail As DAO.Recordset
Dim rsRecord As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("qryTngOverdue")
Set rsRecord = CurrentDb.OpenRecordset("qryTngOverdue")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , "training Due soon", " training will be " & _
    "due soon. Please get scheduled for this training as soon as possible. ", True

    Do Until rsRecord.EOF
        rsRecord.edit
        rsRecord.Fields("TngDueEmailSent").Value = -1
        rsRecord.Update
        rsRecord.MoveNext
    Loop

rsEmail.MoveNext
Loop
Set rsEmail = Nothing
Set rsRecord = Nothing

That will take a recordset, your query, and change unchecked Yes/No boxes to checked. I have to go now but once you get this part working post back and I will try and help you out with the next bit.


[This message has been edited by BukHix (edited 12-21-2001).]

 

[hilbertm]

BukHix,
Great, it works. Changes the yes/no box for all that the query filters. The code also sends email to only the first person (first record)on the list.

I am well ahead of where I was yesterday.

Thanks, looking foward to any more help you can give me.

 

[BukHix]

So what is the next thing you need to have it do?

 

[hilbertm]

The code only sent e mail to the first of 11 people on the query list. Is there a reason from the posted code that this would happen?
I would like e mail to be sent to all on the query results.

I also need to find a way to change the yes/no field back to no after the training is complete. This way a new e mail can be sent if training is overdue next time.

thanks again for your time.

 

[BukHix]

I am not sure why only eleven people would of got the email and not the rest. Unless an error is occuring that stops the process. I had this happen in a ASP app once and the only way I could figure it out was to set them SendObjects edit back to tru and launch the emails one by one until I found it. There was a problem with the syntax in one of the addresses.

Basicaly you just need a way to trigger the unchecking and then put this in that event:

Dim rsRecord As DAO.Recordset
Set rsRecord = CurrentDb.OpenRecordset("qryTngOverdue")


Do Until rsRecord.EOF
rsRecord.edit
rsRecord.Fields("TngDueEmailSent").Value = 0
rsRecord.Update
rsRecord.MoveNext
Loop

Set rsRecord = Nothing

I am not at a computer where I can test this but I think that should do it.

 

[hilbertm]

Thanks again for your help.

Actually only the first person got the e mail and not the rest that got the check marks. I will have to check the e mail addresses, that may be the problem.

I will not be able to check until Friday, since I have some time off.

Any ideas on how to trigger the unchecking routine? I guess I could somehow uncheck the boxes when the new training date is entered.

Happy Holidays,
Mike