Make HTML table lines visible via vba

megatronixs · Feb 13th 2015

Hi all,

I have the below code to create an email via access and insert a table that is converted to rtf format for sending.
When it is converted to rtf, the table lines are not visible any more. What can I change in the code to make the lines visible?

HTML

Option Compare Database
Private Sub Command11_Click()
Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
oMail.HTMLBody = "<html><body><p>Dear reader,</p><p>Please find below information on the letter.</p>" & "<p>Any queries please let us know</p><p>Regards</p><TABLE border=2><TR><TD>Rating:</TD><TD>1</TD></TR><TR><TD>BIN:</TD><TD>1234567</TD></TR><TR><TD>Company name:</TD><TD>Something Ltd.</TD></TR><TR><TD>Status:<TD>New</TD></TABLE>  "
oMail.Subject = "Status on case with BIN nr: "
oMail.To = "Doodle, Jhon"
oMail.BodyFormat = 3
oMail.Display
Set oMail = Nothing
Set oApp = Nothing 
End Sub

Display More

Greetings.

ashu1990 · Feb 13th 2015

Re: Make HTML table lines visible via vba

try this

HTML

Private Sub Command11_Click()
       
    Set oApp = CreateObject("Outlook.application")
    Set oMail = oApp.CreateItem(olMailItem)
    oMail.HTMLBody = "<html> <head> <style> table, th, td {border: 1px solid black;} </style> </head> <body> <p>Dear reader,</p><p>Please find below information on the letter.</p> <p>Any queries please let us know</p><p>Regards</p> <TABLE><TR><TD>Rating:</TD><TD>1</TD></TR><TR><TD>BIN:</TD><TD>1234567</TD></TR><TR><TD>Company name:</TD><TD>Something Ltd.</TD></TR><TR><TD>Status:<TD>New</TD></TABLE> </body> </html>"
    oMail.Subject = "Status on case with BIN nr: "
    oMail.To = "Doodle, Jhon"
    oMail.BodyFormat = 2
    oMail.Display
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Display More

megatronixs · Feb 13th 2015

Re: Make HTML table lines visible via vba

Hi,

Thanks for you tip. The only thing is that when it is in HTML and not RTF, my macro in outlook wil not be able to find text inside the tables and will not work.
Any chance the below code can be adjusted to see the HTML and find the words inside the table?

Code

[size=10] Public iItem As Integer
Option Explicit
Sub ExtractData()
Dim oItem As MailItem
If Application.ActiveExplorer.Selection.Count = 0 Then
MsgBox "No Items selected!", vbCritical, "Error"
Exit Sub
End If
For Each oItem In ActiveExplorer.Selection
If oItem.Subject = "Status on case with BIN nr: " Then
CopyToExcel oItem
End If
Next oItem
Set oItem = Nothing
End Sub
Sub CopyToExcel(olItem As MailItem)
Dim objItem As Object
Dim dtDate As Date 'needed
Dim sName As String 'needed
Dim oMail As Outlook.MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText As Variant
Dim sText As String
Dim sAddr As String
Dim vAddr As Variant
Dim vItem As Variant
Dim i As Long, j As Long
Dim rCount As Long
Dim bXStarted As Boolean
Dim FinalRow As Long
Dim strParameter As String
Dim strParamValue As String
Dim Company_name As String
Dim BIN_nr As String ' Risk_rating:
Dim Risk_rating As String
Dim Status As String
Dim sPath As String 'needed
Dim path2 As String
Dim path1 As String
Dim SavePath As String
Set oMail = objItem
　
On Error Resume Next
For Each objItem In ActiveExplorer.Selection
Set oMail = objItem
'Process the message
With olItem
sText = olItem.Body
vText = Split(sText, Chr(13))
For i = UBound(vText) To 0 Step -1
vItem = Split(vText(i), Chr(9))
strParameter = ""
strParamValue = ""
strParameter = Trim(Replace(vItem(0), Chr(10), ""))
strParamValue = Trim(vItem(1))
Select Case strParameter
Case "Rating:"
Risk_rating = strParamValue
Debug.Print Rating
Case "BIN nr:"
BIN_nr = strParamValue
Debug.Print BIN_nr
Case "Company name:"
Company_name = strParamValue
Debug.Print Company_name
Case "Status:"
Status = strParamValue
Debug.Print Status
End Select
'Move the email code here below.
Next i
End With
path1 = "Y:\Developement\Cases\"
path2 = Rating & "\" ''''''& BIN_nr & "_" & Company_name
SavePath = path1 & path2
　
　
'If objItem.MessageClass = "IPM.Note" Then
sName = oMail.Subject 'needed
ReplaceCharsForFileName sName, "-" 'needed
dtDate = oMail.ReceivedTime
sName = Format(dtDate, "yyyymmdd", vbUseSystemDayOfWeek, _
vbUseSystem) & Format(dtDate, "-hhnnss", _
vbUseSystemDayOfWeek, vbUseSystem) & "-" & sName & ".msg" 'needed
'If BIN_nr = "" Then Exit Sub
oMail.SaveAs SavePath & sName, olMsg 'needed
objItem.UnRead = False
Debug.Print
' End If
Next
End Sub
[/SIZE]

Display More

ashu1990 · Feb 13th 2015

Re: Make HTML table lines visible via vba

i used your code and it worked fine for me it took the table criteria and the staus as you described...

what is the issue are you getting any error?

megatronixs · Feb 13th 2015

Re: Make HTML table lines visible via vba

Hi,
When the table is in RTF it will find the text, if in HTML it does not.
Maybe language settings in windows?

For me this is strange and no clue where I went wrong.

Greetings.

ashu1990 · Feb 13th 2015

Re: Make HTML table lines visible via vba

have you tried my code and then use your code for extracting the data from mail it should do the trick try using debug mode go step by step and see if the strParameter comes with the data and so on...

megatronixs · Feb 13th 2015

Re: Make HTML table lines visible via vba

Hi,

Nothing in the immediate window
when I run the macro with the

Code

oMail.BodyFormat = 3

it wil just work fine, but then I don't have the table borders. I will try to use this code at home and see what will be (at work I use office 2003 and at home 2013, could be difference).

Greetings.

ashu1990 · Feb 16th 2015

Re: Make HTML table lines visible via vba

i didn't get it i ran the code and worked perfect for me anyways here's a useful link i found might help you http://www.pindari.com/rtf3.html

megatronixs · Feb 18th 2015

Re: Make HTML table lines visible via vba

Hi,

I will try to have a look at it and see if I can make something work. For now I use it without the borders and it works.

Greetings.

Make HTML table lines visible via vba

Participate now!

Share