sleone74
asked on
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.
__________________________
'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,
'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
__________________________
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?
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.
__________________________
'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,
'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
__________________________
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?
ASKER
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.
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,
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.Appl
Set objEmailMessage = objOutlook.CreateItem(olMa
'Set subject if specified
If sSubject <> "" Then objEmailMessage.Subject = sSubject
'If message body specified then add that
objEmailMessage.Body = sBodyText
objEmailMessage.ReadReceip
objEmailMessage.Originator
'If attachment passed then add that
If sAttachment <> "" Then objEmailMessage.Attachment
'Set the email object
objEmailMessage.Recipients
'Resolve email address
objEmailMessage.Recipients
'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)
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,
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.Appl
Set objEmailMessage = objOutlook.CreateItem(olMa
'Set subject if specified
If sSubject <> "" Then objEmailMessage.Subject = sSubject
'If message body specified then add that
objEmailMessage.Body = sBodyText
objEmailMessage.ReadReceip
objEmailMessage.Originator
'If attachment passed then add that
If sAttachment <> "" Then objEmailMessage.Attachment
'Set the email object
objEmailMessage.Recipients
'Resolve email address
objEmailMessage.Recipients
'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)
as for using task scheduler, add a new task but like this...
cmd /c BatchFileYouaretryingtoRun
and ensure that you have set the 'run as' username and password correctly
cmd /c BatchFileYouaretryingtoRun
and ensure that you have set the 'run as' username and password correctly
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If so, could u not move that processed record from Reminder_Information to some other Processed_Reimder_informat
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