Here is a toughy worth some pretty solid points.
I have created the following vba script that runs in access which i used for email service reminders for my customers. The code works fine and it runs really well. The problem that I just found out is this:
The code sends 'technically' a report (which poses as a $5 coupon in a snapshot form). The coupon is supposed to be tailored for each individual customer but what happens is, since it is a report in access, each customer gets a slew of coupons (reports) everyday from the number of people that were sent emails. I have included my code below and was hoping that you guys could steer me in the right direction as to how I can get it so that the customers only get one report and the report they do get should be specific to them. I hope that makes sense.
__ Code is below this line______________________
'A procedure that sends automatic emails
Public Function ReminderEmails() As Boolean
Dim rs As DAO.Recordset
Dim db As Database
On Error Resume Next
'On Error GoTo SMError
'removed SMError to alleviate having to fix issues for typos.
'Will move onto next record and will prevent having to find error, fix, then rerun code.
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Customer_First_Name, Customer_Last_Name, Customer_Email_Address, Vehicle_Make, Vehicle_Model, Vehicle_Year, Store_Name, Invoice_Date FROM Reminder_Information")
'We open a recordset, read all the emails
'this query reads the values from the field called Email, table Needed_Information
'we then go through the list of records returned by the above call
Do While Not rs.EOF
Dim emailText As String
emailText = "Dear " & rs!Customer_First_Name & " " & rs!Customer_Last_Name & "," & vbCrLf & vbCrLf & _
"We at the " & rs!Store_Name & " Jiffy Lube serviced your " & rs!Vehicle_Year & " " & rs!Vehicle_Make & " " & rs!Vehicle_Model & " on " & rs!Invoice_Date & ". If you are an average driver our records show you are due for another oil change in a couple of weeks. If you bring in the attached coupon within 14 days of today's date we will take $5 off of your next service. This is just a small token of our appreciation for your business. Thank you for your patronage and we hope to see you very soon!!" & vbCrLf & vbCrLf & _
"The attached coupon needs either Microsoft's Snapshot Viewer or Access in order to view it correctly. You can download the Snapshot Viewer for free at http://www.microsoft.com/downloads/details.aspx?FamilyID=b73df33f-6d74-423d-8274-8b7e6313edfb&DisplayLang=en
." & vbCrLf & vbCrLf & _
"Sincerely," & vbCrLf & vbCrLf & _
"CB Squared Services, Inc. T/A Jiffy Lube"
'Send to email address read from the database (field is rs!Customer_Email_Address)
'Subject is Come back to see your friendly Jiffy Lube
'Body is emailText
DoCmd.SendObject acSendReport, "Thank_You_Coupon", acFormatSNP, rs!Customer_Email_Address,
, , "Come back to see your friendly Jiffy Lube!", emailText, False
'Read next record
'Close and clear all
Set rs = Nothing
Set db = Nothing
ReminderEmails = True
'MsgBox "A problem occurred." & vbCrLf & Err.Description, vbCritical, "ReminderEmails"
'ReminderEmails = True
___End of Code______________________
The only other problem is and I don't know if you can help me with this but I have tried a number of ways to automate the macros I created to run this autoemailer and for some reason The task scheduler built into windows has a hard time running macros and batch files so i am having to manually run this everday. Do you have any suggestions?