asked on

Need all loop data in one email

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

ASKER CERTIFIED SOLUTION

Cimperiali

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sandra Smith

ASKER

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!

Cimperiali

:-)