Email a table (recordset)

[MCyn]

I'm trying to send out an automated e-mail through Access 2003, I'd like to
add in the body of the e-mail a "table view" of a table within the database.
Example, I have a table named tblTest with two fields Field1 and Field2; I'd
like to send the contents (data) of the table through a e-mail (as if I were
to open the table and copy and paste into a new e-mail, which creates a table
type of view of the table) Hope I'm explaining myself, if not let me know.
thanks for your help!

 

[vbasean]

I'll try to point you in the right direction:
you have two things going on here:
1) send email via Access 2003
2) format a table in HTML

as for sending email with Access 2003 a quick google search will point you
to many methods to do this with the IMAP functions

here's an example that I found doing such:
http://www.blueclaw-db.com/access_email_send_outlook.htm

as for formating a table in the body, we do something similar and here's the
suggestion

create a function that loops through your table
and builds an html table

something like this

function createHTMLtable() as string
dim rs as recordset
dim db as database
dim f
set db = currentdb
set rs = db.openrecordset("[your table name]")
'creates table header
createHTMLtable = "<tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.name & "</td>"
next
createHTMLtable = createHTMLtable & "</tr>"
'creates rows in table
do while not rs.eof
createHTMLtable = createHTMLtable & "<tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.value & "</td>"
Next f
createHTMLtable & "</tr>"
loop
End Function

in your email sub routine add this to the body part.

 

[vbasean]

This function might work a little better.

function createHTMLtable(recordsourcename as string) as string
dim rs as recordset
dim db as database
dim f
set db = currentdb
set rs = db.openrecordset(recordsourcename)
'creates table header
createHTMLtable = "<table><tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.name & "</td>"
next
createHTMLtable = createHTMLtable & "</tr>"
'creates rows in table
do while not rs.eof
createHTMLtable = createHTMLtable & "<tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.value & "</td>"
Next f
createHTMLtable = createHTMLtable & "</tr></table>"
loop
End Function

I'll try to point you in the right direction:
you have two things going on here:
1) send email via Access 2003
2) format a table in HTML

as for sending email with Access 2003 a quick google search will point you
to many methods to do this with the IMAP functions

here's an example that I found doing such:
http://www.blueclaw-db.com/access_email_send_outlook.htm

as for formating a table in the body, we do something similar and here's the
suggestion

create a function that loops through your table
and builds an html table

something like this

function createHTMLtable() as string
dim rs as recordset
dim db as database
dim f
set db = currentdb
set rs = db.openrecordset("[your table name]")
'creates table header
createHTMLtable = "<tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.name & "</td>"
next
createHTMLtable = createHTMLtable & "</tr>"
'creates rows in table
do while not rs.eof
createHTMLtable = createHTMLtable & "<tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.value & "</td>"
Next f
createHTMLtable & "</tr>"
loop
End Function

in your email sub routine add this to the body part.

I'm trying to send out an automated e-mail through Access 2003, I'd like to
add in the body of the e-mail a "table view" of a table within the database.
Example, I have a table named tblTest with two fields Field1 and Field2; I'd
like to send the contents (data) of the table through a e-mail (as if I were
to open the table and copy and paste into a new e-mail, which creates a table
type of view of the table) Hope I'm explaining myself, if not let me know.
thanks for your help!

 

[vbasean]

function createHTMLtable(recordsourcename as string) as string
dim rs as recordset
dim db as database
dim f
set db = currentdb
set rs = db.openrecordset(recordsourcename)
'creates table header
createHTMLtable = "<table><tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.name & "</td>"
next
createHTMLtable = createHTMLtable & "</tr>"
'creates rows in table
do while not rs.eof
createHTMLtable = createHTMLtable & "<tr>"
for each f in rs.fields
createHTMLtable = createHTMLtable & "<td>" & f.value & "</td>"
Next f
createHTMLtable = createHTMLtable & "</tr>"
loop
createHTMLtable = createHTMLtable & "</table>"
End Function


have to get my HTML right!!! lol

 

[MCyn]

vbasean, this is great...but when I incorporated the code into my e-mail, and
sent it; the e-mail looks just as the code. There is no table created, only
the code in the body of the message. (it does show the data, but not in table
form)

 

[vbasean]

instead of using the "body" property, use the "HTMLbody" property

 

[MCyn]

Ah!! yes...now that worked perfectly! Thanks!!

instead of using the "body" property, use the "HTMLbody" property