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]

Okay, This is a loaded question. Do you need help with the recordset loop, the report creation to a pdf, the email portion or all of the above? I think you need to walk before you run if you need all 3.

Let me know I will see where I can help you.

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

 

[gbscobel]

Thanks for the response. I think my biggest problem is not really knowing how to declare a recordset and then use that to loop through and run the report for each company in that recordset. I'm pretty sure I can figure out how to add code to send the report with the pdf attachment doing the docmd.sendobject but I'm really struggling trying to figure out using a recordset and specifying a where clause so that it runs the same report with different data for each company in the recordset. If you can help me with that portion, I believe I can figure out the rest.

 

[GShen]

Ok, try this in your VBcode You have to execute it somehow, either with a command button or event or what have you. If you need help with that let me know also.
----------------------------
Private Sub Whatever()
Dim Rst As Recordset
Set Rst = Me.Recordset

Do While Not Rst.EOF

' Do Whatever you want in here. This will loop till your record set has been processed, one at a time.

Rst.MoveNext

Loop

End Sub

---------------------------
Hope that helps.

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

 

[gbscobel]

Thanks, that's a start. I'm still not clear on what the recordset represents, how does it know what data is in the recordset? Further, how do I run a report with the next recordset object as the source so it knows how to fill it in? Here's the basic scenario:

I have a form with a command button. When you push the command button, it runs a few queries to create a data set with a bunch of data fields. It then runs a report with fields from the dataset.

What I need to understand is how to assign the data set to the recordset and then how in the loop itself, I can run the report off the next record in the recordset so it creates an individual unique report for each customer and from there I can email it to an email address also from the recordset.

 

[GShen]

Okay, I will walk you thru this.
Your recordset is collection of everything on the form you are displaying. Is your database an MDB or ADP ? Are you driving it with a table, stored procedure or query ? Lets say you have a form with retrieves 30 customer records on it that you want to scroll thru. This will do it for you. When you performt the command.... Set Rst = Me.Recordset which I gave you it will set everything on the form to Rst. In other words, if you referenced something on your form as me.fieldname prior to the recordset it will now need to be referenced as rst.fieldname in order to access that particular field on the form. The 1st instance of rst.fieldname will give you the 1st reference on the form in it. After you say Rst.MoveNext, then rst.fieldname will provide you with the information for the next record on the form. Hope that makes sense.
Let me know.

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

 

[gbscobel]

Ok, I understand what you're saying. I am using an mdb file and the data will typically be in a query but may also come from a table. The problem is, the form I am using is more of a switchboard type and has no data behind it. It is a form where you can call multiple different reports all of which have different data sets which are generated when a certain report is run. In this case, there is no data for a recordset to be set to so I don't know how this applies.

Can you give me specific syntax on how I would generate a report within the vbcode you originally sent me that would be able to select a report just for the current record using a WHERE clause? I am still unsure how the report will know which specific record and data to run off.

I'm sorry to be such a pain, I'm understanding the recordset concept better, I'm just not sure how to apply it in my situation.

 

[GShen]

Okay, 1st of all you have some options. You can run vb code to generate the record set as on option.
For ex. Say you click on your button which would generate x amount of records to be printed. You can do actually set the record to that query and then parse through that set of records to execute your loop.

I can give you an example but can you please give me more info. on exactly what you are doing ? Is the scenerio I described above correct? I would assume your report is driven off a query which has the customer number set to a number which you either type in or send from a form, correct ? There 1 is report, yes ? Sorry, but you have to be more specific so I can lead to an answer and not down a path of no return. There are a lot of options.

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

 

[gbscobel]

Not a problem. Currently, the way it works is you click the button, a series of queries is run which ends up with a table of customers with various information including a customer id, email address and various other fields which fill in the blanks on a letter we print and send out. Currently, the program simply prints a letter for each customer in the table, I don't do any limiting at all.

So what I want to do is run the queries to create the table and then I would like to generate each customer's report with their own unique information and email it to their email address as stored in the table, then move on to the next customer and do the same for the next record in the dataset.

Whether emailing or printing, I don't know how to define the recordset and I don't know how to make the program step through each record, merging each one with the report and then emailing it out to the stored email address.

Does this give you enough specific information?

 

[GShen]

Ok. Good. So your table has all the info. you need to generate the reports, perfect. If that is true, all you need to do is SET the recordset SET Rst = YOUR TABLE. Look for the syntax. It may be a little different with an MDB. Then, every field in your table will be preceeded with Rst. when referencing it in VB. Your report obviously has all the parameters for all the fields.

In an ADP, the vb code to run a stored procedure is like this:

Dim spConn As ADODB.Connection
Set spConn = CurrentProject.Connection
spConn.CommandTimeout = 120
Set spcmd = New Command
Set spcmd.ActiveConnection = spConn
spcmd.CommandText = "dbo.storedprocedure"
Set sprst = spcmd.Execute

Now you would not be executing a stored procedure. Not sure of the syntax for a table. Are you using DAO or ADO ?

All you have to do is to set each one of these parameters in the loop prior to calling the report.

EG.
YourReportCustomerfield = rst.customerfieldfromTable
YourReportCustomerEmailfield = rst.EmailfieldNamefromTable

Let me know if you can get the table loaded in the recordset


Do you know how to debug at all in VBA, using the immediate pane ?


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

 

[gbscobel]

