Nested Iterations in Sending Emails (1 Viewer)

[david_johnson]

I have two tables. One contains a list of applicants, the other the list of contacts for that applicant that will feed the email's table. The join is on the Applicant ID, so in the contacts table 6 rows will have the same applicant ID (always 6). What I can't figure out how to do is to filter the list so that it only pulls the contacts for the matching applicant ID. I've managed to nest the iterations without help. The problem is that it pulls the entire contact set for each applicant iteration. A related problem, I can't seem to pull the applicant email for each iteration, I always only get the first applicant. I suppose my problem is that I can't seem to figure out the code to pull field values for the iteration I'm currently in at any given time.

Private Sub Command1_Click()
    Dim olApp As Object
    Dim olItem As Variant
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim strQry As String
    Dim aHead(1 To 5) As String
    Dim aRow(1 To 5) As String
    Dim aBody() As String
    Dim lCnt As Long
    Dim asPreTable As String
    Dim asPostTable As String
    Dim appsrec As DAO.Recordset
    Dim str_app_SQL As String
    Dim ApplicantID As Integer
    
    
    str_app_SQL = "SELECT * FROM applicant_list"
    Set appsrec = CurrentDb.OpenRecordset(str_app_SQL)
    
    If Not appsrec.BOF And Not appsrec.EOF Then
        appsrec.MoveFirst
        Do While Not appsrec.EOF

        
        
    'Create the header row
    aHead(1) = "Agency"
    aHead(2) = "Role"
    aHead(3) = "Name"
    aHead(4) = "Phone"
    aHead(5) = "Email"
    

     
    asPreTable = "Greetings, <br>" _
               & "<br>Addressing the unique recovery needs of disaster-impacted communities requires a collaborative effort involving state and federal agencies in conjunction with local jurisdictions and leaders. I am contacting you today to provide you with information on the various individuals and organizations that will be providing support throughout the FEMA Public Assistance (PA) recovery process.  The table below provides contact information for individuals serving in essential roles related to your community's recovery under the PA program:<br><br>" _
                         
               
    asPostTable = "<br><br><b><i>FEMA Roles and Responsibilities</i></b><br>" _
                & "FEMA is responsible for making eligibility determinations for all your reported losses and for approving funding through Project Worksheets (PWs) for eligible damages.  Over the coming weeks and months, your designated FEMA Program Delivery Manager (PDMG) will work with you extensively to assess damages and collect information/documentation required for the approval of PWs.<br><br>" _
                & "<b><i>TDEM Roles and Responsibilities</i></b><br>" _
                & "TDEM is responsible for grant administration and compliance support; this includes assistance with processing your reimbursement requests, time extensions, necessary scope of work modifications, final financial reconciliation, as well as grant closeout. As these needs surface, TDEM will provide technical assistance and policy guidance as it pertains to your PWs. TDEM's objective is to ensure your recovery efforts are completed in compliance with local and federal regulations.<br><br>" _
                & "The firm I work for, CohnReznick, is contracted by the Texas Division of Emergency Management (TDEM) to assist with implementation and delivery of the FEMA PA Program.  As your TDEM Grant Coordinator, I am responsible for assisting you in effectively navigating the recovery process and for providing any needed technical or financial support to address your day-to-day recovery needs.  I will be working with you extensively throughout the recovery process; however, I am a part of a larger team that is comprised of other specialized personnel who may also periodically contact you regarding certain aspects of your projects or to request documentation needed to support reimbursements.       Our team works under the supervision and direction of the TDEM Recovery Liaison and Section Administrator overseeing recovery efforts in your region and these State resources are available and equipped to provide additional support to you as needed.<br><br>" _
                & "In the coming days we will be providing additional guidance and reaching out to set up a meeting to discuss the recovery process in more detail and to answer any questions you might have. Please feel free to reach out to me at any time.<br><br>" _
                & "Respectfully,<br>" _
                & "Grant Coordinator Name"
                
                
    lCnt = 1
    ReDim aBody(1 To lCnt)
    aBody(lCnt) = "<HTML><body>" & asPreTable & "<table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"
    

    'Create each body row
    strQry = "SELECT * From contacts_table_primary"
    Set db = CurrentDb
    Set rec = CurrentDb.OpenRecordset(strQry)

    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            lCnt = lCnt + 1
            ReDim Preserve aBody(1 To lCnt)
            aRow(1) = rec("contact_agency")
            aRow(2) = rec("contact_role")
            aRow(3) = rec("contact_name")
            aRow(4) = rec("contact_phone")
            aRow(5) = rec("contact_email")
            aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End If

    aBody(lCnt) = aBody(lCnt) & "</table>" & asPostTable & "</body></html>"

    
    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)

    olItem.display
    olItem.To = "example@example.com"
    olItem.Subject = "Test E-mail"
    olItem.htmlbody = Join(aBody, vbNewLine)
        
    olItem.display
    
    appsrec.MoveNext
    Loop
    
    End If
    appsrec.Close
    Set appsrec = Nothing
    
    
End Sub

 

[pbaldy]

I wouldn't normally use an array, but to get just the contacts associated with the current applicant you'd have to use it in a criteria:

strQry = "SELECT * From contacts_table_primary WHERE FieldName = " & appsrec!FieldName

The "To" is hard-coded; to get one from a recordset:

appsrec!FieldName