Send Email - Addresses, subject text and body text in query...

[misscrf]

I tried this code that I found online:

( http://www.datastrat.com/Code/MultipleEmail.txt)

But I had some issues. I believe that I am under some constraints, because
I have Outlook running on Exchange.

From my research, I think I would like to do the following:

I have a table with employees, which includes their email. I have a table
with scheduled appointments for car services (oil changes, inspections, etc)
it is something our company offers.

This schedule table contains a checkbox for if the service should have a
followup reminder. Then it has a field for months - how many months from
this service date, the reminder should be based on the months. Finally is a
field that calculates the reminder date.

What I want to do is have a form for processing reminders. The User would
put in the date of the upcoming service, and click a "process reminders"
button. It would run a query to find the (LAST) reminder date for an
employee, where the reminder date is >= the service date. It would then
send an email to that person - 1 email to each person (ie there may be 10
results, but that would make 10 emails go out, 1 to each person) Each email
would say in the subject "This is your reminder for your "&[service type]

The body would say "Please reply to schedule an appointment, for the next
service date, on "& [form date entered by user]&" Thank you, Sender"

I think part of my confusion is that I don't want to hard code all of the
subject, the to, and the body. I want to have some "text for the subject " &
[fields] .

Make sense?

Here is how I tried to modify the code from above, but from trying to debug
it, now it doesn't do anything.

Function Email(strTo As String, strSubject _
As String, Optional varMsg As Variant, Optional varAttachment As
Variant)

' ©Arvin Meyer 1999-2004
' Permission to use is granted if copyright notice is left intact.
' Permisssion is denied for use with unsolicited commercial email

'Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim strTo As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
'Dim objEml As Outlook.MailItem
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailReminder")
Set objOutl = CreateObject("Outlook.application")
'Set objEml = objOutl.createItem(olMailitem)

If IsNull(Me.cmbReminder) Or IsNull(Me.cmbServiceDate) Then
DoCmd.CancelEvent
MsgBox "You must choose a type of reminder and a service date!", vbCritical,
"Please Choose Your Reminder"
Else

With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
Else
MsgBox "No Reminders to Send!", vbOKOnly, "Thank you"
End If
End With

For i = 1 To rst.RecordCount
If Len(rst!EmailAddress) > 0 Then
strTo = "[email protected]"
strBCC = rst!EmailAddress
Dim objEml As Outlook.MailItem
Set objEml = objOutl.createItem(olMailitem)

With objEml
.To = strTo

.Subject = "Your car is due for an oil change!"

.Body = "Please reply to schedule your oil change for the next
service date, which will take place one " & cmbServiceDate


' Uncomment for attachment
'            If Not IsMissing(varAttachment) Then
'                .Attachments.Add varAttachment
'            End If

.Send
End With
End If
Set objEml = Nothing
rst.MoveNext
Next i

ExitHere:
Set objOutl = Nothing
'Set objEml = Nothing
Set rst = Nothing
Set db = Nothing
End If

Exit Function

Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere

End Function

Any help would be appreciated. I think part of the problem is that while I
can do some code, I don't understand declaring objects very well, so a lot of
this code doesn't make sense to me.

Thanks.