Background Color to header row in HTML

I am automating a report in Access using HTML and want a table included.  I've gotten that far, but what I also want to do is put background color to the header row.  Here is what I have:

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strHTML As String
Dim rst As DAO.Recordset
Dim cnt As Long
 
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM END_Late_EmailFR_q")
    'WHERE VenName = '" & Forms!frmTemplate!Owner & "'")
   
 If Not rst.EOF Then
        strHTML = strHTML & "<table border=""1"" align=""Left"">" & vbCrLf
        For cnt = 0 To rst.Fields.Count - 2
        strHTML = strHTML & " <b> <th> " & rst(cnt).Name & "</th> " & vbCrLf

        Next cnt
    Do While Not rst.EOF
        strHTML = strHTML & "  <tr bgColor = '#FF000'>" & vbCrLf
        For cnt = 0 To rst.Fields.Count - 2
        strHTML = strHTML & "    <td>" & rst(cnt) & "</td>" & vbCrLf
        Next cnt
        strHTML = strHTML & "  </tr>" & vbCrLf
        rst.MoveNext
    Loop
        strHTML = strHTML & "</table>" & vbCrLf
    End If

I've also tried this: style='color:blue:' in place of bgColor = '#FF000'

Thank you for your help!
Sheli Van LaninghamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
Close to the right idea, but the wrong spot.
This creates your header row
strHTML = strHTML & "<table border=""1"" align=""Left"">" & vbCrLf
For cnt = 0 To rst.Fields.Count - 2
        strHTML = strHTML & " <b> <th> " & rst(cnt).Name & "</th> " & vbCrLf
Next cnt


See the <th>
That's your table column header tag, and certainly not the <tr bgColor = '#FF000'>
You don't actually color the row, you are actually coloring each of the <th> the same color.  Since all the <th> are in the first row, it semantically is the same as coloring the row, but not syntactically!
http://www.w3schools.com/tags/att_th_bgcolor.asp

So try

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strHTML As String
Dim rst As DAO.Recordset
Dim cnt As Long
 
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM END_Late_EmailFR_q")
    'WHERE VenName = '" & Forms!frmTemplate!Owner & "'")
   
 If Not rst.EOF Then
        strHTML = strHTML & "<table border=""1"" align=""Left"">" & vbCrLf
        For cnt = 0 To rst.Fields.Count - 2
        strHTML = strHTML & " <b> <th bgColor = '#FF000'> " & rst(cnt).Name & "</th> " & vbCrLf

        Next cnt
    Do While Not rst.EOF
        strHTML = strHTML & "  <tr>" & vbCrLf
        For cnt = 0 To rst.Fields.Count - 2
        strHTML = strHTML & "    <td>" & rst(cnt) & "</td>" & vbCrLf
        Next cnt
        strHTML = strHTML & "  </tr>" & vbCrLf
        rst.MoveNext
    Loop
        strHTML = strHTML & "</table>" & vbCrLf
    End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sheli Van LaninghamAuthor Commented:
Thank you!  This is my first go at HTML, so thank you for the explanation and the solution!!
0
Nick67Commented:
I am by no means an Expert on the web end of things.
I do mean things with Access VBA -- including having it write HTML both for webpages and, like you are doing now, the creation of .HTMLBody strings for Outlook mail messages.
The w3schools link is a good, concise place to learn syntax.

Depending upon what you are doing, these days purists look down their noses at us folks who use tables
That's so 20th century, you know :)
On the other hand, for displaying tabular data in an email message, there's nothing simpler or better, as email clients like Outlook don't necessarily support all the fanciness.

What can be less annoying if you have some facility with Excel is to take your recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM END_Late_EmailFR_q")
     'WHERE VenName = '" & Forms!frmTemplate!Owner & "'")

and ensure that all the field names in the query are nice and presentable.
And then fire up an Excel automation and throw it in with .CopyFromRecordset

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oBook.Worksheets("sheet2").Delete
oBook.Worksheets("sheet3").Delete
oSheet.Name = "All"

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
'iNumCols = 9
iNumCols = rs.Fields.count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rst.Fields(i - 1).Name
Next

'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rst

Open in new window


You could blow in some formatting like
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

Open in new window


And some borders and formats for dates and numbers
With oSheet.Range("A1:N" & rs.RecordCount + 1)
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlThin
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlThin
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlThin
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideVertical).ColorIndex = xlAutomatic
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).Weight = xlThin
    .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
  
End With
oSheet.Range("A1:N" & rs.RecordCount + 1).Copy
oSheet.Range("A1:A" & rs.RecordCount + 1).NumberFormat = "[$-1009]d-mmm-yy;@"

Open in new window


Then the nice part!
Save that Excel workbook as html
'oSheet.SaveAs myfilename, xlHtml
Dim myfilename as string
    myfilename = c:\temp\Summary.xls"
    If Application.Version > 11 Then
        oBook.CheckCompatibility = False
        oSheet.SaveAs myfilename, 56 ' that's .xls format!
    Else
        oSheet.SaveAs myfilename ' straight up for Access 2003
    End If
End If

Open in new window


Now, you have a nice block of ready-to-go html without the pain of coding it out in html!
Now you need to get into your Outlook message!
I'll leave out the Outlook bits as you are that far already.
First, you find the .htm file and open it as a TextStream

            Dim ts As Object
            Set fs = CreateObject("Scripting.FileSystemObject")
            If fs.FileExists("c:\tempPDF\Summary.htm") = False Then
                GoTo skipHTML
            End If
            Set ts = fs.GetFile("c:\tempPDF\Summary.htm").OpenAsTextStream(1, -2)
            .HTMLBody = "<p>This is an automated sending by Me as requested. </p>Please respond to me@neat.com with any inquiries.</P><br><br>"
            .HTMLBody = .HTMLBody & ts.ReadAll

Open in new window


And that's it!
No messing with HTML syntax at all.
Let Excel handle that!
If you are interested, that is!

For fun, I then attach the Excel sheet to the message and then the client can read the nice table, and save the sheet for their own use, too.

Nick67
0
Sheli Van LaninghamAuthor Commented:
That is awesome, I am much more familiar with using Excel automation.  Thanks for the excellent suggestion!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.