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, , , "[e-mail]", , , "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 :-)