Adding report content into e-mail Body

[Toasty]

Hello,



I am trying to work my way into putting the content of my report into the body of an e-mail. I have used the following code to do so but I keep on getting the error: "An Expression You Entered Is the Wrong Data Type for one of the Arguments"

Private Sub Command278_Click()
 On Error GoTo ER
 Dim db As Database
 Dim rstOpenPurchaseOrders As Recordset
 Dim strSQL As String
 Dim strSubject, strBody, strAddresses As String
 Set db = CurrentDb()
 strSQL = "SELECT * FROM qry_POOpenOrdersPerVendor;"
 Set rstOpenPurchaseOrders = db.OpenRecordset(strSQL, dbOpenDynaset)
 If Not rstOpenPurchaseOrders.EOF Then
 strBody = "blah blah blah" & vbCrLf & vbCrLf
 strBody = strBody & "Partnumber" & vbCrLf
 strBody = strBody & "==========" & vbCrLf
 rstOpenPurchaseOrders.MoveFirst
 Do While Not rstOpenPurchaseOrders.EOF
 strBody = strBody & rstOpenPurchaseOrders![po_partnr] & vbCrLf
                       rstOpenPurchaseOrders.MoveNext
          Loop
      End If
      strSubject = "Here's my report!"
      strAddresses = "look.look@nmhg.com"
      DoCmd.SendObject acSendNoObject, , acFormatTXT, Me.E_mail_Adress, , , strSubject, strBody, True
      Exit Sub
ER:
     MsgBox Err.Number & " " & Err.Description
     Exit Sub
 End Sub

I am clueless.

Thanks all!

Best Regards,

Toasty

[ItsMe]

It helps if you tell readers where the error is occurring, what line of code. You can determine this by using the Debug option when the Run Time Exception occurs.

I would think it would work anyway but, you may want to change
Dim strSubject, strBody, strAddresses As String

to
Dim strSubject As String
Dim strBody As String
Dim strAddresses As String

[Toasty]

I can not get the debug to work. As you said it would be a lot more easy if the line is marked that creates the error but when the error occurs, I can not start the debug mode.

Your tip to separate the string reference does not work. I get the same error. How can I start the debug mode?

[June7]

Disable the error handler code until the procedure runs properly. Comment the On Error GoTo line.

Review link at bottom of my post for debug guidelines.

[Toasty]

Thanks June7! It works. My Debugging quest found out that there is nothing wrong with the code, but if there are a lot of lines, the code simply can not process the data. So now I have to find a way to filter my query in a report or form. I know that a report does not filter a query. This is not the correct thread to ask this. Thanks all for the help!

[June7]

Too much data for email - that's a new one. How will filtering report data help you with this email? I presume the recordset query is already filtered to the dataset you need to transmit.

[Toasty]

Well the people working with the report are able to filter the report in the report view. It can have a lot of rows, to sometimes they filter on (by example Partnumber): Equals " 00001 ". When they do this they get the correct partnumbers and can mail the results. Due to the fact that my code links to the underlaying query and not to the report itself, the complete dataset will be set up in the e-mail. Is there a way to filter the query in the report (and ofcourse not when running the report but when the report is already shown)? That would fix two problems! Thanks

[ItsMe]

Perhaps a debug.print line of strBody here would be beneficial

 Do While Not rstOpenPurchaseOrders.EOF
 strBody = strBody & rstOpenPurchaseOrders![po_partnr] & vbCrLf
                       rstOpenPurchaseOrders.MoveNext
          Loop

[Toasty]

I have added a filter option like this one in my query:

popartnrfilter: (IIf([Forms]![frm_POOpenOrdersPerVendor]![poPartnrFilter] Is Null,True,[po_partnr]=[Forms]![frm_POOpenOrdersPerVendor]![poPartnrFilter]))

With the following criteria:

<>False

This allows me to filter my query by using an unbound field in my form. The filtering works perfect. But when I try to export the data to outlook this results in the following error: 3061 too few parameters. Expected 1.

Could it be related to the "updating" of my recordset?

[June7]

Don't use Is Null in IIf() expression, use IsNull.

IIf(IsNull([Forms]![frm_POOpenOrdersPerVendor]![poPartnrFilter])

[Toasty]

Hi June,

I tried it but unfortunately without success. I think is has something to do with my first code and the updating of my recordset due to the filtering of my query after the form is run. Could that has something to do with it?

Thanks.

[Toasty]

To make things more clear, here is my file: TestFile.zip

I really hope someone can help me.

Best regards,

[ItsMe]

Something looks strange to me with your code. Maybe if you looked closer at it. Have you tried debugging?

Do While Not rstOpenPurchaseOrders.EOF
 strBody = strBody & rstOpenPurchaseOrders![po_partnr] & vbCrLf
                       rstOpenPurchaseOrders.MoveNext
          Loop
      End If
      strSubject = "Here's my report!"

[Toasty]

Yes I did and the error occurs in this line:

 Set rstOpenPurchaseOrders = db.OpenRecordset(strSQL, dbOpenDynaset)

Again, everything was fine, untill I tried to filter the query after the form was loaded.

[ItsMe]

I doubt this line has anything to do with the properties of your Form
Set rstOpenPurchaseOrders = db.OpenRecordset(strSQL, dbOpenDynaset)

However, setting your form aside, you could try
Set rstOpenPurchaseOrders = db.OpenRecordset(strSQL, dbOpenSnapshot)