I have a working module that will send an e-mail through Access via Outlook, so this post isn't another one seeking code to get something like that working.
What I'm wondering, is if there is a way to specify WHO to send the e-mails to. Basically, I am sending out e-mails to a specified list of people to let them know that job tickets have been "out" for 5, 10, or 15 days (or longer). At 5 days, the clerks (secretaries) get the message, at 10 days the managers and accounts receivable people get the message, so on... The 10 day list does not need to receive the 5 day list, so I have created three separate tables in order to prevent this from happening.
What I would LIKE to do, is consolidate these tables into one, and have a field within the table that specifies whether they are a 5, 10, or 15 day message recipient. Also, the 5 day list receives both the 10 and 15 day message, and the 10 day list receives the 15 day message. I hope this all makes sense..
I appreciate any help y'all can give!
Thanks for the reply!
So here's what I'm going to try..
In the table containing the e-mail addresses, I'll assign a "type" to the names in the table (5-Day addresses will be type 3, 10-Dayers will be type 2, and 15-dayers will be type 1).
I then plan to use an "If" statement to determine which names to send the e-mail to.
'If ![Type] = 3 Then
' The next line sends the E-Mail
DoCmd.SendObject acSendForm, "5-Day Notification", , ![E-Mail Address], , , "5-Day Reminder!", "Hello " & ![First Name] & ", " & Chr(10) & "You have tickets that have been out for 5 days or longer. Please get them processed as soon as possible.", False
.MoveNext ' Moves to the next record in the recordset
That was the code I just tried, and got an error message that said "Compile Error: Loop without Do"
So I know I have SOMETHING wrong. Maybe I misunderstood your advice?
Private Sub btnSendEmail_Click()
Dim rs As DAO.Recordset
Dim stDocName As String
Dim StrAttach As String
Set rs = CurrentDb.OpenRecordset("5-Day E-Mail Addresses")
If .EOF And .BOF Then ' If no tickets were found, no e-mails will be sent.
MsgBox " No emails will be sent because there are no records from the query '5-Day Notification' "
'Do Until .EOF ' Do until the end of the recordset
Do While ![Type] = "3"
' The next line sends the e-mail
DoCmd.SendObject acSendForm, "5-Day Ticket Notification Subform", , ![E-Mail Address], , , "5-Day Reminder!", "Hello " & ![First Name] & ", " & Chr(10) & "You have tickets that have been out for 5 days or longer. We ask that you delay no further in getting them processed. Please see the attached document for details.", False
.MoveNext ' Moves to the next e-mail address
' Clean up memory at the end
If Not rs Is Nothing Then
Set rs = Nothing
This was my best shot. It sent the e-mail to the first person in the list whose type was 3, but I can't get it to send to any of the other threes in the list.