Creating multiple html tables in Outlook email

N

nosaj03

Registered User.
Local time
Today, 11:13
Joined
May 29, 2016
Messages
21
Good Evening everyone!

Im trying to create an html formatted table in Outlook that displays the results of a designated query.

Ive been successful in creating the table for one of my queries but when attempting to create an additional table based on another query, Im only getting first to be created in the email.

The secondary code is identical to the first but I cant seem to generate a second table. Any suggestions?
 
It might help to see the code.
 
pbaldy said:
It might help to see the code.
Click to expand...

See Below. The only table that populates is table 2 at the bottom. The code for table 1 seems to be completely ignored.

Public Function Report()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String
Dim rs As DAO.Recordset

'********************* created header of table 1
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column1 </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column2 </p></Font></TD>" & _
"</TR>"

' add the data to the table
Set rs = CurrentDb.OpenRecordset("queryA", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"

rs.MoveNext
Loop
rs.Close



'********************* created header of table 2
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column1 </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column2 </p></Font></TD>" & _
"</TR>"

' add the data to the table
Set rs = CurrentDb.OpenRecordset("queryB", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"

rs.MoveNext
Loop
rs.Close

Set rs = CurrentDb.OpenRecordset("queryC", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"
rs.MoveNext
Loop
rs.Close


' <br> used to insert a line ( press enter) and send email
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "John.Doe@email.com"
.CC = "Jane.Doe@email.com"
.Subject = "Report for : " & Format(Date - 1, "mmm dd, yyyy") & ""
.HTMLBody = "Report for: " & Format(Date - 1, "MMM DD, YYYY <br> <br>") & mailbody & "</table>"
.Display
'.Send
End With
End Function
 
That is because it looks like you have copied the code for table 1 for table 2 and modified to suit, but mailbody is set to the html for table 2?

You need to concatenate mailbody each time, as you do for the data?

Code: 
mailbody = mailbody & <rest of the html>
 
Gasman said:
That is because it looks like you have copied the code for table 1 for table 2 and modified to suit, but mailbody is set to the html for table 2?

You need to concatenate mailbody each time, as you do for the data?

Code: 
mailbody = mailbody & <rest of the html>
Click to expand...

Thanks for the info. So where in my code will I place your suggestion?
 
nosaj03 said:
Thanks for the info. So where in my code will I place your suggestion?
Click to expand...

See below.

May I ask why you used a Function and not Sub?, was it because of the string limit?. I only use Function if I need to return something.?

Code: 
Public Function Report()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String
Dim rs As DAO.Recordset

'********************* created header of table 1
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column1 </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column2 </p></Font></TD>" & _
"</TR>"

' add the data to the table
Set rs = CurrentDb.OpenRecordset("queryA", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"

rs.MoveNext
Loop
rs.Close



'********************* created header of table 2
mailbody = [COLOR="Red"]mailbody &  [/COLOR]"<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column1 </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column2 </p></Font></TD>" & _
"</TR>"

' add the data to the table
Set rs = CurrentDb.OpenRecordset("queryB", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"

rs.MoveNext
Loop
rs.Close

Set rs = CurrentDb.OpenRecordset("queryC", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"
rs.MoveNext
Loop
rs.Close


' <br> used to insert a line ( press enter) and send email
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "John.Doe@email.com"
.CC = "Jane.Doe@email.com"
.Subject = "Report for : " & Format(Date - 1, "mmm dd, yyyy") & ""
.HTMLBody = "Report for: " & Format(Date - 1, "MMM DD, YYYY <br> <br>") & mailbody & "</table>"
.Display
'.Send
End With
End Function
 
Gasman said:
See below.

May I ask why you used a Function and not Sub?, was it because of the string limit?. I only use Function if I need to return something.?

Code: 
Public Function Report()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String
Dim rs As DAO.Recordset

'********************* created header of table 1
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column1 </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column2 </p></Font></TD>" & _
"</TR>"

' add the data to the table
Set rs = CurrentDb.OpenRecordset("queryA", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"

rs.MoveNext
Loop
rs.Close



'********************* created header of table 2
mailbody = [COLOR="Red"]mailbody &  [/COLOR]"<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column1 </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">column2 </p></Font></TD>" & _
"</TR>"

' add the data to the table
Set rs = CurrentDb.OpenRecordset("queryB", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"

rs.MoveNext
Loop
rs.Close

Set rs = CurrentDb.OpenRecordset("queryC", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![column1].Value & "</TD>" & _
"<TD><center>" & rs.Fields![column2].Value & "</TD>" & _
"</TR>"
rs.MoveNext
Loop
rs.Close


' <br> used to insert a line ( press enter) and send email
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "John.Doe@email.com"
.CC = "Jane.Doe@email.com"
.Subject = "Report for : " & Format(Date - 1, "mmm dd, yyyy") & ""
.HTMLBody = "Report for: " & Format(Date - 1, "MMM DD, YYYY <br> <br>") & mailbody & "</table>"
.Display
'.Send
End With
End Function
Click to expand...

There is no particular reason for using function over sub. I guess its just habit. Thanks for the solution! It worked but I need to add breaks because it was merging the two together.
 
You must log in or register to reply here.

Similar threads

E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
175
Trevor G
Trevor G
D
Darshan Hiranandani : How to Open Outlook in HTML Format Using DoCmd.SendObject in Access
Replies
3
Views
156
arnelgp
arnelgp
D
Darshan Hiranandani : How to Open Outlook in HTML Format Instead of Plain Text via VBA in Access?
Replies
1
Views
185
Minty
Minty
C
querying outlook
Replies
2
Views
195
CJ_London
C
S
How to Open Outlook Classic from website email address?
Replies
15
Views
545
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom