Need all loop data in one email

Sandra Smith
Sandra Smith used Ask the Experts™
on
I have the attached email code.  However, when it generates the email, it only returns the last loop.  Where and how should I tell it to append the next loop's data to the end of the previous loop?  that is, if I have five accounts, I shoud have five loops and all the data from each loop.
Public Sub GenerateEmail()
'Generates email to manager
Dim strUpdateTitle      As String
Dim strUpdateHistory    As String
Dim strUpdateCurrent    As String
Dim qdfUpdateHistory    As QueryDef
Dim qdfUpdateCurrent    As QueryDef
Dim strTaskID           As String
Dim strTaskTitle        As String
Dim rst                 As DAO.Recordset
Dim rstHistory          As DAO.Recordset
Dim rstCurrent         As DAO.Recordset
Dim objOutApp           As Object
Dim objOutMail          As Object
Dim strMailBody         As String
Dim strEmailAddress     As String

strEmailAddress = "someaddress.com"

Set objOutApp = CreateObject("Outlook.Application")
Set objOutMail = objOutApp.CreateItem(0)

'First, need to get the TaskIDs for this week's updates
Set rst = CurrentDb.OpenRecordset("Select DISTINCT TaskID, TaskTitle FROM qryTasks_UpdatesEmail")
rst.MoveFirst

With rst
  Do While Not .EOF
If fntDoesObjectExist("qryUpdateHistory", "Query") Then DoCmd.DeleteObject acQuery, "qryUpdateHistory"
If fntDoesObjectExist("qryUpdateCurrent", "Query") Then DoCmd.DeleteObject acQuery, "qryUpdateCurrent"
strTaskID = rst!TaskID
strTaskTitle = rst!TaskTitle

strMailBody = "<p><b><Font Color=Black>" & strTaskTitle & "</Font></b></p>" & vbCrLf
  
Set qdfUpdateHistory = CurrentDb.CreateQueryDef("qryUpdateHistory", "SELECT Update " & _
                                         "FROM qryTasks_UpdatesEmailHistorical " & _
                                         "WHERE TaskID = '" & strTaskID & "' " & _
                                         "ORDER BY UpdateDate")
DoCmd.OpenQuery "qryUpdateHistory"
DoCmd.Close acQuery, "qryUpdateHistory"
Set rstHistory = CurrentDb.OpenRecordset("qryUpdateHistory", dbOpenForwardOnly)
With rstHistory
    strMailBody = strMailBody & "<p><Font Color=Black>" & ![Update] & "</Font></p>"
End With

Set qdfUpdateCurrent = CurrentDb.CreateQueryDef("qryUpdateCurrent", "SELECT Update " & _
                                        "FROM qryTasks_UpdatesEmail " & _
                                         "WHERE TaskID = '" & strTaskID & "' ORDER BY TimeStamp ")
DoCmd.OpenQuery "qryUpdateCurrent"
DoCmd.Close acQuery, "qryUpdateCurrent"
Set rstCurrent = CurrentDb.OpenRecordset("qryUpdateCurrent", dbOpenForwardOnly)

With rstCurrent
    strMailBody = "<p>" & strMailBody & "<Font Color=Blue>" & ![Update] & "</Font></p>"
End With

'Be sure to close old queries before re-using, just to clean up
    rstHistory.Close
    rstCurrent.Close
    Set rstHistory = Nothing
    Set rstCurrent = Nothing
'Go to next record in outside loop
    rst.MoveNext
    strMailBody = strMailBody
Loop
End With

    With objOutMail
        .To = strEmailAddress
        .Subject = "Weekly Updates " & Format(Date, "mm/dd/yyyy")
        .HTMLBody = strMailBody
        .Display
    End With
    On Error GoTo 0

    Set objOutMail = Nothing
    Set objOutApp = Nothing

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
here:

declare another string variable on the behinnig of your routine like
dim TotalBody as string

then go here:
'Go to next record in outside loop
    rst.MoveNext
    strMailBody = strMailBody

and replace last prevoius line with
 TotalBody = TotalBody  &  strMailBody
Sandra SmithRetired

Author

Commented:
was heading kinda in that direction with

'Go to next record in outside loop
    rst.MoveNext
    strBodyAll = strBodyAll & strMailBody
Loop

This does work up to a point and I think my next problem is another question.  That is, in the result set, if there is more than one row, it only returns one rather than all the rows.  Say, historical shoudl ahve three or four rows,but it only returns one.  That is another question as this you answered.  Thank you!
:-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial