HTML Email look through two recordsets help

I

Indigo

Registered User.
Local time
Today, 20:20
Joined
Nov 12, 2008
Messages
241
Hello, I am using Office 365 and am trying to create an email from my Access database in HTML using two linked queries. The email generated looks like this:

1592242135902.png


But my preference is to look like this with each CM following after each issue:

1592242280271.png


Below is my code... can anyone point me in the right direction?

Code: 
 strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">No.&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">KPI&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Ranking&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">RPS Started&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Issue Details&nbsp;</p></Font></TH>" & _
              "</TR>"
                
                                ' add the data to the table
                    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
                    rs2.MoveFirst
                    Do While Not rs2.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneIssueNo].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![ZoneKPI].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneRankID].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![RPSStarted].Value & "</TD>" & _
                        "<TD align=left width=""50%"">" & rs2.Fields![ZoneIssue].Value & "</TD>" & _
                                 "</TR>"
                                
           strBody3 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">CM Details&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Responsible&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Target Date&nbsp;</p></Font></TH>" & _
              "</TR>"
              
                                ' add the data to the table
                    Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
                    rs3.MoveFirst
                    Do While Not rs3.EOF
                                 strBody3 = strBody3 & "<TR>" & _
                        "<TD align=left width=""40%"">" & rs3.Fields![CMDetails].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs3.Fields![Responsible].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs3.Fields![TargetDate].Value & "</TD>" & _                       
                                 "</TR>"
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            
                            rs2.MoveNext
                            Loop
                        rs2.Close

Thank you.
 
For your issue, I don't see any closing table tags in your code. You should close the table for each issue prior to starting a table for each CM. Then after all CM's for an issue have been added, close that table as well.

To that, you don't really need strBody2 and strBody3. Just use one strBody and add to it the HTML that goes next until you've built the whole email.
 
Thank you for your response. I had the closing tags in here:

Code: 
.HTMLBody = strBody2 & "</Table><br>" + strBody3 & "</Table><br>"

I have since moved them here:

Code: 
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody2 = strBody2 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody3 = strBody3 & "</table>"

strBody2 and strBody3 are coming from queries on two separate but related tables (PK), so I am not clear on how I can use one strBody to get the results I am looking for....?
 
The first line of your code in #1 is
Code: 
strBody2 = "<TABLE Border=""1"......
That's associated with the outer loop.

The code in #3 is associating strBody2 with the inner loop. Swap strBody2 with strBody3
 
I think Cronked solved it in the prior post.

strBody2 and strBody3 are strings unrelated to queries and primary keys. They are where you are building your HTML. You're going thru your your data in the order you want it to appear in your HTML, so only 1 variable is needed to build all the HTML.

Code: 
Enter Issue Loop
--strBody += html for 1 issue (opening tags, header row, 1 row of data, closing tags)
--strBody+=html to start related CM table (opening tags, header row)
--Start related CM Loop
----strBody+=html for 1 CM row of data
----Next CM item
--strBody += html to close related CM table (closing tags)
--Next Issue Item

If you had just 1 variable to store HTML it would have made misplaced closing <table> tags a little easier to identify.
 
Cronk, are you asking me to change to this?

Code: 
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody3 = strBody3 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody2 = strBody2 & "</table>"

When I do that, it makes no difference to the email. I still get the same results as I got in image #1 above.
 
I see it now. You need to go with just 1 variable like I mentioned instead of 2.

strBody2 contains nothing but Issue records, strBody3 contains only CM records. When you concatenate them you get all Issues then all CMs. To get an issue and it's CMs then another issue and it's CMs, then another issue, etc, you need to use just 1 strBody variable.
 
