Hi Everyone,
I am trying to create an html formatted table in Outlook that displays the results of a query. I can get the 2 tables to display on the email but they merge together into one. I also would like to write text above the second table. Below is my code. Any suggestions?
Function ReportPlantToOutlookBody()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim strBodyText As String
Dim olApp As Outlook.Application
Dim ObjMail As Outlook.MailItem
Dim signature As String
Dim strSQL As String
Dim mailbody as String
Dim strList As String
Dim varItem As Variant
Dim strOrderBy As String
strOrderBy = Forms!truck_schedule!lst_truckschedule.Column(8, varItem)
Call SelectAlltruck(Forms!truck_schedule!lst_truckschedule)
'Define format for output
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Customer </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Purchase Order </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">City </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Product </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Order Number </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Planned Ship Date;</p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Plant </p></Font></TD>" & _
"</TR>"
With Forms!truck_schedule!lst_truckschedule
For Each varItem In Forms!truck_schedule!lst_truckschedule.ItemsSelected
strList = strList & Forms!truck_schedule!lst_truckschedule.Column(0, varItem) & ","
Next
If strList <> "" Then
strList = Left(strList, Len(strList) - 1)
strList = "(" & strList & ") "
strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00CX' OR [Plant] = '00AB' ORDER BY tblTrucks.Plant ASC"
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
mailbody = mailbody & _
"<tr>" & _
TD(rst![Customer] & "") & _
TD(rst![PO] & "") & _
TD(rst![City] & "") & _
TD(rst![MatDescription] & "") & _
TD(rst![SalesDoc] & "") & _
TD(rst![PIGIdate] & "") & _
TD(rst![Plant] & "") & _
"<tr>"
strBodyText = "<HTML><BODY>Good Morning!<BR><BR>Please advise on the below items as soon as you can.<BR><BR><B>Beaumont - 00CX & 00AB</B><BR><BR>Do we have carrier(s) confirmed for the below load(s):<BR><BR></BODY></HTML>"
rst.MoveNext
Loop
rst.Close
'(send e-mail)
Else
MsgBox ("Please select an order from the list.")
End If
End With
'********************* created header of table 2
'Define format for output
mailbody = mailbody & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Customer </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Purchase Order </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">City </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Product </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Order Number </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Planned Ship Date </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Plant </p></Font></TD>" & _
"</TR>"
With Forms!truck_schedule!lst_truckschedule
For Each varItem In Forms!truck_schedule!lst_truckschedule.ItemsSelected
strList = strList & Forms!truck_schedule!lst_truckschedule.Column(0, varItem) & ","
Next
If strList <> "" Then
strList = Left(strList, Len(strList) - 1)
strList = "(" & strList & ") "
strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00FG' "
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
mailbody = mailbody & _
"<tr>" & _
TD(rst![Customer] & "") & _
TD(rst![PO] & "") & _
TD(rst![City] & "") & _
TD(rst![MatDescription] & "") & _
TD(rst![SalesDoc] & "") & _
TD(rst![PIGIdate] & "") & _
TD(rst![Plant] & "") & _
"<tr>"
rst.MoveNext
Loop
rst.Close
'(send e-mail)
Else
MsgBox ("Please select an order from the list.")
End If
End With
Set olApp = CreateObject("Outlook.Application")
olApp.Session.Logon
Set ObjMail = olApp.CreateItem(0)
On Error Resume Next
With ObjMail ' This creates a blank email and captures the users default signature.
.BodyFormat = olFormatHTML
.Display
End With
signature = ObjMail.HTMLBody
With ObjMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Follow up Items - " & Date
.HTMLBody = strBodyText & mailbody & signature
.Display 'or use .Send
End With
'outlook tidy up
Set ObjMail = Nothing
Set olApp = Nothing
Set rst = Nothing
End Function
I am trying to create an html formatted table in Outlook that displays the results of a query. I can get the 2 tables to display on the email but they merge together into one. I also would like to write text above the second table. Below is my code. Any suggestions?
Function ReportPlantToOutlookBody()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Dim strBodyText As String
Dim olApp As Outlook.Application
Dim ObjMail As Outlook.MailItem
Dim signature As String
Dim strSQL As String
Dim mailbody as String
Dim strList As String
Dim varItem As Variant
Dim strOrderBy As String
strOrderBy = Forms!truck_schedule!lst_truckschedule.Column(8, varItem)
Call SelectAlltruck(Forms!truck_schedule!lst_truckschedule)
'Define format for output
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Customer </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Purchase Order </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">City </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Product </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Order Number </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Planned Ship Date;</p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Plant </p></Font></TD>" & _
"</TR>"
With Forms!truck_schedule!lst_truckschedule
For Each varItem In Forms!truck_schedule!lst_truckschedule.ItemsSelected
strList = strList & Forms!truck_schedule!lst_truckschedule.Column(0, varItem) & ","
Next
If strList <> "" Then
strList = Left(strList, Len(strList) - 1)
strList = "(" & strList & ") "
strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00CX' OR [Plant] = '00AB' ORDER BY tblTrucks.Plant ASC"
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
mailbody = mailbody & _
"<tr>" & _
TD(rst![Customer] & "") & _
TD(rst![PO] & "") & _
TD(rst![City] & "") & _
TD(rst![MatDescription] & "") & _
TD(rst![SalesDoc] & "") & _
TD(rst![PIGIdate] & "") & _
TD(rst![Plant] & "") & _
"<tr>"
strBodyText = "<HTML><BODY>Good Morning!<BR><BR>Please advise on the below items as soon as you can.<BR><BR><B>Beaumont - 00CX & 00AB</B><BR><BR>Do we have carrier(s) confirmed for the below load(s):<BR><BR></BODY></HTML>"
rst.MoveNext
Loop
rst.Close
'(send e-mail)
Else
MsgBox ("Please select an order from the list.")
End If
End With
'********************* created header of table 2
'Define format for output
mailbody = mailbody & "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Customer </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Purchase Order </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">City </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Product </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Order Number </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Planned Ship Date </p></Font></TD>" & _
"<TD Bgcolor=""lightblue"", Align=""Center""><Font Color=black><b><p style=""font-size:16px"">Plant </p></Font></TD>" & _
"</TR>"
With Forms!truck_schedule!lst_truckschedule
For Each varItem In Forms!truck_schedule!lst_truckschedule.ItemsSelected
strList = strList & Forms!truck_schedule!lst_truckschedule.Column(0, varItem) & ","
Next
If strList <> "" Then
strList = Left(strList, Len(strList) - 1)
strList = "(" & strList & ") "
strSQL = "SELECT * FROM tblTrucks WHERE [Plant] = '00FG' "
Set rst = CurrentDb.OpenRecordset(strSQL)
Do Until rst.EOF
mailbody = mailbody & _
"<tr>" & _
TD(rst![Customer] & "") & _
TD(rst![PO] & "") & _
TD(rst![City] & "") & _
TD(rst![MatDescription] & "") & _
TD(rst![SalesDoc] & "") & _
TD(rst![PIGIdate] & "") & _
TD(rst![Plant] & "") & _
"<tr>"
rst.MoveNext
Loop
rst.Close
'(send e-mail)
Else
MsgBox ("Please select an order from the list.")
End If
End With
Set olApp = CreateObject("Outlook.Application")
olApp.Session.Logon
Set ObjMail = olApp.CreateItem(0)
On Error Resume Next
With ObjMail ' This creates a blank email and captures the users default signature.
.BodyFormat = olFormatHTML
.Display
End With
signature = ObjMail.HTMLBody
With ObjMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Follow up Items - " & Date
.HTMLBody = strBodyText & mailbody & signature
.Display 'or use .Send
End With
'outlook tidy up
Set ObjMail = Nothing
Set olApp = Nothing
Set rst = Nothing
End Function
