On to my next programming issue ... also involving sending e-mails with tables.
Overall, what I want to do is to open query that already exists and pull the name, email and troop number from the first record. Then, using the troop number, run an sql statement that pulls all the records for the leader's troop and builds a table with the troop roster.
So, two recordsets, the recordset for the troop leaders and the recordset for the roster.
When I step though and watch the locals window, I see that the leader recordset opens (it has a little + to the left of it), but the roster recordset does not (no little +).
When I set up the DIM statements, I see that there is a Recordset variable type and a Recordset2 variable type.
What is the difference?
In the locals window, the leader recordset has "Recordset/Recordset2" as the type while the roster recordset says "Recordset" for the type. Does it matter? I tried changing the roster recordset to "Recordset2," but the code still didn't run correctly.
I have two debug.print statements. The leader count statement would be the count of leaders. When it appears in the immediate window, it is correct. The troop members count should be the number of members in the leader's troop. In this case, the number should be 6, but it comes up 0.
Also, I have used EOF and BOF successfully in other code, but for some reason unknown to me, it doesn't seem to work with this code. That's why I'm counting the records and using that to get out of the loop (made THAT mistake only once!).
Code:
Sub RosterEmails()
Dim objOutlook As Object
Dim objEmailItem As Outlook.MailItem
Dim dbs As DAO.Database
Dim tlTroop As Integer
Dim FirstName As String
Dim EMail As String
Dim rstTL As DAO.Recordset
Dim rstRoster As DAO.Recordset
Dim strQry As String
Dim strTable As String
Dim tblHeader As String
Dim OutlookAcct As Variant
Dim OutlookAccounts As Variant
Dim OutlookAcctTemp As Variant
Dim strFrom As String
Dim rstCountTL As Integer
Dim rstStopTL As Integer
Dim qryTL As String
Dim rstCountRos As Integer
Dim rstStopRos As Integer
Dim qryRoster As String
Dim FullName As String
'prevent 429 error if Outlook is not open
On Error Resume Next
Err.Clear
'See if Outlook is open
Set objOutlook = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
'Should open Outlook
Set objOutlook = CreateObject("Outlook.Application")
End If
Set OutlookAcctTemp = objOutlook.Account
strFrom = "an_email@gmail.com"
Set OutlookAccounts = objOutlook.Application.Session.accounts
For Each OutlookAcctTemp In OutlookAccounts
If (OutlookAcctTemp.SmtpAddress = strFrom) Then
Set OutlookAcct = OutlookAcctTemp
Exit For
End If
Next OutlookAcctTemp
Set dbs = CurrentDb
qryTL = "TroopRosterOnlyTL"
Set rstTL = dbs.OpenRecordset(qryTL)
rstTL.MoveLast
rstStopTL = rstTL.RecordCount
Debug.Print "Number of Troop Leader records: " & rstStopTL
rstCountTL = 0
rstTL.MoveFirst
Do Until rstCountTL = rstStopTL 'rstQuery.EOF 'Do While Not rstQuery.EOF
rstCount = rstCount + 1
FirstName = rstTL!First
tlTroop = rstTL!Troop
'Pull Troop Roster based on Troop leader's troop number
qryRoster = "SELECT TroopRoster.Troop, TroopRoster.TrpPos, TroopRoster.First, TroopRoster.Last, TroopRoster.Email, TroopRoster.Type, TroopRoster.PosSortOrder, TroopRoster.School, TroopRoster.Grade, [first] & "" "" & [last] AS FullName From TroopRoster WHERE (((TroopRoster.Troop)=tlTroop) AND ((TroopRoster.TrpPos) Not Like ""*_*"")) ORDER BY TroopRoster.Troop, TroopRoster.Type DESC , TroopRoster.PosSortOrder, TroopRoster.Last, TroopRoster.First;"
Set rstRoster = dbs.OpenRecordset(qryRoster, dbOpenDynaset, dbReadOnly)
'Create table header
tblHeader = "<HTML><br><br>Troop" & tlTroop & " Roster: (please note: information is accurate as of 10/19/2021;" & _
" changes may take up to 24 hours to appear)<br><BR><BR><br>" & _
"<table border='1' style='font-family:calibri;font-size:12pt;border-collapse:collapse;padding:1px 10px 1px 10px'>" & _
"<tr><th align='left'>Position</th><th align='left'>Name</th><th align='left'>Email</th>" & _
"<th align='left'>School</th><th align='left'>Grade</th>"
'Create Table Rows
rstRoster.MoveLast
rstStopRos = rstRoster.RecordCount
Debug.Print "Number of troop members: " & rstStopRos
rstCountRos = 0
rstRoster.MoveFirst
Do Until rstCountRos = rstStopRos 'rstQuery.EOF 'Do While Not rstQuery.EOF
rstCountRos = rstCountRos + 1
strTable = strTable & "<tr><td>" & rstRoster!TrpPos & "</td><td>" & rstRoster!FullName & _
"</td><td>" & rstRoster!EMail & "</td><td>" & rstRoster!School & _
"</td><td>" & rstRoster!Grade
Debug.Print rstRoster!FullName
rstRoster.MoveNext
Loop
'Build E-mail
Set objEmailItem = objOutlook.CreateItem(0)
With objEmailItem
.SendUsingAccount = OutlookAcct
'Who the e-mail is to
.To = rstTL!EMail
'What the subject line is
.Subject = "GS Troop Roster information, please respond!"
'using HTML so the font and size can be changed. <br> is a carriage return
.HTMLBody = "<BODY style=font-size:12pt;font-family:Calibri>" & rstQuery!FirstName & ",<br><br>" & _
"Hello Again! I need you to verify several things. And again, please respond, even" & _
"if it's to say that everything is correct.<br><br>Below is the roster for your troop. " & _
"<br><br>Here are specific things to check on:<BR><BR>" & _
"Adults - is the position correct? When I get the information from the council, if an adult " & _
"is listed as both a troop leader and a troop helper, I generally code them as a troop leader. " & _
"I only keep track of leaders, helpers, cookie managers, and fall product sales managers. " & _
"If an adult is listed as, say, a camping volunteer at the council level and that's all, I code " & _
"them as a troop helper." & _
"<BR><BR>Adults - are all the adults listed? Are there adults who should not be listed?" & _
"<BR><BR>Girls - are all the girls listed? Are there girls who should not be listed?" & _
"<BR><BR>E-mail addresses - is the e-mail address for each person correct? If it's blank, " & _
"there is not a valid e-mail address. Some high school girls have both a parent's e-mail and " & _
"their own e-mail on file, however, for this listing, I only included the parent e-mail address." & _
"<BR>BR>School - is the school listed for each girl correct?<BR>BR><BR>BR>" & _
tblHeader & strTable & "</table><br>" & _
"<br><br><br>Thank you very much for checking this over and for taking time to respond, " & _
"even if it's just to say everything is correct!" & _
"<br><br><br>Susie Gilson<br>Registrar<br>SU 678<br>Girl Scouts<br></BODY>"
Display the e-mail rather than sending it
.DISPLAY
End With
'have the e-mail item be visible
objOutlook.visble = True
'Reset variables
Set objEmailItem = Nothing
rstRoster.Close
Set rstRoster = Nothing
strTable = ""
'Move to next item in the list
rstTL.MoveNext
Loop
rstTL.Close
Set rstTL = Nothing
Set dbs = Nothing
End Sub
Any insight would be helpful!
Susie
Kansas