I will play with it and see if I can figure this out. Not sure how to tell if I'm using DAO or ADO but DAO seems familiar. Your code looks very different from what I'm used to seeing so I suspect MDB and ADP must use different syntax. This is what I've been able to come up with so far, the table I'm running through is called Report.

Private Sub Command215_Click()
Dim dbCustomer As Object
Dim rstStandardHours As Object

Set dbCustomer = CurrentDb
Set rstStandardHours = dbCustomer.OpenRecordset("Report")
With rstStandardHours
Do While Not .EOF
DoCmd.OpenReport "Hours Status", acViewPreview, "", "[client]=rstStandardHours.Client", acNormal
Loop
.Close
End With
Set rstStandardHours = Nothing

End Sub

This does not work, it prompts me for a rstStandardHours.Client and no matter what I type, it just locks up the database and does not respond. I don't know what's wrong.

 

[GShen]

Ok, Looks pretty good. Nos sure what the .close is for but for the the line

Do While Not .EOF

replace with

Do While Not rstStandardHours.EOF


Do you know how to step thru the debugger and use the immediate pane ? The syntax you have for the opening of the report looks a little funky. Is that correct ? You have acViewPreview and acNormal in the same line. Not sure of the mdb syntax. Have you used that before ? If not, then just hard code a client number in there and remove the record set stuff. Make sure that report works for you. Then put the recordstuff back in. Like I said, you need to walk before you run. talk to you tomorrow. Let me know about everything above.

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

 

[gbscobel]

This is the first time I've ever done any of this. The openreport command I got by creating a macro for it and then converting it to vb code so I'm assuming it's correct. I have no idea on the top stuff, I got that from another website, when I try to dim the rstStandardHours as Recordset, it keeps giving me Type mismatch errors so the only way I can make it work is by making it an object.

I see how to Add a Watch to get the immediate window to open but the code dies so quickly, I can't get back to see if it's really doing anything.

I tried the code as you suggested just doing it equal to an actual client code and it appears to try to open the report and then goes right to Not Responding and hangs up, can't do anything but close the database completely. I'm just not sure what's going on here.

 

[GShen]

Ok. I just found some MDB stuff.

Define your rec. set as:

Dim dbCustomer As DAO.Recordset

Get rid of that Acnormal at end of the DoCmd line.

The DIM should fix the recordset problem. However, keep that all commented out till you get the report opened correctly. One step at a time. Once you get the report opened then you can try the other stuff. The intellisense should work after you fix the DIM above. Eg. dbCustomer. should give you all your options for the recordset.

I will await your reply

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

 

[gbscobel]

Unfortunately, no matter what I do I can't get this to work. I even created a brand new database, brought over just the table, the report and the form with the button on it and when I try to run it, even without any parameters, it still just immediately hangs up while trying to run the report. Here is my code now after I made the changes you suggested.

Private Sub Command215_Click()
Dim dbCustomer As Database
Dim rstStandardHours As DAO.Recordset

Set dbCustomer = CurrentDb
Set rstStandardHours = dbCustomer.OpenRecordset("Report")
Do While Not rstStandardHours.EOF
DoCmd.OpenReport "Hours Status", acViewPreview, ""
Loop
rstStandardHours.Close

Set rstStandardHours = Nothing

End Sub

I am attaching this small test database. Maybe you can see something I'm missing. I have simplified it as much as I know how and still can't make this work. I appreciate your patience with this, it's driving me a little crazy.

 

[gbscobel]

I guess I don't really know how to attach files here. I have uploaded the file test.accdb to my company's ftp site. If you go to ftp.qualitech.net and navigate to the Access folder, the file is there. There is just a table, a report and a form with a Sample Code button that has the event procedure behind it. Hopefully you can see something here. Thanks again.

 

[GShen]

OK, It worked fine for me. However, I did have to add 1 line. rstStandardHours.MoveNext You need this or you will loop forever. You had a tight loop that would take your server down. Only kidding. Let me know where you need to go from here.


Private Sub Command215_Click()
Dim dbCustomer As Database
Dim rstStandardHours As DAO.Recordset

Set dbCustomer = CurrentDb
Set rstStandardHours = dbCustomer.OpenRecordset("Report")

Do While Not rstStandardHours.EOF
DoCmd.OpenReport "Hours Status", acViewPreview, ""
rstStandardHours.MoveNext ' You need this or you will loop forever.
Loop

rstStandardHours.Close

Set rstStandardHours = Nothing

End Sub


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

 

[gbscobel]

Ok, that worked. So that's a good start. I further was able to add a selection criteria for a specific client code and that worked too. So now I'm down to how to run the report based on the recordset and also how to reference the email address when I email it. I feel like we're pretty close now, thank you.

 

[GShen]

Does each customer get a report individually ? You should have the report piece working with the code I gave you. Not sure what you mean HOW TO RUN THE REPORT BASED ON THE RECORDSET. That should be working. Paste your new code here and let me know what you are trying to do. The email address can be referenced the same way, however, you would need to add it to your table, call it email for lack of a better field name. You can then access it as

rstStandardHours.Email and do as you may with it.



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

 

[gbscobel]

Each customer should get their own report, I'm going to try to apply the email syntax to this and see if it works for a smaller group of records. I think part of the problem is I just realized the report isn't based on the table called report, but a query called detail report. I tried to change the set recordset command to that but it tells me I don't have enough parameters. Is there something extra I have to do to get this to work with a query?