Automatic E-mails from Access 2000

[SBelyea]

Hey everyone -

It's been a while since I've been on the boards. I've spent quite a bit of time trying to set up my database in such a way that I can execute some VBA code to automatically send e-mails when inspections are overdue for BMP (stormwater management "Best Management Practices").

I've created a query called qrySendMail that pulls all the information I need out of my database, such as OwnersBusiness (which is sent in the body of the e-mail).

This is the code I am currently using. It creates the e-mail perfectly, but does not send it automatically. I'd really appreciate any help available to get this working.

Public Function SendEmailAdvice() As String

'Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String

   'Set environment
   Set db = CurrentDb
   DoCmd.SetWarnings False
   Dim Email As String
   
   Email = "sbelyea@town.ashland.va.us"
   
   'Open the controlling recordset
   Set rs = db.OpenRecordset("qry_sendemail")
   While Not rs.EOF
       
       'For each record (Booking) send an email
       DoCmd.SendObject acSendNoObject, , , [Email], , , "BMP Inspection Reminder", _
           "A BMP inspection is overdue for " & rs![OwnersBusiness] & _
           vbCrLf & vbCrLf & "This e-mail was automatically generated. Do not reply."
       
       'Update the Booking table so that emails are not duplicated
       'sSQL = "UPDATE Booking SET EmailSent=-1 WHERE BookingID=" & rs![BookingID]
       'db.Execute sSQL
       
       'Cycle on to the next record
       rs.MoveNext
   Wend
   rs.Close
   Set rs = Nothing
   'Control recordset now closed
   
   'Reset environment
   DoCmd.SetWarnings True

End Function

The code is not my own - I can't remember the source I copied it from, but I have made changes to make it pull the proper info from my database.

A second question:

VBA hangs every time I try to run the code at this line

'Dim db As DAO.Database, rs As DAO.Recordset

, so I have commented it out. Can someone please enlighten me on how to get this line functioning? I've checked my references and DAO 3.6 is checked.

Thanks everyone!

 

[SBelyea]

Whoops! I figured it out - thanks tek-tips for the incredible FAQ section!

 

[TrunkMonkey]

Check out a third party application called Persits ASP email. I found its much easier to use this via VBA, then to use the bottled stuff from Access. Very easy to use...