Exporting Access Data into Email Body

S

Suggy1982

I have the following VBA in outlook, which opens a query in an access
database and then exports the data into a table that is in the body of an
email, this is done as HTML.

However the problem I have is that the code I have only exports the first
record from the recordset and then inserts this into the first row of that
table. I know I need to use the movenext method to move through the recordset
and then insert the record into the next line of the table in the email body.
But I cannot figure out how to do this

Can anyone help?

Code:

Sub mail_Report()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection

With cn
..Provider = "Microsoft.Jet.OLEDB.4.0"
..ConnectionString = "\\uknts804\RetailData\Process and Procedures
Team\RCMs\Weekly Reports\RCM_Reporting.mdb"
..CursorLocation = adUseClient
..Mode = adModeShareDenyNone
..Open
End With

Set rs = New ADODB.Recordset
With rs
..CursorLocation = adUseClient
..CursorType = adOpenStatic
..LockType = adLockReadOnly
Set .ActiveConnection = cn

..Open ("SELECT xtab_report_received_log_Crosstab.*,
qry_Percent_Received_On_Time.[% Rec On Time] FROM
qry_Percent_Received_On_Time INNER JOIN xtab_report_received_log_Crosstab ON
qry_Percent_Received_On_Time.RCM = xtab_report_received_log_Crosstab.RCM
ORDER BY qry_Percent_Received_On_Time.[% Rec On Time] DESC;")

End With

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

Dim html As String

html = "<html><body><table>"

html = html & "<tr><td>" & rs.Fields(0).Value & "</td><td>" &
rs.Fields(1).Value & "</td><td>" & rs.Fields(2).Value & "</td><td>" &
rs.Fields(3).Value & "</td><td>" & rs.Fields(4).Value & "</td><td>" &
rs.Fields(5).Value & "</td><td>" & rs.Fields(6).Value & "</td><td>" &
rs.Fields(7).Value & "</td><td>" & rs.Fields(8).Value & "</td><td>" &
rs.Fields(9).Value & "</td></tr>"

html = html & "</table></body></html>"

With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.HTMLBody = html
..Display
End With

rs.Close
cn.Close

End Sub
 
J

Jim Burke in Novi

I think this is all you need to do (i just added a couple of lines of code
where needed, everything else would remain as you have it):

html = "<html><body><table>"
while not rs.eof
html = html & "<tr><td>" & rs.Fields(0).Value & "</td><td>" & _
rs.Fields(1).Value & "</td><td>" & rs.Fields(2).Value & "</td><td>" & _
rs.Fields(3).Value & "</td><td>" & rs.Fields(4).Value & "</td><td>" & _
rs.Fields(5).Value & "</td><td>" & rs.Fields(6).Value & "</td><td>" & _
rs.Fields(7).Value & "</td><td>" & rs.Fields(8).Value & "</td><td>" & _
rs.Fields(9).Value & "</td></tr>"
rs.movenext
wend
html = html & "</table></body></html>"

Also, after you're done with an object and you close it, you should set it
to nothing, e.g.
rs.close
set rs = nothing


Suggy1982 said:
I have the following VBA in outlook, which opens a query in an access
database and then exports the data into a table that is in the body of an
email, this is done as HTML.

However the problem I have is that the code I have only exports the first
record from the recordset and then inserts this into the first row of that
table. I know I need to use the movenext method to move through the recordset
and then insert the record into the next line of the table in the email body.
But I cannot figure out how to do this

Can anyone help?

Code:

Sub mail_Report()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "\\uknts804\RetailData\Process and Procedures
Team\RCMs\Weekly Reports\RCM_Reporting.mdb"
.CursorLocation = adUseClient
.Mode = adModeShareDenyNone
.Open
End With

Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
Set .ActiveConnection = cn

.Open ("SELECT xtab_report_received_log_Crosstab.*,
qry_Percent_Received_On_Time.[% Rec On Time] FROM
qry_Percent_Received_On_Time INNER JOIN xtab_report_received_log_Crosstab ON
qry_Percent_Received_On_Time.RCM = xtab_report_received_log_Crosstab.RCM
ORDER BY qry_Percent_Received_On_Time.[% Rec On Time] DESC;")

End With

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Set olApp = Outlook.Application
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

Dim html As String

html = "<html><body><table>"

html = html & "<tr><td>" & rs.Fields(0).Value & "</td><td>" &
rs.Fields(1).Value & "</td><td>" & rs.Fields(2).Value & "</td><td>" &
rs.Fields(3).Value & "</td><td>" & rs.Fields(4).Value & "</td><td>" &
rs.Fields(5).Value & "</td><td>" & rs.Fields(6).Value & "</td><td>" &
rs.Fields(7).Value & "</td><td>" & rs.Fields(8).Value & "</td><td>" &
rs.Fields(9).Value & "</td></tr>"

html = html & "</table></body></html>"

With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.HTMLBody = html
..Display
End With

rs.Close
cn.Close

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error 462 help 0
ADO Access For Value List Error 4
Export specific fields from access to Excel 0
Boiler Plateing text into another text document. 9
Force results to print on separate pages 1
Mail Merge Data Source Issue 3
How can I redirect to a URL? 0
Can one include data from javascript in form 1

Top