Populate email body from query

[Jordon_jd]

Hi there,
I have very little idea of what i am doing but i am trying to create an email that is populated from a query.
It is supposed to be a shipping confirmation email that contains the the customer, product and tracking information.
I found some code and have tried adapting it but i keep getting "3061 too few parameters. expected 2"
i also have no idea as whether the rest of this is correct. Any help would be appreciated.

Option Compare Database
Option Explicit
Private Sub Command38_Click()
On Error GoTo EH
    'Recordset variables
    Dim db As Database
    Dim rstQry_Ship_Confirm As Recordset
    Dim strSQL As String
    Dim strSubject, strBody, strAddresses As String
    Set db = CurrentDb()
    strSQL = "SELECT * FROM Qry_Ship_Confirm;"
    Set rstQry_Ship_Confirm = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rstQry_Ship_Confirm.EOF Then
        strBody = "blah blah blah" & vbCrLf & vbCrLf
        strBody = strBody & "[Customer]     [Order_No]    [Model]    [Part_No]    [Qty]    [Ship_Date]    [Serial_Number]    [Tracking_No]" & vbCrLf
        strBody = strBody & "==================================" & vbCrLf
        rstQry_Ship_Confirm.MoveFirst
        Do While Not rstQry_Ship_Confirm.EOF
            strBody = strBody & rstQry_Ship_Confirm([Customer]) & Chr(9) & _
                rstQry_Ship_Confirm([Order_No]) & Chr(9) & _
                rstQry_Ship_Confirm("Model") & Chr(9) & _
                rstQry_Ship_Confirm([Part_No]) & Chr(9) & _
                rstQry_Ship_Confirm("Qty") & Chr(9) & _
                rstQry_Ship_Confirm([Ship_Date]) & Chr(9) & _
                rstQry_Ship_Confirm([Serial_Number]) & Chr(9) & _
                rstQry_Ship_Confirm([Tracking_No]) & vbCrLf
            rstQry_Ship_Confirm.MoveNext
        Loop
    End If
    strSubject = "Here's my report!"
    strAddresses = "jo****@codade.co.uk"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub

like I said above I have almost no idea what I am doing on this, I assume its something wrong with the query? maybe?

[pbaldy]

More than likely the query has form parameters, which OpenRecordset can't resolve. Simplest solution is to wrap each in the Eval() function:

Eval('Forms!FormName.ControlName')

[Micron]

Here's 2 cents more:
Dim strSubject, strBody, strAddresses As String
All variables not explicitly typed (not as in keyboard typing, but assigning a data type) are variants. Likely you should have

Dim strSubject As String, strBody As String, strAddresses As String
unless you want the non-typed ones to be variants, in which case they should be typed as such to avoid ambiguity

Object type libraries are almost always declared for objects. In this case, should be declared. Probably
Dim db As DAO.Database
Dim rstQry_Ship_Confirm As DAO.Recordset
Note that explicitly typing objects according to their library may raise an error such as "user type object undefined" or some such thing. Type libraries are on vb editor Tools>References toolbar. In some Access versions, DAO is the default, but why guess?

