Help with email code

[sroot]

Hey guys. i am really stuck here and am ready to give up... i really hope someone can help. I am kind of new so bear with with me. I have a database that basically emails information off to those who need to fix whatever is being sent. It works ok as long as there is not more that one record for the item and qty. I am assuming it is because i am using a Dlookup and from what i understand it will only show one record. How can i make this code work to show everything that is in the table "test"? From research maybe a openrecordset but unfortunately i have no idea how to do this. I will be so thankful for any help you can give!

Private Sub Command4_Click()
On Error GoTo Err_SendInfo_Click

    Dim varTo As Variant
    Dim varCC As Variant
    Dim stSubject As String
    Dim stItem As String
    Dim stQty As String
    Dim stPO As String
    Dim stFirstName As String
    Dim stLastName As String
    Dim stAddress1 As String
    Dim stAddress2 As String
    Dim stCity As String
    Dim stState As String
    Dim stZip As String
    Dim stPhoneInfo As String
    Dim stPhone As String



    
    varTo = DLookup("[Email]", "tblEmail")
    varCC = DLookup("[CC]", "tblEmail")
    stSubject = "RDC DTC Order Shipping Change"
    stItem = DLookup("Item", "Test")
    stQty = DLookup("Qty", "Test")
    stPO = Me.PO
    stFirstName = Me.Firstname
    stLastName = Me.lastname
    stAddress1 = Me.address1
    stAddress2 = Me.address2
    stCity = Me.City
    stState = Me.State
    stZip = Me.zip
    stPhoneInfo = Me.phoneinfo
    stPhone = Me.phone
    
        
    stText = Chr$(13) & Chr$(13) & _
             "PO: " & stPO & Chr$(13) & _
             "First Name: " & stFirstName & Chr$(13) & _
             "Last Name: " & stLastName & Chr$(13) & _
             "Address #1: " & stAddress1 & Chr$(13) & _
             "Address #2: " & stAddress2 & Chr$(13) & _
             "City: " & stCity & Chr$(13) & _
             "State: " & stState & Chr$(13) & _
             "Zip Code: " & stZip & Chr$(13) & _
             "Phone Info: " & stPhoneInfo & Chr$(13) & _
             "Phone #: " & stPhone & Chr$(13) & _
             "Item #: " & stItem & Chr$(13) & _
             "Item #: " & stQty
  
    DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1
    
    On Error GoTo Err_Execute
    CurrentDb.Execute strSQL, dbFailOnError
    On Error GoTo 0

     Exit Sub

Err_Execute:
    Resume Next
Exit_SendInfo_Click:
    Exit Sub
Err_SendInfo_Click:
    MsgBox Err.Description
    Resume Exit_SendInfo_Click

End Sub

[June7]

If you want to send info on multiple records, options:

1. open a filtered report and send it, it will attach to the email in whatever format you specify - PDF is commonly used

2. code that loops through a recordset of the data and builds a string as part of the email body

Why do you have Execute code? There is no action query statement to execute.

[sroot]

how would i do the second option? I need to have all the info in the body of the email. the execute code was for something i had in there before but i took out, forgot to take that out. I am pretty new at this and not sure how to get it to work i looping through a recordset but i couldn't figure out how to make it work with that i have. Thanks

[June7]

Look at example in http://allenbrowne.com/func-DAO.html...cordsetExample

It shows opening and looping through recordset. All it does it print a single field content to immediate window. You could instead build string, like:

strData = strData & rs!fieldname1 & ", " & rs!fieldname2 & ", " & rs!fieldname3 & vbCrLf

[sroot]

Where in the code would i put that to write it to the email?

[sroot]

OK i think i might have figured most of it out but i have two problems, 1 is that it is not looping right and just showing the first record. (i probably have the code wrong) and 2, it is showing in the email under the item part that i am trying to loop, the name of the table... any ideas?

