Not Looping as I expected...

[alexfwalker81]

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?

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]

Hi. Your .Textbody was always overwritten by data from the last record, in the code.

Try this:

    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]

Hi. Your .Textbody was always overwritten by data from the last record, in the code.

Try this:

    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!

 

[jocph]

Glad it works. Goodluck!