Group records in HTML email body with Access vba

[Mikki]

I have a vba code that displays a table in an email body, but now I have to group accounts so it looks like:
CCRT# 1234-01 ACCT# 4567
Trans# | Trans Date | Item | Reference | Amount
# | x | x | x | $
CCRT# 1234-01 ACCT# 8970
Trans# | Trans Date | Item | Reference | Amount
# | x | x | x | $

I found a code with <DL> <DT> <DD> </DL> (<DT> = defined term, <DD> = definition), but it did not work.
Please help.
Thax~mjc

 

[plog]

Tables in html use the table, tr and td tags:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Mikki]

Thank you~plog. That is where I got the first code and the table is very good, but now I have to group accounts and I am no sure how to use <fieldset> to make it happen. Any advice?

 

[Minty]

You would need to produce the output in the correct format, I don't think the HTML coding in outlook is clever enough to do that for you.

 

[plog]

I don't understand what you mean by "group accounts".

You can always end a table and start a new one

Account 1 Data
[Table 1 Here]

Account 2 Data
[Table 2 Here]

Account 3 Data
[Table 3 Here]

When building emails I always grab some test data and open a text editor to get the HTML right. Half the screen is the text editor, the other half is the .html file opened in a browser--make a change in the text editor, refresh the browser. Getting it right that way makes it easier to code.

 

[Mikki]

Thanks, Minty~I am working in Access to produce an Outlook email,~mjc

Yes, plog. It displays the CCTR and ACCT and under that, it displays all records that fall under those columns/rows. It displays as a table header rows with columns and rows under it. Thx~mjc

 

[Minty]

I understand what you are trying to do.
I'm saying you can't use the HTML /CSS coding to do the grouping for you.
You will have to format the code to create the HTML groups by looping in Access vba or something similar, as @plog has suggested.

 

[Mikki]

Apologizes, Minty, so <fieldset> would not work to group?

 

[Minty]

I've never seen that used in creating an Outlook email body from VBA.

I've done quite a lot of HTML formatting (mainly from SQL to be fair) but I don't think you have an active dataset available for "dynamic" HTML to work within the outlook body object.
It's effectively just display formatting.

 

[theDBguy]

Apologizes, Minty, so <fieldset> would not work to group?

Hi. Pardon me for jumping in, I am no HTML expert; but according to this website, fieldset basically just puts a box around a group of elements.

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Mikki]

This is what I have:
strTable = "<TABLE Border=""1"", Cellspacing=""1"", Cellpadding=""2"", Width=""100%"">" & _

"<TR><TD colspan=""10"", Width=""100%"", Align=""Center"" Bgcolor=""#D8D8D8"", Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:16px"">---- Ledger Detail Report ----&nbsp;</p></Font></TD></TR>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Trans Date&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Post Date&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Jnl&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Trans Ref 1&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Trans Ref 2&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Trans Ref 3&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Trans Ref 5&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Vendor Reference&nbsp;</p></Font></TD>" & _

"<TD Bgcolor=""#D8D8D8"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:11px"">Amount&nbsp;</p></Font></TD>" & _

"</TR>"



strSQLGL = "SELECT * FROM qryGLEmail WHERE [CPUTREF] = 'CCRT-2111-01'"



Set rs = CurrentDb.OpenRecordset(strSQLGL, dbOpenDynaset)



rs.MoveFirst

Do While Not rs.EOF



strTable = strTable & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Trans Number].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Trans Date].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Post Date].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Journal].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Ref 1].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Ref 2].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Ref 3].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Ref 5].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Left""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & rs.Fields![Ledger Ref].Value & "</TD>" & _

"<TD Bgcolor=""#FFFFFF"", Align=""Center""><Font Color=#000000><b><Font Face=""Courier New""><p style=""font-size:12px"">" & Format(rs.Fields![Ledger Amount].Value, "Currency") & "</TD>" & _

"</tbody></TR>"

Can <fieldset> be worked in with the recordset?

 

[theDBguy]

Can <fieldset> be worked in with the recordset?

Hi. Did you see my post earlier? Just curious...

 

[Mikki]

Thanks, theDBguy, I will look at the link you sent!!!

 

[Mikki]

Thanks, theDBguy, I saw it after I posted that very long code

 

[Mikki]

There was a site with this table

<TH id="a6" axis="location">San Jose</TH> to categorize cells. Can this be worked into my recordset table?

 

[theDBguy]

There was a site with this table View attachment 93391
<TH id="a6" axis="location">San Jose</TH> to categorize cells. Can this be worked into my recordset table?

Hi. I'm not sure we are using the same terminology. If that image was the result of your code in Outlook, then it already came from a "recordset," I am not sure why you would need to convert it again into a "recordset table." Besides, I am not sure there is such a thing as a "recordset table" - either in HTML or in Access.

If you want that data presented with groupings, then you'll have to change your code to process each group at a time as you create the HTML table.

Things might go faster if you could post a sample db with test data.