-
December 13th, 2012, 11:01 AM
#1
[RESOLVED] Outlook 2003 Automatic E-Mail
Hello all,
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!
-
December 13th, 2012, 03:15 PM
#2
Re: Outlook 2003 Automatic E-Mail
Just use a 1,2, or 3 for the type, and the date as the parameter. Subtract that date from NOW to determine what to send.
-
December 13th, 2012, 03:33 PM
#3
Re: Outlook 2003 Automatic E-Mail
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.
Code:
'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
Loop
End If
End With
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?
-
December 14th, 2012, 02:03 AM
#4
Re: Outlook 2003 Automatic E-Mail
there is no DoCmd. Check DO WHILE... before LOOP
-
December 14th, 2012, 10:06 AM
#5
Re: Outlook 2003 Automatic E-Mail
Code:
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")
With rs
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' "
Else
'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
Loop
End If
End With
' Clean up memory at the end
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
End Sub
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|