Yes, I am getting closer..... made it all strBody2
Code: 
                '*********** display issues & CMs
           strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">No.&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">KPI&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Ranking&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">RPS Started&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Issue Details&nbsp;</p></Font></TH>" & _
              "</TR>"
                
                                ' add the data to the table
                    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
                    rs2.MoveFirst
                    Do While Not rs2.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneIssueNo].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![ZoneKPI].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneRankID].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![RPSStarted].Value & "</TD>" & _
                        "<TD align=left width=""50%"">" & rs2.Fields![ZoneIssue].Value & "</TD>" & _
                                 "</TR>"
                                
           strBody2 = strBody2 & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">CM Details&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Responsible&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><pfont-size:14px"">Target Date&nbsp;</p></Font></TH>" & _
              "</TR>"
              
                                ' add the data to the table
                    Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
                    rs3.MoveFirst
                    Do While Not rs3.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=left width=""40%"">" & rs3.Fields![CMDetails].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs3.Fields![Responsible].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs3.Fields![TargetDate].Value & "</TD>" & _
                                 "</TR>"
                        rs3.MoveNext
                            Loop
                        rs3.Close
'                            strBody2 = strBody2 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
'                            strBody2 = strBody2 & "</table>"

But I am not clear where to put my "</Table>" tag(s) .... and do I move my
Code: 
rs2.MoveNext
Loop
rs2.Close

it's also generating all countermeasures for each Issue... so, I think I need to adjust my strSQL....
 
The HTML is fixed, now you need to fix your second (CM) recordset. You need to use the issue you are own as criteria in generating the second recordset. I don't know where strSQL3 is set, but it at least needs to be modified before this line:

Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
 
The "</table>" tags are not in the correct place. Don't I need to close the first table? If I leave them where they are the CMs table gets embedded in the issues table.
1592320958184.png

And If I close the first table by doing this:

Code: 
                '*********** display issues & CMs
           strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">No.&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">KPI&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Ranking&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">RPS Started&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Issue Details&nbsp;</p></Font></TH>" & _
              "</TR>"
                
                                ' add the data to the table
                    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
                    rs2.MoveFirst
                    Do While Not rs2.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneIssueNo].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![ZoneKPI].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs2.Fields![ZoneRankID].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs2.Fields![RPSStarted].Value & "</TD>" & _
                        "<TD align=left width=""50%"">" & rs2.Fields![ZoneIssue].Value & "</TD>" & _
                                 "</TR>"
                            strBody2 = strBody2 & "</table>" 'Move it here!
                                
           strBody2 = strBody2 & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">CM Details&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Responsible&nbsp;</p></Font></TH>" & _
           "<TH Bgcolor=""#2B3856"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:14px"">Target Date&nbsp;</p></Font></TH>" & _
                         "</TR>"
              
                                ' add the data to the table
                    Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
                    rs3.MoveFirst
                    Do While Not rs3.EOF
                                 strBody2 = strBody2 & "<TR>" & _
                        "<TD align=left width=""40%"">" & rs3.Fields![CMDetails].Value & "</TD>" & _
                        "<TD align=center width=""15%"">" & rs3.Fields![Responsible].Value & "</TD>" & _
                        "<TD align=center width=""10%"">" & rs3.Fields![TargetDate].Value & "</TD>" & _
                                                        "</TR>"
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody2 = strBody2 & "</table>"

Then I get this:

1592321261277.png


I fear I've made a muddled mess of this..... :-(
 
plog said:
The HTML is fixed, now you need to fix your second (CM) recordset. You need to use the issue you are own as criteria in generating the second recordset. I don't know where strSQL3 is set, but it at least needs to be modified before this line:

Set rs3 = CurrentDb.OpenRecordset(strSQL3, dbOpenDynaset)
Click to expand...

Thanks.... strSQL3 is set just before I build the tables....

Code: 
            strSQL3 = "SELECT * FROM qryZonePermCM " & _
                    " WHERE qryZonePermCM.ZoneMeetingID = " & HoldMeetingID & "AND ZoneIssueID = " & HoldZoneIssueID

however the ZoneIssueID will change as I loop through rs2 so I cannot point to the form to "Hold" it.... as I initially tried....
 
Your closing table tags were in the right place in post #8--you need one just before rs2.MoveNext