Private Sub Command4_Click()
On Error GoTo Err_SendInfo_Click

    Dim varTo As Variant
    Dim varCC As Variant
    Dim stSubject As String
    Dim stItem As String
    Dim stQty As String
    Dim stPO As String
    Dim stFirstName As String
    Dim stLastName As String
    Dim stAddress1 As String
    Dim stAddress2 As String
    Dim stCity As String
    Dim stState As String
    Dim stZip As String
    Dim stPhoneInfo As String
    Dim stPhone As String
    Dim stRDC As String
    Dim rs As DAO.Recordset
    Dim strData As String
    
    
    
    strData = ("Test")
    Set rs = DBEngine(0)(0).OpenRecordset(strData)
    strData = strData & rs!item & " , " & rs!qty & vbCrLf
    
    
    rs.Close
    Set rs = Nothing
    
    varTo = DLookup("[Email]", "tblEmail")
    varCC = DLookup("[CC]", "tblEmail")
    stSubject = "RDC DTC Order Shipping Change"
    stItem = strData
    stPO = Me.PO
    stFirstName = Me.Firstname
    stLastName = Me.lastname
    stAddress1 = Me.address1
    stAddress2 = Me.address2
    stCity = Me.City
    stState = Me.State
    stZip = Me.zip
    stPhoneInfo = Me.phoneinfo
    stPhone = Me.phone
    stRDC = DLookup("rdc", "rdc")
        
    stText = "DTC PO Info for Heavy Goods Order for RDC " & stRDC & Chr$(13) & Chr$(13) & _
             "PO: " & stPO & Chr$(13) & _
             "First Name: " & stFirstName & Chr$(13) & _
             "Last Name: " & stLastName & Chr$(13) & _
             "Address #1: " & stAddress1 & Chr$(13) & _
             "Address #2: " & stAddress2 & Chr$(13) & _
             "City: " & stCity & Chr$(13) & _
             "State: " & stState & Chr$(13) & _
             "Zip Code: " & stZip & Chr$(13) & _
             "Phone Info: " & stPhoneInfo & Chr$(13) & _
             "Phone #: " & stPhone & Chr$(13) & _
             "Item #: " & stItem
           
  
    DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1
    
    Do While Not rs.EOF
    rs.MoveNext
    Loop
    
    Resume Next
Exit_SendInfo_Click:
    Exit Sub
Err_SendInfo_Click:
    MsgBox Err.Description
    Resume Exit_SendInfo_Click

End Sub

[June7]

Set rs = DBEngine(0)(0).OpenRecordset(strData)
stText = "DTC PO Info for Heavy Goods Order for RDC " & stRDC & vbCrLf & _ ...
Do While Not rs.EOF
stData = strData & rs!item & " , " & rs!qty & vbCrLf
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText & strData, -1

I don't understand the DLookup for stRDC. What are you trying to find with this? Probably need some filter criteria.

[sroot]

Thank you i got it working. All that does it look up a number that the user puts in for each department so whoever gets the email can file it correctly. Thanks for all the help!

[June7]

What that DLookup does is find the first value in the rdc field of rdc table. Is that what you really want?

[sroot]

That is all that is in there is just one record. But one thing i would like to do with that, if you happen to know if there is a way is to check to see if there is anything in that table (i know how to do that part) and if there is nothing then have a box pop up where they put the number in? like a parameter box, but have it write into the table rdc... is that possible? i have it just open the table right now but it looks unprofessional.

[June7]

stRDC is declared as String so it cannot be Null, it can be empty string. The procedure will throw error if there is no record in table because string variable cannot hold Null. Either declare the variable as a Variant or change code. If you want a number, why declare as string? Number variable also cannot hold Null, must be a number. Only Variant type can hold Null.

More code. Something like:
stRDC = Nz(DLookup("rdc", "rdc"),"")
If strRDC = "" Then
strRDC = InputBox(build what you want here)
CurrentDb.Execute "INSERT INTO RDC(rdc) VALUES(" & strRDC & ")"
End If

However, validating InputBox entry is tricky. What if user enters "389-"? Example from my code:

Do
strResponse = InputBox("Enter hours for cost charging.", "Check Cost", 30)
Loop Until IsNumeric(strResponse)

[sroot]

that works perfectly. thank you again!