Automate Email Problems- Runtime Error 91

[CrostonScottish]

I wonder if anybody can help. I have a code that i have used successfully in previous databases to automatically send an email based on a record set.

The problem i have is that this database does not use set email addressess placed in the code as in the past. I would like the email to be sent to the email address from the the same record set each time. This would be the booking confirmation for a student with the email address in their record.

I have tried to change the StrEmailAddress but i get a Runtime Error 91.

Any ideas of what i need to do to the StrEmailAddress to get it to generate an email for the individual student?

Private Sub Exit_2_Click()
 Dim intStore As Integer
'Count of unsent e-mails
intStore = DCount("[BookingReference]", "tblLINKStudent_Course", "[ysnoConfirmationSentbyMail]=0")
'If count of unsent e-mails is zero then application will quit
'If count of unsent e-mails is greater than zero, msgbox will prompt to send mail.
'If user response is Yes then run the SendMail procedure or else quit application.
    If intStore = 0 Then
        DoCmd.Quit
        ElseIf MsgBox("You have " & intStore & " new course bookings." & vbCrLf & vbCrLf & "Please send the booking confirmations by clicking on 'Ok'." & vbCrLf & vbCrLf & "You must click 'Ok' on the next screen to allow the emails to be sent.", vbOKOnly + vbExclamation, "You Have Unsent E-Mails...") = vbOK Then
        SendMail
        Else
        MsgBox "Good Bye", , "Application Closing"
        DoCmd.Quit
    End If
End Sub
Public Sub SendMail()
  'Provides the Send Mail automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

strSubject = "Confirmation of booking at Terence Burgess Education Centre "
strEmailAddress = rst![HypEmailaddress]
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
'e-mail addresses
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendConfirmationMail")


'Count of unsent e-mails
intCount = DCount("[BookingReference]", "tblLINKStudent_Course" _
, "[ysnoConfirmationSentbyMail]=0")
'If count of unsent e-mails is zero then the procedure will not run
'If count of unsent e-mails is greater than zero, msgbox will prompt
'to send mail.

    If intCount = 0 Then
        MsgBox ("There are " & intCount & " new course bookings.") _
        , vbInformation, "System Information"
        Exit Sub
    Else

rst.MoveFirst
Do Until rst.EOF
strEMailMsg = "Dear " & rst![StrTitle] & rst![StrFirstName] & rst![StrLastName] & "," & Chr(10) & " The details of your booking are listed below:" & Chr(10) _
& Chr(10) & "Booking Reference:  " & rst![BookingReference] & Chr(10) & "Date of Course:  " & rst![CourseDate] & "Course Name:  " _
& rst![strCourseTitle] & Chr(10) & "Lunch Provided:  " & rst![strLunchProvided] & Chr(10) & Chr(10) & "Further course details will be forwarded to you shortly." _
& Chr(10) & "Regards"

  
         
         'EMAIL USER DETAILS & ATT REPORT
    DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
    , , strSubject, strEMailMsg, False, False
    
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
    
'Run update to update the sent mail check box
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblLINKStudent_Course SET tblLINKStudent_Course.ysnoConfirmationSentbyMail = -1 WHERE (((tblLINKStudent_Course.ysnoConfirmationSentbyMail)=0))"
    DoCmd.SetWarnings True
    MsgBox "All new booking confirmations have been sent", vbInformation, "Thank You"
   End If
End Sub

 

[boblarson]

You can't use this line:
strEmailAddress = rst![HypEmailaddress]

before you open the recordset:
Set rst = dbs.OpenRecordset("qrySendConfirmationMail")

 

[CrostonScottish]

Automate email error

Thanks for that. It was all a bit of a guess that i needed to use rst! to send email to specific person but didn't know that much VBA to be able to spot the mistake.

Its working like a dream.

Thanks again

 

[boblarson]

Glad to hear it is working for you. And, welcome to AWF, by the way.