Emailing letters to individual email addresses 2

[gbscobel]

I need to preface this message by saying that while I have a lot of experience creating databases and working with macros, I have next to no experience with VB coding so any help I receive I really need specific help doing what I need to.

Basically, I have a database where I run a report that is basically an hours status letter that runs for customers based on some criteria of number of hours remaining and activity in a month. The result is a report that contains several letters, one for each customer and I am currently printing the entire report, parsing out each customer's report and mailing it.

What I would like is an automated way to generate each customer's letter and email is a pdf attachment. I have gone through various other threads and have seen references to recordsets and looping but due to my novice status, I am unable to figure out how to apply this information to my database and integrate it in.

I understand this is a novice question and that anybody who responds will need to give me some handholding to walk me through how to get this code in and how to customize it with my database. Thanks very much, any help is greatly appreciated.

 

[gbscobel]

Ok, I was finally able to figure out that part of it and the recordset is returning information so that's working. So, you're saying I need to remove the record source from the report, replace every single field with rstStandardHours!'field name' and then try it?

 

[GShen]

You got it. OR. Just pass back the name of the client from the query and then have the report run a query based on that client. Six of 1, 1/2 dozen of the other. The later would allow you to use that report maybe in other spots and you would not have to change any of the names.
The DRIVER (eg. the recordset) would just be used to create a list of clients to print..... ttyl

Remember when... everything worked and there was a reason for it?

 

[GShen]

It is simple once you have done it a 1000 times. You will be the expert next time. Have a good night. If you want to run without stepping thru, click on the red dot again and save it. It will disappear when you click on it again.

Remember when... everything worked and there was a reason for it?

 

[gbscobel]

Thanks again for all the effort. I think I'm about done here. I removed the record source of the report, changed every field to rstStandardHours!field name, and when I run the procedure, I still get the error that 'The action or method is invalid because the form or report isn't bound to a table or query.' I don't know what it is I'm doing or not doing but this has become a huge time suck for both of us and I just don't seem to be making any progress at all.

Thanks again for all the help but I have spent way more time on this than I ever believed possible and I'm not sure I'm any closer to making this work than when I started.

Believe me, it's not you. I'm just missing basic knowledge to make a connection here. I'm sure it's obvious for someone with experience but I'm just flailing here and I feel like I'm wasting your time and patience.

 

[GShen]

I know it is fustrating. Give this one more shot. You are learning on the fly. Can you make a query for the report which is driven by the client ? In other words when the report runs, it would prompt for a client. You type it in and away it works. Normal stuff. (obviously you would have to change the names back the names from the query which drives it). Now once you get that working. Change the query which is driving the report to get the client from a field name on your FORM (PRINT REPORTS). Use the code I gave you to set the field name on your form right before the opening of the report. You will have to obviously create the field on the form PRINT REPORTS and name it Client or what have you.
EG.
Forms![Print Reports]![Client] = rstStandardHours!Client

Now when your vb code runs, it will set the field on your form to the 1st client in the record set. Your report opens and the query is driven with this field to populate the report. The movenext gets the next record in the record set, sets the form field (eg. Forms![Print Reports]![Client]) to the next client in the recordset (eg. rstStandardHours!Client), calls the report again, with the new client. The report opens again getting the new field and populates the report with the information for the client from the query driving the report. etc. Create it just as I said. Send me another update if it fails. This should work.

Remember when... everything worked and there was a reason for it?

 

[gbscobel]

I did all that but now when I run the code, I get the error Too few parameters. Expected 2. It won't go into debug mode to show me what exactly is wrong though so I can't figure out where it's failing. Both the report and query run fun independantly of the code, I don't know what's going on. Here is the code now:

Private Sub Command215_Click()
On Error GoTo Err_Command215_Click

Dim dbCustomer As Database
Dim rstStandardHours As DAO.Recordset
Dim rstEmail As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbCustomer = CurrentDb

Set qdf = dbCustomer.QueryDefs("Detail Report")
qdf.Parameters(0) = Forms![Print Reports]![sort Date]
Set rstStandardHours = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)

Do While Not rstStandardHours.EOF
Forms![Print Reports]![Client] = rstStandardHours!Client
'DoCmd.SendObject acReport, "Hours Status", "PDFFormat(*.pdf)", rstEmail!Email, "", "", "Hours Status letter", "Attached find your letter", False, ""
DoCmd.OpenReport "Hours Status", acViewPreview, "", "", acNormal
rstStandardHours.MoveNext
Loop
rstStandardHours.Close

Set rstStandardHours = Nothing

Exit_Command215_Click:
Exit Sub
Err_Command215_Click:
MsgBox Err.Description
Resume Exit_Command215_Click

End Sub

 

[gbscobel]

Ok, I figured out the problem, since I added another form field to the query, I had to establish another parameter. Here's what doesn't make sense now. I have the query, Detail Report which establishes my recordset but the query runs off a form field which draws data from a recordset which doesn't exist until the query runs. I don't see how this is ever going to work, don't we need another table or query to establish the client field on the form or else the detail report query will never be able to run.

 

[GShen]

This looks good. Can you send me another sample database.
I want to see how your report is set with its query and I want to make sure the FORM is set properly. Again, not sure what

DoCmd.OpenReport "Hours Status", acViewPreview, "", "", acNormal does.

Can you please change that to

DoCmd.OpenReport Me.lstReport, acPreview, "", ""

to muddy the waters less.


You are closer than you think.

Remember when... everything worked and there was a reason for it?

 

[GShen]

You need 2 queries. One query for the recordset which just has the client name in it and one query to drive the report by the client name which will have all data in it to print the report.

Remember when... everything worked and there was a reason for it?

 

[gbscobel]

Well, believe it or not, I believe that is working now. I just tested it with emailing and I got 3 separate emails each with individual letters. I will have to play with it a bit more to get it fully integrated with my system but I can't thank you enough for all your time and patience with this. This code is going to save me a ton of time and aggravation so thanks again so much!

 

[GShen]

Great! Let me know if you get stuck again. Glad I could help. You can give me a star if you would like. Once you get the concept you may be able to do this a little more streamlined but I think you are getting there. Yes, a vba course would help you. Even the intro course.

Remember when... everything worked and there was a reason for it?