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.

 

[GShen]

Set rstStandardHours = db.OpenRecordset("detail report")

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

 

[gbscobel]

When I try that, it tells me there are "Too few parameters. Expected 1

 

[GShen]

Are you passing parameters in the query ? You did not tell me that. I do not have osmosis. How many ? I would assume 1, is that correct ?

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

 

[gbscobel]

I'm sorry, I didn't realize it mattered and I wasn't passing any parameters to the query overtly however, there is a reference in one field to a field on the form. I didn't realize that had to be accounted for since the form in question is the one the procedure is being run from. How do I account for the parameter?

 

[gbscobel]

Ok, I think I have that figured out, runs without errors now on the query. So now I'm down to the emailing. Here is the issue. The query can and often does return multiple records for each client which makes up the detail section of the report. When I run this through the procedure just as an open report, it works fine and gives me each report just as if I were running it without any procedure at all which I guess is normal. When I try it with the SendObject, it essentially sends out the first customer's report for each record in the recordset to the correct email address. Somehow I'm missing how to get it to generate each customer's individual letter and I guess how to make it only run once for each client. I can certainly setup a separate table or query with just the client list with their emails but not sure how to bring all this together.

 

[GShen]

I am just tired. Sorry. I am not sure. Try something like:

Dim qdf As DAO.QueryDef
Dim qdfs As DAO.QueryDefs
Set qdfs = db.QueryDefs
Set qdf = qdfs("Detail Report")
qdf.Parameters("Your paramter name") = parameter value

Set rs = qdf.OpenRecordset

That should work.

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

 

[GShen]

I don't fully understand what you are trying to do.

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

 

[gbscobel]

Gshen,

You've been very patient and I appreciate all the help. Let me try to clarify the situation.

1. I have a letter I generate for my clients which list any support we have done for them over the month and gives them a balance of their support hours. The query this letter is based on typically returns multiple records per client each representing one support incident during the month.
2. I run this letter and print it as a whole so I get around 100 letters in one batch all printed and they are all folded and mailed to the clients.
3. What I want to do is generate the letters but have each client's letter email to the client's email address as an attachment. I understand the best way to do this is generate each client's letter separately, then email it, then move to the next client and repeat until the recordset has been moved all the way through.
4. Everything you've had me do so far is working, if all I wanted to do was print the letters just for the members of the recordset, essentially doing programatically what I am already doing using a macro.
5. When I attempt to replace the DoCmd.OpenReport command with a DoCmdSendObject command, it steps through all the records of my base query which is the first problem because it is generating multiple emails when a client has more than one detail record. The second problem is it only generates the letter for the first client over and over for each record and never generates it for any further clients.
6. I'm certain I'm just lacking understanding of a vital step or two in this process but that's where I am.

Hopefully, this clarifies what I'm trying to do. Please believe I am extremely appreciative for all your help so far.

 

[GShen]

Hi,
No problem. I need the report also. Is it stepping thru properly for the individual printing of the reports /
Can you please post me a copy of your DB like you did last time ? Put some notes in there. I will step thru it. Sometimes visualizing is better. Maybe you are missing something but are inadvertently not telling me. So you want 1 letter for each customer. Do you have the link in the report properly to print it 1 at a time ? Send me the example. Did you add the email address to the query ?

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

 

[gbscobel]

Ok, the database is on the ftp site again, ftp.qualitech.net, subdirectory Access. I'm not sure where exactly to put notes on my database. I sent you a barebones structure with my Print Reports form with the Sample Code button that runs the procedure. The Hours Status report is there which is what needs to run for each client and the Detail report is there with it's supporting tables and queries which is the backbone of the report. I also created an email table which just has the email addresses but haven't been able to figure out how to use it.

I notice once I moved everything over to this smaller database that the procedure no longer works at all so I don't know what the story is with that, I think I have everything there but when I try to just preview the report nothing happens and when I try to email I get run-time error 2501 so hopefully there's something easy that's missing in the sample.

When you ask if I have the link in the report properly to print 1 at a time, I'm not sure what you mean so maybe that's my problem, I'm just not understanding the question.

 

