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
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