Generating an HTML email with embedded table based on query results

[jvol]

Hello,

Currently, I am using a macro linked to a button that generates a text based email with the result of the query attached as an excel file.

Instead, I would like to be able to generate an HTML based email which displayes data from one of my queries in a table in the body of the email. Does anyone have ideas on how to accomplish this? Thanks for your help in advance.

J

[jonh]

Which part do you need help with?

I don't know much about email. The format for the message body should be a property setting afaik.

Creating html tables is pretty straigt forward.

This function takes your sql, whether or not you want to display headers and a title (control tip).


Public Function GenHTMTable(sql As String, hdr As Boolean, ttl As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tbl As String, style As String

Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
If rs.EOF Then Exit Function

tbl = "<table title='" & ttl & "' border='1' cellspacing='0' cellpadding='4px' " & _
"style='font-family:arial;font-size:smaller;'>" & vbCrLf

If hdr Then
tbl = tbl & "<tr style='background-color:lightgray;'>"
For Each f In rs.Fields
tbl = tbl & "<th>" & f.Name & "</th>"
Next
tbl = tbl & "</tr>" & vbCrLf
End If

style = "background-color:white;"
Do Until rs.EOF
tbl = tbl & "<tr style=" & style & ">"
For Each f In rs.Fields
tbl = tbl & "<td>" & Nz(f.Value, "&nbsp;") & "</td>"
Next
tbl = tbl & "</tr>" & vbCrLf
rs.MoveNext
If style = "background-color:lightblue;" Then
style = "background-color:white;"
Else
style = "background-color:lightblue;"
End If
Loop
GenHTMTable = tbl & "</table>" & vbCrLf

rs.Close: Set rs = Nothing: Set db = Nothing
End Function

'useage example :
Debug.Print GenHTMTable("table1", True, "Latest Sales Figures")
Debug.Print GenHTMTable("table2", True, "Quarter 2 results")
Debug.Print GenHTMTable("query2", True, "Diary")

[jvol]

Hi John,

Thanks for the note. The issue I'm having is that I when I paste any cody into the body of the email it comes up as plain text. I need a way to make the email html based. Any ideas?

Thanks,
J

[jonh]

What client are you using? Show us the code that isn't working.