[GShen]

Let me clarify. 1st of all try and name your fields and reports, forms, etc. to proper names. It is a little confusing. You have a form named "Print Reports", you have a query named "Detail Report", you have a table named "Report". Okay that being said:

1) Looks like your query runs of a date (Sort Date). Whatever I put on your form (Report), the resulting query is always the same. That needs to be fixed.
2) Not sure what the table EMAIL does for you. Delete it.
3) Once you get that fixed, then you reference all the feilds in your query (Detail Report).
4) Do NOT TRY and email this. Try and get the report working 1st. You have your report set to the query, that is not what you want. You will end up with all the clients everytime (eg. for each record in your recordset). You want 1 report for each record in your record set by client I presume.

Here is one way of doing it.
You want your report set to a client being returned from the query which resides in the recordset. (fix the query 1st). You can create a field on your form called client. Create one for Email as well. You can set the criteria for the report based on that field. Something like you had originaly ( DoCmd.OpenReport "Hours Status", acViewPreview, "", "[client]=rstStandardHours.Client")
You can get the email just by referencing the recordset.
rststandardhours!email. You cannot use a period. It needs a ! (EG. rstStandardHours.Client should be rstStandardHours!Client).

Also add this aftter your sub line and get the code at the end as well. It will trap your errors.

Private Sub Command215_Click()
On Error GoTo Err_Command215_Click <--- add this line

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)
--->get rid of this line Set rstEmail = dbCustomer.OpenRecordset("Email")
Do While Not rstEmail.EOF 'this will start at record 1 returned from the recordset and keep given you the next query record till it is exhausted.
field on Form for client = rstStandardHours!Client
field on form for email = rstStandardHours!email
----> fix this to point to the field on your form to point to the proper client DoCmd.OpenReport "Hours Status", acViewPreview, "", "", acNormal
rstEmail.MoveNext -- this will get the next record in the recordset and process it.
Loop
rstEmail.Close

Set rstEmail = Nothing


Exit_Command215_Click: <---- add all this and below.
Exit Sub
Err_Command215_Click:
MsgBox Err.Description
Resume Exit_Command215_Click
End Sub

---------------------------

Not sure what else I can give you. Get the query fixed. Fix your report. Get your loop working properly for the report, then worry about the email piece.

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

 

[gbscobel]

Okay, the query in question is not a problem, it's just that what I've sent you only has a limited number of records for a test, I didn't send the entire data set that gets parsed based on the date. I'm just not understanding this bit about putting a field on the form for the client and then referencing it in the procedure. How is this going to get me a loop where the report runs for each client? Won't this just run the report once for whatever client is listed on the form? If I reference a specific client on the form, how does this ever get back to the recordset? Maybe I'm misunderstanding what you're telling me but I'm having a hard time putting all these pieces together.

I'm sorry, I know you've spent a lot of time on this, I guess I'm just not competent enough in Access to get this to work. Either I need some training or I need to find a local Access programmer to work with me on this because every time I think I understand what I'm doing, something else comes up that makes no sense to me.

 

[GShen]

I feel for you. This concept is a little confusing if you have never done it. I will try 1 more time.

The record set returned when you call it in vba, will look just like your query if run stand alone. Instead of referencing every field in the query by the actual name, you have to preface it with rstStandardHours!
1)You need to drive your report off the client. SQL is a litle different but the concept is the same. You may not need to create a field. It is the way I do it in SQL. It makes it more portable to other events. You can set the name of the field which is going to drive your report equal to rstStandardHours!client. The record set will have the 1st client name in it. Everytime you hit the movenext command it is get the next record. That record will have the next client name in it. Your report will then be set to that new client. etc., etc. till the query is exhausted and the EOF command will exit the loop.

What I trying to was to base the report on a field on your report (say client). Then if you notice in the code I sent you I change the client every time before the report opens to rstStandardHours!client. The report will then run off of the next client in the query. If you can set the report from the docmd. eg.

DoCmd.OpenReport "Hours Status", acViewPreview, "", "[client]=rstStandardHours!Client")

