?
Solved

I need help with a VBA script

Posted on 2006-05-31
10
Medium Priority
?
355 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:sleone74
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16799534
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
 

Author Comment

by:sleone74
ID: 16799990
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16800230
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:lojk
ID: 16800811
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
 

Author Comment

by:sleone74
ID: 16800817
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 16801572
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
 

Assisted Solution

by:Jayzed
Jayzed earned 1000 total points
ID: 16804832
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Starting up a Project

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question