If Not rstQry_Ship_Confirm.EOF Then
You're only checking for EOF, which does not guarantee that there are any records. Attempting to navigate through an empty rs will raise an error. No need to confirm EOF more than once, as in your code. Also, I have found that sometimes you unexpectedly do not start at the beginning of an unordered domain when it's a table; i.e I have had recordset loops start somewhere in what appears to be the middle. If your domain (rstQry_Ship_Confirm) is a query, I cannot claim that looping through a recordset based on a query will or won't always start at the "beginning" when the query is unordered. Rather than sorting the query as a fix, I'd recommend to always do more like the below example (I'll use customary rs/rst rather than your long name). Note that I'm not saying sorting a query for a recordset is never needed, just that it's not a fix for ensuring you don't start in the middle.

Do While Not (rs.BOF And rs.EOF) 'if both are true, there are no records
rs.MoveFirst

If you need a recordset count, you have to rs.MoveLast before looping.

[Jordon_jd]

Hi,
Thankyou for the replies, I will have a look at what you have both mentioned. I Managed to get it to work last night to populate the email. I'm aiming to hopefully get to the point of not having some information repeating like order no, so I assume I should have to try and retrieve the information from a report instead.

[Jordon_jd]

Ok i dont know where to put the Eval function, or if i am putting it in the correct place it is not having the desired effect.
I tired

Eval(rstQry_Ship_Confirm([Order_No])) & Chr(9) & _
Eval(Forms!

Qry_Ship_Confirm.Order_No)
etc etc.
It worked because I removed the [enter po no] from the actual query...
I need to be able to reduce the results from the query though.
and if possible restructure the data slightly so I don't have 15 line just because the product had 15 serial numbers..

[pbaldy]

The quotes around the form reference in my example were not a typo.

[Jordon_jd]

I am clueless so apologies if this is annoying.

After a day of googling I have found something else that allows me to export my report as html and add it to the body of an email..

Private Sub Command40_Click()

Dim strline, strHTML
 Dim OL As Outlook.Application
  Dim MyItem As Outlook.MailItem
  Set OL = New Outlook.Application
  Set MyItem = Outlook.Application.CreateItem(olMailItem)
  
 DoCmd.OutputTo acOutputReport, "Rpt_Ship_Confirm", acFormatHTML, "C:\Users\XXXXXXX\Desktop\Jordon Test DB\Rpt_Ship_Confirm.html"
  
  Open "C:\Users\XXXXXXX\Desktop\Jordon Test DB\Rpt_Ship_Confirm.html" For Input As 1
  Do While Not EOF(1)
    Input #1, strline
    strHTML = strHTML & strline
  Loop
  Close 1
  ' If OL2002 set the BodyFormat
  If Left(OL.Version, 2) = "10" Then
    MyItem.BodyFormat = olFormatHTML
  End If
  MyItem.HTMLBody = strHTML
  MyItem.Display
End Sub

This is fine but I would like to somehow incorporate the recipient email, cc, and subject (especially if I can pull the order no from the query for the subject)

  Dim strSubject, strBody, strAddresses, strCC As String

 strSubject = "Here's my report!"
    strAddresses = "email@email.co.uk"
    strCC = "Blah@hotmail.com"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, strCC, , strSubject, strBody, True
    Exit Sub

Is there a way I can get this information onto the email that is generated with the top method?
can I also get populate the subject with information in the underlying query for the report?

[pbaldy]

Did you try

Eval('Forms!Qry_Ship_Confirm.Order_No')

in the query? Can you attach the db here?

[Jordon_jd]

I think i almost have it.
Not sure if everything below is necessary but it seems to do the trick minus the Order number in the Subject

Private Sub Command40_Click()

Dim strline, strHTML
 Dim OL As Outlook.Application
  Dim MyItem As Outlook.MailItem
    Dim strSubject, strBody, strAddresses, strCC As String
     Dim myRecipient As Outlook.Recipient
     
  Set OL = New Outlook.Application
  Set MyItem = Outlook.Application.CreateItem(olMailItem)
Set myRecipient = MyItem.Recipients.Add("XXX")
  
 DoCmd.OutputTo acOutputReport, "Rpt_Ship_Confirm", acFormatHTML, "C:\Users\XXXX\Desktop\Jordon Test DB\Rpt_Ship_Confirm.html"
  
  Open "C:\Users\XXXX\Desktop\Jordon Test DB\Rpt_Ship_Confirm.html" For Input As 1
  Do While Not EOF(1)
    Input #1, strline
    strHTML = strHTML & strline
  Loop
  Close 1
  ' If OL2002 set the BodyFormat
  If Left(OL.Version, 2) = "10" Then
    MyItem.BodyFormat = olFormatHTML
  End If
  MyItem.HTMLBody = strHTML
  MyItem.Display
  MyItem.Subject = "Item Shipping Confirmation, Order:"
  
 
  MyItem.CC = "Email@Email.co.uk ; 
Email@Email.co.uk
"
    Exit Sub
  
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub

[pbaldy]

If it's on the form:

MyItem.Subject = "Item Shipping Confirmation, Order: " & Me.OrderNumber

using the actual name of the control with order number in it.

[Jordon_jd]

Perfect, I think that was the last piece if the puzzle, email is being generated with the correct information, recipients populated and the order number in the subject as well.

thankyou very much

[pbaldy]

Glad you got it working and welcome to the site by the way!

[Jordon_jd]

Thank you,

I have come up with one issue,
My report is a shipment confirmation, It lists the products shipped and their serial numbers. I am finding that the exported report only seems to have a certain length? one of my emails was missing a serial number, not because the report did not have it in their but because the exported report into the email body seems to cut off at a certain length.

This is the report.



This is the code, I cannot see anything that seems to relate to a certain length??

 Option Compare Database
Private Sub Command60_Click()
Dim strline, strHTML

 Dim OL As Outlook.Application
  Dim MyItem As Outlook.MailItem
    Dim strSubject, Strbody, strAddresses, strCC As String
     Dim myRecipient As Outlook.Recipient
     
  Set OL = New Outlook.Application
  Set MyItem = Outlook.Application.CreateItem(olMailItem)
  Set myRecipient = MyItem.Recipients.Add(Forms!Orders_Form.ContactEmail)

  
 DoCmd.OutputTo acOutputReport, "Rpt_Ship_Confirm", acFormatHTML, "\\192.168.1.100\Public\Shipment Confirmation\Rpt_Ship_Confirm.html"
  
  Open "\\192.168.1.100\Public\Shipment Confirmation\Rpt_Ship_Confirm.html" For Input As 1
    
    signature = Environ("appdata") & "\Microsoft\Signatures\"
    If Dir(signature, vbDirectory) <> vbNullString Then
        signature = signature & Dir$(signature & "*.htm")
    Else:
        signature = ""
    End If
    signature = CreateObject("Scripting.FileSystemObject").GetFile(signature).OpenAsTextStream(1, -2).ReadAll
  
  Do While Not EOF(1)
    Input #1, strline
    
    strHTML = strHTML & strline
  Loop
  Close 1
    ' If OL2002 set the BodyFormat
  If Left(OL.Version, 2) = "10" Then
    MyItem.BodyFormat = olFormatHTML
  End If
  MyItem.HTMLBody = strHTML & signature
  MyItem.Display
  MyItem.Subject = "Shipping Confirmation for Order: " & Forms!Orders_Form.Order_No
  
 
  MyItem.CC = "Email@Email.co.uk ; 
Email@Email.co.uk
"
    Exit Sub
  
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub

the results is :




Basically there should be 11 serial numbers for this item, if I remove a product from the report it will display more serials of this product so it seems like the exported file only displays a certain length??

Any help would be appreciated

Thanks

Jordon

[pbaldy]

Just a guess, see if it output multiple pages as separate files in the network folder.

[Jordon_jd]

IT seems its related to the page size on the report and then additional pages being saved under "Page 2" file name etc, I tried finding a wau of making a custom page size but i ended up removing the serials from the report and creating PDF with the serials to attach to the email.