emailing query results in access vba

[slburke67]

I have a database we use for order tracking. I collect information such as the vendor we purchased from as well as tracking information from each vendor for each order. Sometime the orders require multiple vendors and multiple tracking numbers. What I would like is a button to send all the tracking numbers for an order to the customers email.

this is what I have so far:

Private Sub Command9_Click()
        'Send the E-Mail
 
        Dim oApp As Outlook.Application
        Dim oMail As MailItem
        Dim Subjectline As String
        Dim emailaddr As String
        Dim SC As String
        Dim PO As String
        Dim SalesC As String
        PO = Forms![Orders].[PO_NUM]
        SC = DLookup("SalesChannel", "orders", "PO_NUM ='" & PO & "'")
        emailaddr = DLookup("Cust_EMAIL", "orders", "PO_NUM ='" & PO & "'")
        If SC = "1" Then
            SalesC = "DoD EMALL"
        Else
            If SC = "2" Then
                SalesC = "GSA Advantage"
            Else
            SalesC = ""
            End If
        End If
 
        Subjectline = "Tracking Inforamtion for your " & SalesC & " Order " & PO
        Set oApp = CreateObject("Outlook.application")
 
        Dim MyBodyText As String
 
        MyBodyText = "QUERY RESULTS SOMEHOW"
 
 
        Set oMail = oApp.CreateItem(olMailItem)
        oMail.Body = MyBodyText
        oMail.Subject = Subjectline
        oMail.To = emailaddr
        'oMail.Send
        oMail.Display
        Set oMail = Nothing
        Set oApp = Nothing
 
End Sub

I just need to know how to run the following sql statement

SELECT ShippingAndDelivery.TrackingReceived, ShippingAndDelivery.TrackingNumber, ShippingAndDelivery.ShipMethod
FROM Orders INNER JOIN ShippingAndDelivery ON Orders.PO_NUM = ShippingAndDelivery.PONbr
WHERE (((Orders.PO_NUM)="MOMS00006806531"));

the PO_NUM will come from the form

Any help would be tremendous!

[zmbd]

Does your query work?
That is to say, if you set it up as a normal query does it return the information you desire?

If it does, then you have several ways of sending this as an attacment using the docmd.sendobject or you can build the message text by opening the query as a recordset within the VBA code and looping thru the records to build the message string, or thru even more advanced automation should you be using Outlook you could insert a table and then populate the table with the recordset results.

[slburke67]

The query does work as I want it to and I an trying to build the message body as a record set right now. Im not after pretty - only functional at this moment. Plus most of the people I need to email only receive emails in text mode so a table would be not as effective. Can you point me to the correct recordset command to get what I need. I have made a little progress on the code:

        Dim rs As DAO.Recordset
        Dim SQL As String
 
        SQL = "SELECT ShippingAndDelivery.TrackingReceived, " & _
          "ShippingAndDelivery.TrackingNumber, ShippingAndDelivery.ShipMethod " & _
          "FROM Orders INNER JOIN ShippingAndDelivery ON Orders.PO_NUM = ShippingAndDelivery.PONbr" & _
          "WHERE (((Orders.PO_NUM)='" & PO & "'"
 
        Set rs = CurrentDb.OpenRecordset(SQL)
 
        'Check to see if the recordset actually contains rows
        If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rs.EOF = True
 
                rs.someCommand to get the complete row
                 there are 3 fields in the output
 
                MyBodyText = "QUERY RESULTS SOMEHOW"
 
                rs.MoveNext
            Loop
        Else
            MsgBox "There are no records in the recordset."
        End If
 
        MsgBox "Finished looping through records."
 
        rs.Close 'Close the recordset
        Set rs = Nothing 'Clean up

Thank you for your reply and assistance!

[zmbd]

It is very importaint that you properly format posted script, be it SQL, VBA, etc... or formated text (ie table) using the [CODE/] button in the format toolbar. It helps keep things like my enterprise AV from tripping malware warnings and it gives us a way to easily reference lines of code etc within your posts.

Rabbit has kindly done that for you in your last post (Thank You Rabbit (^_^) )

Starting on Line16:
Sorry to say there is no single command to pull the entire record at once (well there is; however, it pulls to multi-dimensional array so you end up looping.

actuall there are a few other things... I'll fix some here and then leave the rest for you to code...

'This is air code... you need to finsh.
Sub bytes_957268()
    Dim zDB As DAO.Database
    Dim zHoldString As String
    Dim zMessage As String
    Dim rs As DAO.Recordset
    Dim SQL As String
    '
    '>In the SQL You have an undeclared variable
    Dim PO As String
    '
    '>Error traps can be your friend
    On Error GoTo zerr
    '.
    'SQL = "SELECT ShippingAndDelivery.TrackingReceived, " & _
        "ShippingAndDelivery.TrackingNumber, ShippingAndDelivery.ShipMethod " & _
        "FROM Orders INNER JOIN ShippingAndDelivery ON Orders.PO_NUM = ShippingAndDelivery.PONbr" & _
        "WHERE (((Orders.PO_NUM)='" & PO & "'"
    '
    Set zDB = CurrentDb()
    Set rs = zDB.OpenRecordset(Name:=SQL, Type:=adOpenDynamic)
 
    'Check to see if the recordset actually contains rows
    'I don't depend on the EOF/BOF thing.... if there's even a single record then the count isn't zero.
    'If Not (rs.EOF And rs.BOF) Then
    If rs.RecordCount > 0 Then
        rs.MoveFirst 'Unnecessary in this case, but still a good habit
                        '>I wouldn't bet on this being unnecessary
        With rs
            Do
                'rs.someCommand to get the complete row
                ' - getrows method; however, it really doesn't help you here.
                'instead add the value of each field in the SQL:
                '
                zHoldString = !.TrackingReceived, & "," & '.... finish coding here
                zMessage = zMessage & zHoldString
                .MoveNext
                If Not .EOF Then zMessage = zMessage & Chr(10) & Chr(13)
            Loop Until .EOF
        End With
    Else
        MsgBox "There are no records in the recordset."
    End If
        MsgBox Prompt:=zMessage, Title:="Finished looping through records."
ZCleanUp:
    rs.Close
    If Not rs Is Nothing Then Set rs = Nothing
    If Not zDB Is Nothing Then Set zDB = Nothing
Exit Sub
zerr:
    MsgBox Err.Number & vbCrLf & Err.description
    Stop
    Resume ZCleanUp
End Sub