send a report as the body of an email

Hi All,
I'm using a code somebody posted here. I extract the records from a
query which gave me just the entries for the current date, there will
be maximum 5-6 entries per days and I need to send them as the body of
an email. The code partially works and what happens is that it will
send an email per every record when what I need is to send just an
email, at the end of the day, including all the entries made(Rack,
CompanyName and ComponentName are the records from the query). I want
to try moving "pst.send" below ".MoveNext" this is an idea that just
ocurrs to me, I don't know it will works, I just started taking vb
classes, kind of a beginner. Here is the code I'll appreciate if
someone can help me:
************************************************** ************************** Sub sendEmail()
Dim olk As Outlook.Application
Dim pst As Outlook.MailItem
Dim rst As Recordset
Dim db As Database
Dim usr As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset("ASPComponentShipped")
Set olk = CreateObject("outlook.application")

With rst
Do Until .EOF
usr = "pi************@epi.epson.com"
Set pst = olk.CreateItem(olMailItem)

With pst
.ReadReceiptRequested = False
.To = usr
.Subject = "ASP Component Shipped"
.Body = "EAI Gemini Support," & vbCrLf & vbCrLf &
"The following ASP component requests were fullfilled today:" _
& vbCrLf & vbCrLf & Rack & " " & Companyname & " "
& ComponentName _
& vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "EPI
Gemini Support"
.Send
End With

.MoveNext
Loop
End With
End Sub

"pilar" <ca***********@hotmail.com> wrote in message
news:6b**************************@posting.google.c om...

Hi All,
I'm using a code somebody posted here. I extract the records from a
query which gave me just the entries for the current date, there will
be maximum 5-6 entries per days and I need to send them as the body of
an email. The code partially works and what happens is that it will
send an email per every record when what I need is to send just an
email, at the end of the day, including all the entries made(Rack,
CompanyName and ComponentName are the records from the query). I want
to try moving "pst.send" below ".MoveNext" this is an idea that just
ocurrs to me, I don't know it will works, I just started taking vb
classes, kind of a beginner. Here is the code I'll appreciate if
someone can help me:

************************************************** **************************

Sub sendEmail()
Dim olk As Outlook.Application
Dim pst As Outlook.MailItem
Dim rst As Recordset
Dim db As Database
Dim usr As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset("ASPComponentShipped")
Set olk = CreateObject("outlook.application")

With rst
Do Until .EOF
usr = "pi************@epi.epson.com"
Set pst = olk.CreateItem(olMailItem)

With pst
.ReadReceiptRequested = False
.To = usr
.Subject = "ASP Component Shipped"
.Body = "EAI Gemini Support," & vbCrLf & vbCrLf &
"The following ASP component requests were fullfilled today:" _
& vbCrLf & vbCrLf & Rack & " " & Companyname & " "
& ComponentName _
& vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "EPI
Gemini Support"
.Send
End With

.MoveNext
Loop
End With
End Sub

I would start by splitting this code into parts. One of them would be a
function to retrieve the text from the tables:

Function GetShipText() As String

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ASPComponentShipped", dbOpenForwardOnly,
dbReadOnly)

While Not rst.EOF
strInfo = strInfo & rst!Rack & vbTab
strInfo = strInfo & rst!CompanyName & vbTab
strInfo = strInfo & rst!ComponentName & vbCrLf
rst.MoveNext
Wend

GetShipText = strInfo

Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Then you could have in your sub:

Dim strBody

strBody = GetShipText()

If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
strBody = "The following ASP component requests were fullfilled today:"
& vbcrlf & strBody
End if

...
...
etc