Help with email and VB 1

[DarkOne72]

Hello,

I have a form that is pulled from a query and shows the results on the form properly. On this form I have a button that sends an email to a recipient with the results from the query/form however the problem is it is only showing one of the records in the email body and not the reminaing records but other than that it sends and all is fine.

Can someone please assist me in making it show all reocrds in a single email?

Here is my code:

Option Compare Database

Private Sub cmdReady_For_Pickup_Click()
   On Error GoTo Err_cmdReady_For_Pickup_Click

    Dim stWhere As String       '-- Criteria for DLookup
    Dim varFrom As String
    Dim varTo As String        '-- Address for SendObject
    Dim varCC As String
    Dim stText As String        '-- E-mail text
    Dim stSubject As String
    Dim ID As String
    Dim strSQL As String        '-- Create SQL update statement
    Dim Label26 As String
    Dim lstEmployeePickup As String
    Dim errLoop As Error
    
    '-- Email address to
    
   varTo = "Test@test.com" 
   stSubject = "Ready For Pickup"
   
     
      stText = "Ready for Pickup" & Chr$(13) & _
              Chr$(13) & _
              "You are receiving this email because the following Devices are ready for pickup." & Chr$(13) & _
              "The technician's name and phone number is listed below." & Chr$(13) & _
              Chr$(13) & _
              Chr$(13) & _
              Chr$(13) & "Tech Name: " & [Tech_First] & " " & [Tech_Last] & _
              Chr$(13) & "Tech Number: " & [Tech_Number] & _
              Chr$(13) & "Tech's Phone #: " & [Phone_Num] & Chr$(13) & _
              Chr$(13) & "Notification Date: " & [Now] & Chr$(13)


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

     On Error GoTo Err_Execute
     On Error GoTo 0

    Exit Sub

Err_Execute:
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                   errLoop.Description
        Next errLoop
    End If

    Resume Next


Exit_cmdReady_For_Pickup_Click:
    Exit Sub

Err_cmdReady_For_Pickup_Click:
    MsgBox Err.Description
    Resume Exit_cmdReady_For_Pickup_Click

End Sub

 

[PWise]

Add
dim rst as recordset


stText = "Ready for Pickup" & Chr$(13) & _
Chr$(13) & _
"You are receiving this email because the following Devices are ready for pickup." & Chr$(13) & _
"The technician's name and phone number is listed below." & Chr$(13) & _
Chr$(13) & _
Chr$(13) & _
Set rst = me.recordset.clone
do while rst.eof

stText=stText & Chr$(13) & "Tech Name: " & [Tech_First] & " " & [Tech_Last] & _
Chr$(13) & "Tech Number: " & [Tech_Number] & _
Chr$(13) & "Tech's Phone #: " & [Phone_Num] & Chr$(13) & _
Chr$(13) & "Notification Date: " & [Now] & Chr$(13)

rst.movenxet
loop

 

[dhookom]

I would take PWise's code and place it into a function that is called with the foreign key value (or other) and returns the stText. Use the returned value in your email.

Duane
Hook'D on Access
MS Access MVP

 

[DarkOne72]

I repalced th stText with what you had and added the rst.movenext, loop and dim rst as recordset and Set rst = me.recordset.clone, do while rst.eof ,but it doesn't show any records now just the text of why youare receiving it.

 

[PWise]

Question how many records in the query /form
how many times does it loop

 

[DarkOne72]

Sorry I didn't mean to be rude last time, I thank you for your fast response and all of the assistance!
Currently there are 3 records but could be up to like 30 possibly more.

=)

 

[PWise]

Sorry my mistake should be

do while not rst.eof

 

[dhookom]

If the form doesn't have all of the records, you will need to create a recordset from a SQL statement.

There is also a FAQ in this forum on how to debug your code. You should review this so you can troubleshoot on your own.

Duane
Hook'D on Access
MS Access MVP

 

[DarkOne72]

OMG !!!!! You sir are the man! I can not believe it was a matter of "Not" stopping it. I can't thank you enough.

You are awesome!

Thank you again!

 

[DarkOne72]

Hey PWise, it is working and yes your still the man but it is showing the 3 records but it is all the same record .?!?!?!?

Sorry, didnt catch that earlier just saw the 3 records show up in email

 

[PWise]

do while rst.eof

stText=stText & Chr$(13) & "Tech Name: " & rst![Tech_First] & " " & rst![Tech_Last] & _
Chr$(13) & "Tech Number: " & rst![Tech_Number] & _
Chr$(13) & "Tech's Phone #: " & rst![Phone_Num] & Chr$(13) & _
Chr$(13) & "Notification Date: " & rst![Now] & Chr$(13)

rst.movenxet
loop

 

[DarkOne72]

Yep yep! You are the man, I added the rst! in front of them and it is working completely now! I did notice the "NOT" in the top line you just sent me..I know you were testing me! =)
Anyway thank you again for all of your help, it was much appreciated.

 

[PWise]

no testing just quick typing and copy & paste without using my head