Not Looping as I expected... (1 Viewer)

alexfwalker81

Member
Local time
Today, 04:00
Joined
Feb 26, 2016
Messages
90
The code below isn't functioning as I expected it to... In short, I've added in the 'While Not rs. EOF', down to 'Wend', in the expectation that it would loop through all the records in my query and drop them all into the text body of the email that I'm sending. Oddly though, the only record that comes through on the email is the final one in the query.

Any pointers?

Code:
Public Function SendEmailStatus()

'If Format(Time(), "HH") = "08" And Format(Time(), "MM") < "30" Then

If DCount("[SKU]", "qry_status_monitor_email") > 0 Then
 
    Dim rs
    Dim db As DAO.Database
    Dim mail    As CDO.MESSAGE
    Dim config  As CDO.Configuration
    
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("qry_status_monitor_email")
  
    Set mail = CreateObject("CDO.Message")
    Set config = CreateObject("CDO.Configuration")
 
    config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
    config.Fields(cdoSMTPServer).Value = "10.0.0.102"
    config.Fields(cdoSMTPServerPort).Value = 25
    config.Fields.Update
 
    Set mail.Configuration = config
       



    With mail
        .To = " blah "
        .From = " blah "
        .Subject = "Availability Status"
        
    While Not rs.EOF
    
        .TextBody = rs.Fields("sku") & " " & rs.Fields("description") & " " & rs.Fields("qty") & " " & rs.Fields("relevantstatus") & " "
        
        rs.MoveNext
    Wend
        
        .Send
    End With
    

    
End If
    Set config = Nothing
    Set mail = Nothing
    
'End If
    
End Function
 

jocph

Member
Local time
Today, 19:00
Joined
Sep 12, 2014
Messages
57
Hi. Your .Textbody was always overwritten by data from the last record, in the code.

Try this:
Code:
    With mail
        .To = " blah "
        .From = " blah "
        .Subject = "Availability Status"
        .TextBody = ""

    While Not rs.EOF
   
        .TextBody = .TextBody & rs.Fields("sku") & " " & rs.Fields("description") & " " & rs.Fields("qty") & " " & rs.Fields("relevantstatus") & " " & vbcrlf
       
        rs.MoveNext
    Wend
       
        .Send
    End With
 

alexfwalker81

Member
Local time
Today, 04:00
Joined
Feb 26, 2016
Messages
90
Hi. Your .Textbody was always overwritten by data from the last record, in the code.

Try this:
Code:
    With mail
        .To = " blah "
        .From = " blah "
        .Subject = "Availability Status"
        .TextBody = ""

    While Not rs.EOF
  
        .TextBody = .TextBody & rs.Fields("sku") & " " & rs.Fields("description") & " " & rs.Fields("qty") & " " & rs.Fields("relevantstatus") & " " & vbcrlf
      
        rs.MoveNext
    Wend
      
        .Send
    End With
Works like a charm, thank you.

I wondered if it was overwriting, but would've had no clue how to fix it!
 

Users who are viewing this thread

Top Bottom