How can I get all recordset results into email body

[ebasshead]

Hi Everybody,
I'm having trouble getting all recordset records into the body of an email. I just get one when there should be more. Could you please checkout my code to see where I'm going wrong. Thank you. Eddie...

Private Sub Command0_Click()
  Dim rs As dao.Recordset
  Dim strSql As String

strSql = "SELECT tblRRCustomers.ArriveDate, tblRRCustomers.Property, [CustFirstName] & "" "" & [CustLastName] AS Name, tblRRCustomers.ArriveTime, tblRRCustomers.DepartDate, tblRRCustomers.DepartTime, tblRRCustomers.Adults, tblRRCustomers.Children, Format([ArriveDate],""dddd  mmm dd"") & "",  "" & Format([ArriveTime],""hh\.nn\.AM/PM"") & "" to "" & Format([DepartDate],""dddd mmm dd yyyy"") & "",  "" & Format([DepartTime],""hh\.nn\.AM/PM"") AS DateLine, tblRROwners.IDOwners, tblRROwners.OwnerFirstName, tblRROwners.OwnerLastName, [OwnerFirstName] & "" "" & [OwnerLastName] AS Owner " & vbCrLf & _
"FROM tblRROwners INNER JOIN (tblRRProperty INNER JOIN tblRRCustomers ON tblRRProperty.Property = tblRRCustomers.Property) ON tblRROwners.IDOwners = tblRRProperty.IDOwners " & vbCrLf & _
"WHERE (((tblRRCustomers.ArriveDate)>=Now()) AND ((tblRROwners.IDOwners)=10) AND ((tblRRCustomers.BookingStatus)=2 Or (tblRRCustomers.BookingStatus)=3)) " & vbCrLf & _
"ORDER BY tblRRCustomers.ArriveDate, tblRRCustomers.Property;"

 Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
 Do While Not rs.EOF
     
     strMSg = rs!DateLine & ", " & rs!Property & ", " & rs!Property & vbCrLf
     
    
     rs.MoveNext
     Loop
     
DoCmd.SendObject acSendNoObject, , , "heriage@oyster.net.ck", , , "Confirmation Test", strMSg, True

End Sub

[NeoPa]

I suspect your line #13 wants to say :

strMsg = strMsg & {some separator character} & rs!DateLine ...

It also appears clear that this code has never been compiled properly. Please see, and follow the instructions in, Before Posting (VBA or SQL) Code before next posting code.

[ebasshead]

Thank you so much Neo, that was the nail on the head,
Cheers Eddie

[NeoPa]

Good to hear :-)