david_johnson
Registered User.
- Local time
- Yesterday, 22:32
- Joined
- Oct 12, 2017
- Messages
- 12
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.
Code:
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