By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

emailing query results in access vba

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command9_Click()
  2.         'Send the E-Mail
  3.  
  4.         Dim oApp As Outlook.Application
  5.         Dim oMail As MailItem
  6.         Dim Subjectline As String
  7.         Dim emailaddr As String
  8.         Dim SC As String
  9.         Dim PO As String
  10.         Dim SalesC As String
  11.         PO = Forms![Orders].[PO_NUM]
  12.         SC = DLookup("SalesChannel", "orders", "PO_NUM ='" & PO & "'")
  13.         emailaddr = DLookup("Cust_EMAIL", "orders", "PO_NUM ='" & PO & "'")
  14.         If SC = "1" Then
  15.             SalesC = "DoD EMALL"
  16.         Else
  17.             If SC = "2" Then
  18.                 SalesC = "GSA Advantage"
  19.             Else
  20.             SalesC = ""
  21.             End If
  22.         End If
  23.  
  24.         Subjectline = "Tracking Inforamtion for your " & SalesC & " Order " & PO
  25.         Set oApp = CreateObject("Outlook.application")
  26.  
  27.         Dim MyBodyText As String
  28.  
  29.         MyBodyText = "QUERY RESULTS SOMEHOW"
  30.  
  31.  
  32.         Set oMail = oApp.CreateItem(olMailItem)
  33.         oMail.Body = MyBodyText
  34.         oMail.Subject = Subjectline
  35.         oMail.To = emailaddr
  36.         'oMail.Send
  37.         oMail.Display
  38.         Set oMail = Nothing
  39.         Set oApp = Nothing
  40.  
  41. End Sub
I just need to know how to run the following sql statement
Expand|Select|Wrap|Line Numbers
  1. SELECT ShippingAndDelivery.TrackingReceived, ShippingAndDelivery.TrackingNumber, ShippingAndDelivery.ShipMethod
  2. FROM Orders INNER JOIN ShippingAndDelivery ON Orders.PO_NUM = ShippingAndDelivery.PONbr
  3. WHERE (((Orders.PO_NUM)="MOMS00006806531"));
the PO_NUM will come from the form

Any help would be tremendous!
Jun 18 '14 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Jun 18 '14 #2

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1.         Dim rs As DAO.Recordset
  2.         Dim SQL As String
  3.  
  4.         SQL = "SELECT ShippingAndDelivery.TrackingReceived, " & _
  5.           "ShippingAndDelivery.TrackingNumber, ShippingAndDelivery.ShipMethod " & _
  6.           "FROM Orders INNER JOIN ShippingAndDelivery ON Orders.PO_NUM = ShippingAndDelivery.PONbr" & _
  7.           "WHERE (((Orders.PO_NUM)='" & PO & "'"
  8.  
  9.         Set rs = CurrentDb.OpenRecordset(SQL)
  10.  
  11.         'Check to see if the recordset actually contains rows
  12.         If Not (rs.EOF And rs.BOF) Then
  13.             rs.MoveFirst 'Unnecessary in this case, but still a good habit
  14.             Do Until rs.EOF = True
  15.  
  16.                 rs.someCommand to get the complete row
  17.                  there are 3 fields in the output
  18.  
  19.                 MyBodyText = "QUERY RESULTS SOMEHOW"
  20.  
  21.                 rs.MoveNext
  22.             Loop
  23.         Else
  24.             MsgBox "There are no records in the recordset."
  25.         End If
  26.  
  27.         MsgBox "Finished looping through records."
  28.  
  29.         rs.Close 'Close the recordset
  30.         Set rs = Nothing 'Clean up
Thank you for your reply and assistance!
Jun 18 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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...

Expand|Select|Wrap|Line Numbers
  1. 'This is air code... you need to finsh.
  2. Sub bytes_957268()
  3.     Dim zDB As DAO.Database
  4.     Dim zHoldString As String
  5.     Dim zMessage As String
  6.     Dim rs As DAO.Recordset
  7.     Dim SQL As String
  8.     '
  9.     '>In the SQL You have an undeclared variable
  10.     Dim PO As String
  11.     '
  12.     '>Error traps can be your friend
  13.     On Error GoTo zerr
  14.     '.
  15.     'SQL = "SELECT ShippingAndDelivery.TrackingReceived, " & _
  16.         "ShippingAndDelivery.TrackingNumber, ShippingAndDelivery.ShipMethod " & _
  17.         "FROM Orders INNER JOIN ShippingAndDelivery ON Orders.PO_NUM = ShippingAndDelivery.PONbr" & _
  18.         "WHERE (((Orders.PO_NUM)='" & PO & "'"
  19.     '
  20.     Set zDB = CurrentDb()
  21.     Set rs = zDB.OpenRecordset(Name:=SQL, Type:=adOpenDynamic)
  22.  
  23.     'Check to see if the recordset actually contains rows
  24.     'I don't depend on the EOF/BOF thing.... if there's even a single record then the count isn't zero.
  25.     'If Not (rs.EOF And rs.BOF) Then
  26.     If rs.RecordCount > 0 Then
  27.         rs.MoveFirst 'Unnecessary in this case, but still a good habit
  28.                         '>I wouldn't bet on this being unnecessary
  29.         With rs
  30.             Do
  31.                 'rs.someCommand to get the complete row
  32.                 ' - getrows method; however, it really doesn't help you here.
  33.                 'instead add the value of each field in the SQL:
  34.                 '
  35.                 zHoldString = !.TrackingReceived, & "," & '.... finish coding here
  36.                 zMessage = zMessage & zHoldString
  37.                 .MoveNext
  38.                 If Not .EOF Then zMessage = zMessage & Chr(10) & Chr(13)
  39.             Loop Until .EOF
  40.         End With
  41.     Else
  42.         MsgBox "There are no records in the recordset."
  43.     End If
  44.         MsgBox Prompt:=zMessage, Title:="Finished looping through records."
  45. ZCleanUp:
  46.     rs.Close
  47.     If Not rs Is Nothing Then Set rs = Nothing
  48.     If Not zDB Is Nothing Then Set zDB = Nothing
  49. Exit Sub
  50. zerr:
  51.     MsgBox Err.Number & vbCrLf & Err.description
  52.     Stop
  53.     Resume ZCleanUp
  54. End Sub
Jun 19 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.