Automated Email Trouble

[bwyrwitzke]

I'm struggling to get the code listed below to work properly. There are two issues I'm faced with.

1. When I open the database and run the sub, only ONE email is generated.

2. If I run the sub a second time, no emails are generated, but the MsgBox comes up saying that "Training Reminders have been sent."

Any help would be appreciated. Thanks.

Private Sub cmdMail_Click()
On Error GoTo Err_cmdMail_Click

  Dim rsPersonnel As DAO.Recordset
  Dim strEmail As String
  Dim strBody As String
  Dim strSubject As String
  Dim strFirstName As String
  Dim strExpiresIn As String
  Set rsPersonnel = CurrentDb.OpenRecordset("qryPersonnel")
  
  Do While Not rsPersonnel.EOF
  strEmail = rsPersonnel.Fields("Email").Value
  
  If blnExpired = True Then
   strBody = "This is an automated message reminding you that your DSP training has expired.  Please contact the OCI Administrator to schedule training."
  End If
  
  If blnWithin30 = True Then
   strExpiresIn = rsPersonnel.Fields("ExpireIn").Value
   strBody = "This is an automated message reminding you that your DSP training expires in " & strExpiresIn & " days.  Please contact the OCI Administrator to schedule training."
  End If
  
  If blnNoTraining = True Then
  strBody = "This is an automated message reminding you to schedule DSP training with the OCI Administrator."
  End If
  
  strFirstName = rsPersonnel.Fields("[First Name]").Value
  strSubject = "DSP Training"
  
  DoCmd.SendObject , , , strEmail, , , strSubject, "Hello " & strFirstName & vbCrLf & strBody, True
  
  rsPersonnel.MoveNext
  
  Loop
  
  Set rsPersonnel = Nothing
  
  MsgBox "Training reminders have been sent"
Exit Sub
  
Exit_cmdMail_Click:
  Exit Sub
  
Err_cmdMail_Click:
  MsgBox "There has been an error generating email notifications.  Please ensure all users have an email address listed.", vbExclamation, conAppName
  Resume Exit_cmdMail_Click
    
End Sub

 

[WayneRyan]

bw,

It sure looks OK, but I get the feeling that a NULL value is
causing you some problems.

Have you used the debugger?

Get your code in design view. Click on the left margin on an
executable line of code. The red circle is a break-point. When
you run the code it will stop there.

Hover over a variable and display its value.

F8 will single-step.
F5 will run to "completion".
Ctrl-F9 will move the next line to be executed to THERE.

Explore.

hth,
Wayne

p.s. We have nice weather here!

 

[bwyrwitzke]

Strange

It works just fine at home. Would my version of Outlook have anything to do with it? I use Access 2000 and Outlook2000 at work. At home, I use Access 2000 and Outlook XP. I dont' get it. I'll try stepping through as you described to see what happens... Thanks.

Bruce

 

[bwyrwitzke]

Stepped through...

and everything looks good, but still have problems (only on the laptop - everything works as expected on the desktop). Don't know what else to try.

Bruce