strSQL3 needs to take into account the current issue you are looking at. That is determined by the first recordset. So you need to compose strSQL3 with an element of the first recordset.
 
plog said:
Your closing table tags were in the right place in post #8--you need one just before rs2.MoveNext
Click to expand...
But I got the second table embedded in the first table when I did that....

strSQL3 needs to take into account the current issue you are looking at. That is determined by the first recordset. So you need to compose strSQL3 with an element of the first recordset.
Click to expand...
I do not know how to do this.... any suggestions on how I can accomplish it? I am starting to Google it....
 
But I got the second table embedded in the first table when I did that....
Click to expand...

That's because you used 2 different variables for your HTML.

I do not know how to do this.... any suggestions on how I can accomplish it?
Click to expand...

You need to reconfigure the WHERE clause of strSQL3 to use whatever issue you are on. I'm not familiar with your data but I would assume that you would swap out HoldZoneIssueID with whatever Issue you are currently on in the first loop.
 
plog said:
That's because you used 2 different variables for your HTML.
Click to expand...
Sorry, I thought it was where the "</table>" flag was... what do I do to overcome this?

You need to reconfigure the WHERE clause of strSQL3 to use whatever issue you are on. I'm not familiar with your data but I would assume that you would swap out HoldZoneIssueID with whatever Issue you are currently on in the first loop.
Click to expand...

So should I be filtering by rs3 to what I am on in the first loop? I thought maybe this, but it doesn't work:
Code: 
            strSQL3 = "SELECT * FROM qryZonePermCM " & _
                    " WHERE qryZonePermCM.ZoneMeetingID = " & HoldMeetingID & "AND ZoneIssueID = " & rs2!ZoneIssueID
 
Sorry, I thought it was where the "</table>" flag was... what do I do to overcome this?
Click to expand...

Something like the below code is what you should have for the closing of your loops:

Code: 
                       rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody = strBody & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody = strBody & "</table>"


So should I be filtering by rs3 to what I am on in the first loop?
Click to expand...

Yes. In prior code you are referencing the first recordset data like this:


rs2.Fields![ZoneIssueNo].Value
 
Thank you....so much.... I am almost there! I updated the code as you suggested and I am still getting the 2nd table embedded in the first.... Sorry but I am confused by your comment:

That's because you used 2 different variables for your HTML.
Click to expand...

1592333296877.png
 
Your probably not closing a table tag. Either you are starting the CM table prior to closing the issue tag, or you are generating too many opening table tags.

The best way to debug this is to dig into the HTML generated. Paste it into a text file then add tabs and spaces to make it readable.

Code: 
<table>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
</table>
<table>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
  <th><td></td><td></td> etc...</th>
</table>
A good text editor (Sublime, Notepad++) will help you find the closing tag for every open tag and even flag those without a partner.
 
So, I have neither of those text editors ... so I pasted into Word and did a Find for "table" I have 2 open table tags and 2 closed table tags....

Code: 
           strBody2 = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           .
           .
           .
           strBody2 = strBody2 & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
           .
           .
           .
                        rs3.MoveNext
                            Loop
                        rs3.Close
                            strBody2 = strBody2 & "</table>"
                            rs2.MoveNext
                            Loop
                        rs2.Close
                            strBody2 = strBody2 & "</table>"
 
Hopefully that gets it, but its not what I meant. You pasted the VBA code into Word, I said you should post the actual HTML code it generates into a text editor.
 
You must log in or register to reply here.

Similar threads

T
Hiding HTMLBody table rows if values are 0
Replies
3
Views
828
plog
P
L
Solved Help on blank Email body
2
Replies
27
Views
2,086
bastanu
bastanu
J
Save Email to Directory
2 3
Replies
58
Views
3,435
johankotze
J
M
Solved VBA access to outlook email with table: aligning table cells
Replies
5
Views
2,247
miacino
M
M
Import and Transpose From File Text File Into Table
Replies
5
Views
1,158
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom