I need help with a VBA script

okay guys,
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
    rs.MoveNext
    Loop

    'Close and clear all
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    ReminderEmails = True
    Exit Function


'SMError:
    'MsgBox "A problem occurred." & vbCrLf & Err.Description, vbCritical, "ReminderEmails"
    'ReminderEmails = True
    End Function
_________________________________________________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?
sleone74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
Not sure what the problem is, do u just want the coupons sent once?

If so, could u not move that processed record from Reminder_Information to some other Processed_Reimder_information?

this way coupon only sent one


If u create a macro in Access, which in turns calls your procedure (which has to be public function)

then u can call access passing in /x <macroname>

this will automatically run that macro
0
sleone74Author Commented:
That is how I have my macro set up now.  I need one coupon sent to each customer that is specific to that customer. In other words the coupon has the customers name on it but when the customer opens up the attachment now they have a coupon from every single customer that was emailed that day because it is a report in Access.  I need it to send just the report for that particular customer.
0
rockiroadsCommented:
ok, what u need to do is run the report first but with filters
SendObject just emails the object u say

e.g.

    Dim sFile As String
   
    'Create a file in C:\Coupons called C_<emailaddress>
    'email address may have characters not allowed so think of something else if u want
    sFile = "C:\Coupons\C_" & rs!Customer_Email_Address
   
    'Run the report, output to sFile
    DoCmd.OutputTo acOutputReport, "Thank You Coupon", acFormatSNP, sFile
   
    'Now send using Outlook automation -
    'Do u have outlook? if not we use CDO
    SendRRMailEE rs!Cusromer_Email_Address, "Come back to see your friendly Jiffy Lube!", emailtext, sFile




Now SendRRMailEE is a function that I created using Outlook Automation

place this code in another module (or same if u prefer)


Public Function SendRRMailEE(ByVal sReceipient, _
                           ByVal sSubject As String, _
                           ByVal sBodyText As String, _
                           ByVal sAttachment As String) As Boolean

    Dim objOutlook As Object
    Dim objEmailMessage As Object
   
   
    'Specify error handler
    On Error GoTo SMError
   
    'If no receipient passed then exit with error
    If Trim$(sReceipient) = "" Then
        MsgBox "No Receipient name has been specified", vbExclamation, "Send Mail"
        Exit Function
    End If
   
   
    'Create outlook objects
    'note, you need to add the Outlook reference in Modules Menu option Tools/References
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmailMessage = objOutlook.CreateItem(olMailItem)
   
    'Set subject if specified
    If sSubject <> "" Then objEmailMessage.Subject = sSubject
   
    'If message body specified then add that
    objEmailMessage.Body = sBodyText
   
    objEmailMessage.ReadReceiptRequested = True
    objEmailMessage.OriginatorDeliveryReportRequested = True
   
    'If attachment passed then add that
    If sAttachment <> "" Then objEmailMessage.Attachments.Add sAttachment
   
    'Set the email object
    objEmailMessage.Recipients.Add sReceipient
    'Resolve email address
    objEmailMessage.Recipients.ResolveAll
   
    'Display email
    objEmailMessage.Display
   
    objEmailMessage.send
   
    'MsgBox "Message has been successfully sent", vbInformation, "Send Mail"
   
    'Return success
    SendRRMailEE = True
    GoTo SMDone
 
SMError:
    'Return failure and display error message
    SendRRMailEE = False
    MsgBox "An error occurred when trying to send the email." & vbCrLf & vbCrLf & Err.Description, vbCritical, "Send Mail"

SMDone:
    'Clear down the objects created
    On Error Resume Next
    Set objEmailMessage = Nothing
    Set objOutlook = Nothing
End Function




You may need to create a reference to Microsoft Outlook Object Library (in Tools/References - vba window)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lojk.Net and Infrastructure ConsultantCommented:
as for using task scheduler, add a new task but like this...

cmd /c BatchFileYouaretryingtoRun.bat

and ensure that you have set the 'run as' username and password correctly
0
sleone74Author Commented:
that's a little too intrinsic for what i was looking for.  I was just wondering if there was a way to use the existing vba script i have and alter THAT.  You are talking about basically restructuring my entire service_reminder automation process.  I'll think of something else.  But thanks for your help.
0
rockiroadsCommented:
ok no problems, its your call

when u do sendobject, u basically run the report (which you are running without filters) and it sends that

what I proprosed was to run the report with your filters, it then creates a file

u then send that file

this answers your issue on

"I need one coupon sent to each customer that is specific to that customer. In other words the coupon has the customers name on it but when the customer opens up the attachment now they have a coupon from every single customer that was emailed that day because it is a report in Access.  I need it to send just the report for that particular customer."


I dont believe its restructuring the code, but its your decision.

I hope you find a suitable solution for your needs

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JayzedCommented:
Hi sleone74

One way to do this without altering your code too much would be to use querydefs
1) Add a Primary Key field to your "Reminder_Information" table (If you don't alreasy have one) and call it ID
2 Create a select query called "Print_Reminder_Information" and add all the fields of the table above to this qurey. Set it as the RecordSource for your "Thank_You_Coupon" report
3) Go through the Code below and add the commented items

What this will do is rewrite the query each time it loops through the Do...Loop Statement and uses the ID field to specify which record to send. I've tested this and it works.

Let me know if you need more info.




'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
    '*********ADD THE "ID" FIELD TO YOUR SELECT STATMENT***********
    Set rs = db.OpenRecordset("SELECT ID, 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
   
    '*********ADD THESE LINES TO YOUR CODE***********
    Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs("Print_Reminder_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
   
    '*********ADD THESE LINES TO YOUR CODE, This will rewrite the query based on the current record***********
    qdf.SQL = "SELECT ID, Customer_First_Name, Customer_Last_Name, Customer_Email_Address, Vehicle_Make, Vehicle_Model, Vehicle_Year, Store_Name, Invoice_Date " & _
                "FROM Reminder_Information " & _
                "WHERE (((Reminder_Information.ID)=" & rs!ID & "));"
    '****************************************************************************************************
    DoCmd.SendObject acSendReport, "Thank_You_Coupon", acFormatSNP, "Thank_You_Coupon", , , "Come back to see your friendly Jiffy Lube!", emailText, False
   
    'Read next record
    rs.MoveNext
    Loop

    'Close and clear all
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
    '*********ADD THIS LINE TO YOUR CODE***********
    Set qdf = Nothing
    '*******************************************

    ReminderEmails = True
    Exit Function


'SMError:
    'MsgBox "A problem occurred." & vbCrLf & Err.Description, vbCritical, "ReminderEmails"
    'ReminderEmails = True
    End Function
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.