then you do not need the field on the form. I am not familiar with this syntax. Hopefully that makes a little more sense.


Does that make any more sense ?

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

 

[gbscobel]

I guess I'm still not sure how exactly the report is getting set to anything. I did what I think you said to do and it doesn't work at all. Here is the code I did based on your suggestion. When I run this, it prompts me for rstStandardHours!Client so I'm obviously still missing something. Also, aren't I still going to get multiple reports for the same client if there are more than one record for each client?

Here is the code I have now, what did I screw up this time?

Private Sub 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
Forms![Print Reports]! = rstStandardHours!Email
DoCmd.OpenReport "Hours Status", acViewPreview, "", "[client] = rstStandardHours!Client", acNormal
rstStandardHours.MoveNext
Loop
rstStandardHours.Close

Set rstStandardHours = Nothing

 

[GShen]

That looks pretty good. Is that the correct syntax for the open report? I pulled that from your code. I don't do it like this in SQL. If it is correct, did you remove the record source from the report ?
IF thee OPEN REPORT command works like that then you do not need:
Forms![Print Reports]![Client] = rstStandardHours!Client
Forms![Print Reports]! = rstStandardHours!Email

Did you create those 2 fields on your main form ? If you do it this way, then you can point your report to the field on your main form. That is the way it is done in SQL. But it you can explicitley set it on the OPEN REPORT command, then it is not necessary.

Think of it this way. Run you query. Look at the results. THAT IS YOUR RECORDSET.

When you do the loop in vba. It is set to the 1st record in your query. All of that data is avaible prefaced with rstStandardHours! with the same exact field names in the query. So instead of ME. you would use rstStandardHours!

I know you do not know how to use the debugger but go to the VB code and click to the left of the PRIVATE Sub code. A red dot will appear. Open up the immediate window pane.
Step through (using f8). After you set the record set to the query go to the immediate pane and type in
?rstStandardHours!client and hit enter. It should bring you back the 1st client in the query. If not your recordset is not getting set properly.

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

 

[gbscobel]

The OpenReport syntax I got by doing a macro to open the report and then converted it to visual basic and copy and pasted it and it works fine if I replace the rstStandardHours.Client with an actual Client name so the syntax is correct.

I can't remove the record source from the report itself or it won't run at all. Gives me the error 'The action or method is invalid because the form or report isn't bound to a table or query.' This instruction I don't understand at all. Are you saying I have to change every field on my report to point to the rstStandardHours.'field name' rather than the query fields?

I'm pretty sure I understand what the recordset is but what I'm not getting is how to get anything to reference it at all. No matter what I put in, it either gives me an error or will only run the report once for the first record.

I tried to implement your suggestion with the Immediate window but I am unable to step through this code for some reason. I put the red dot next the Private Sub but F8 does nothing, just beeps at me so I don't know what's up with that either.

I tried this setting the fields on the form and setting the report criteria to the the form field and that runs the report once for the first client but doesn't seem to loop at all.

 

[GShen]

Hey I just read this. I was typing fast. Sorry for the mispellings. When you click on the left and get the red dot, you have to save the form. When you execute the code, it will stop on the red dot. I forgot to tell you that. Now you can parse thru 1 line at a time using f8. When you get to the point where the record set loads, then go go the immediate window pane and look to see if the values are set properly. If you put the error code stuff in, then you will go immediately to the error box. That is why I wanted you to put in that code. Otherwise, it all looks like it is running (which I assume it is not). I don't think your recordset is getting loaded. But this will verify it.


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

 

[gbscobel]

I've saved the form, I've saved the project, I've saved everything there is to save and it still won't let me step through the code. The red dot is next to Private Sub Command215_Click() and everytime I hit F8 or go to Debug, Step Into, it just beeps at me. I just can't believe it's this hard to do something so simple.

 

[GShen]

Yes, you are getting there!!
You have to set all the fields to the recordset! It is working! Just step thru it. Actually if you changed the names already, then set the report to acNormal and it will print properly.

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

 

[GShen]

When you click on SAMPLE CODE. it will stop on the red dot and then you step thru it. I will check with you tomorrow.

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