HTML table borders not displaying in GMAIL (only)

john316swan

Board Regular
Joined
Oct 13, 2016
Messages
54
Hi Excel Guru's,

I am having trouble with html borders when the email is read via gMail (Outlook/Apple Mail Client both read it). I googled and there wasn't much info, other than someone suggesting I change the .BodyFormat = 2 (or 'olFormatHTML)...which didn't work :( Please help me figure this out, it's driving me crazy :mad:

Sub emailCoaches()
'This will send a weekly email to coaches letting them know which students are on hold for their scholarship offer
Dim ws As Worksheet
Dim sportLr As Double, recruitsLr As Double
Dim x As Double, y As Double
Dim sport As String, studentHold As String, holdList As String
Dim strHeader As String, strbody As String, strFooter As String
Dim SigString As String, signature As String
Dim OL As Outlook.Application
Dim holdEmail As Outlook.MailItem


sportLr = Sheets("Tables").Cells(Rows.Count, 25).End(xlUp).Row
recruitsLr = Sheets("Recruits").Cells(Rows.Count, 1).End(xlUp).Row
SigString = Environ("appdata") & "\Microsoft\Signatures\No Logo.htm"
signature = GetSignature(SigString)


With Application
.EnableEvents = False
.ScreenUpdating = False
End With


'This is the header that won't change for all emails
strHeader = "******>Dear Coach,<br><br>" _
& "Below is a list of all students for whom we can send a financial aid package to, " _
& "but do not have an athletic offer:<br><br>"
'This is the footer that won't change for all emails
strFooter = "<br>The data and amounts in the above table are <b>ESTIMATES</b> based on the most recent information we have. " _
& "If an art scholarship is <b>POSSIBLE</b> you can confirm with the student whether they plan on actually " _
& "auditioning. Please let me or Cathy know if anything looks funky or you have any questions.</BODY></html>" & signature


'This code creates a loop that goes through each of the hold reasons
For x = 2 To sportLr
nextSport:
'Sheets("Tables").Activate
sport = Sheets("Tables").Cells(x, 25)
Sheets("Recruits").Activate
'Now we're going to loop through all students and create a table of holds that meet the "X" criteria
For y = 2 To recruitsLr
If Cells(y, 16) = sport Then
studentHold = "<tr><td nowrap>" & Cells(y, 1) & "</td><td nowrap>" & Cells(y, 2) & "</td><td nowrap>" & Cells(y, 3) & "</td><td nowrap>" & Cells(y, 9) _
& "</td><td>" & Cells(y, 18) & "</td><td>" & Format(Cells(y, 21), "#,###") & "</td><td nowrap>" & Format(Cells(y, 22), "#,###") & "</td><td nowrap>" & Format(Cells(y, 23), "#,###") _
& "</td><td nowrap>" & Format(Cells(y, 24), "#,###") & "</td><td nowrap>" & Format(Cells(y, 25), "#,###") & "</td><td nowrap>" & Cells(y, 30) _
& "</td><td nowrap>" & Format(Cells(y, 26), "#,###") & "</td><td nowrap>" & Format(Cells(y, 27), "#,###") & "</td><td nowrap>" & Format(Cells(y, 28), "#,###") & "</td>"
holdList = holdList & studentHold & "</tr>"
End If
Next y
'Once you loop through list, if there are no holds, we will then skip to next sport
If holdList = "" Then
If x > sportLr Then
Exit Sub
End If
x = x + 1
holdlst = ""
GoTo nextSport
End If
'Now we will send the email to the coach
Set OL = CreateObject("Outlook.Application")
Set holdEmail = OL.CreateItem(0)

strbody = "<style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px;}</style>" _
& "<table><tr><th no wrap>Student ID</th><th nowrap>Last</th><th nowrap>First</th><th nowrap>FAFSA</th><th nowrap>Room & Board" _
& "</th><th nowrap>Academic</th><th nowrap>Scholar Event</th><th nowrap>Pell</th><th nowrap>Cal Grant" _
& "</th><th nowrap>Tot. Free Money</th><th nowrap>Arts Possible</th><th nowrap>Est. Loans</th><th nowrap>Est Costs" _
& "</th><th nowrap>Est Bal B4 Ath $</th></tr>" & holdList & "</table>"

With holdEmail
.To = Sheets("Tables").Cells(x, 27)
.Subject = "19-20 Financial Aid Package Holds for " & Sheets("Tables").Cells(x, 26)
.HTMLBody = strHeader & strbody & strFooter
.BodyFormat = 2
.Send
End With
'Now we reset hold list and move to next sport
Set OL = Nothing
Set holdEmail = Nothing
holdList = ""
Next x


With Application
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub
Function GetSignature(ByVal sFile As String) As String
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetSignature = ts.ReadAll
ts.Close
End Function


<style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px;}</style>
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

john316swan

Board Regular
Joined
Oct 13, 2016
Messages
54
OR is there a way to copy the HTML table I created to a temporary workbook with borders and then email it?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,477
Office Version
365
Platform
Windows
Are you applying any formatting/styles to the HTML you are creating?
 

john316swan

Board Regular
Joined
Oct 13, 2016
Messages
54
Yes, but it's hard to show because the posting keeps converting it to HTML...is there a way to post the vba in a way that won't convert it to HTML like it did above? Code is posted at https://codeshare.io/5Z0y4o Hopefully I am not violating any rules by posting link to external code, if so I can delete it right away. Sorry in advance!<style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px;}</style>
 
Last edited:

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,769
Office Version
2013
Platform
Windows
Last edited:

john316swan

Board Regular
Joined
Oct 13, 2016
Messages
54
Ok, I think I figured out how to show the HTML code :)

Code:
Sub emailCoaches()
'This will send a weekly email to coaches letting them know which students are on hold for their scholarship offer
Dim ws As Worksheet
Dim sportLr As Double, recruitsLr As Double
Dim x As Double, y As Double
Dim sport As String, studentHold As String, holdList As String
Dim strHeader As String, strbody As String, strFooter As String
Dim SigString As String, signature As String
Dim OL As Outlook.Application
Dim holdEmail As Outlook.MailItem


If [UpdateWbk] = True And Weekday(Now(), vbTuesday = 1) Then


    sportLr = Sheets("Tables").Cells(Rows.Count, 25).End(xlUp).Row
    recruitsLr = Sheets("Recruits").Cells(Rows.Count, 1).End(xlUp).Row
    SigString = Environ("appdata") & "\Microsoft\Signatures\No Logo.htm"
    signature = GetSignature(SigString)
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    'This is the header that won't change for all emails
    strHeader = [HTML]"******>Dear Coach,<br><br>" _                & "Below is a list of all students for whom we can send a financial aid package to, " _                & "but do not have an athletic offer:<br><br>"    'This is the footer that won't change for all emails    strFooter = "<br>The data and amounts in the above table are <b>ESTIMATES</b> based on the most recent information we have. " _                & "If an art scholarship is <b>POSSIBLE</b> you can confirm with the student whether they plan on actually " _                & "auditioning - <b>HOWEVER</b> even with a ""False"" they may still apply although they did not indicate they were interested on their application. " _                & " If an amount is blank for Cal Grant, it means we have not yet been able to verify their eligibility. " _                & "Please let me or Cathy know if anything looks funky or you have any questions.</BODY></html>"[/HTML] & signature
    
    'This code creates a loop that goes through each of the hold reasons
    For x = 2 To sportLr
nextSport:
        'Sheets("Tables").Activate
        sport = Sheets("Tables").Cells(x, 25)
        Sheets("Recruits").Activate
        'Now we're going to loop through all students and create a table of holds that meet the "X" criteria
        For y = 2 To recruitsLr
            If Cells(y, 16) = sport Then
<div>                studentHold = [HTML]"<tr><td nowrap>" & Cells(y, 1) & "</td><td nowrap>" & Cells(y, 2) & "</td><td nowrap>" & Cells(y, 3) & "</td><td nowrap>" & Cells(y, 9) _                & "</td><td>" & Cells(y, 18) & "</td><td>" & Format(Cells(y, 21), "#,###") & "</td><td nowrap>" & Format(Cells(y, 22), "#,###") & "</td><td nowrap>" & Format(Cells(y, 23), "#,###") _                & "</td><td nowrap>" & Format(Cells(y, 24), "#,###") & "</td><td nowrap>" & Format(Cells(y, 25), "#,###") & "</td><td nowrap>" & Cells(y, 30) _                & "</td><td nowrap>" & Format(Cells(y, 26), "#,###") & "</td><td nowrap>" & Format(Cells(y, 27), "#,###") & "</td><td nowrap>" & Format(Cells(y, 28), "#,###") & "</td>"                holdList = holdList & studentHold & "</tr>"[/HTML]            End If
        Next y
        'Once you loop through list, if there are no holds, we will then skip to next sport
        If holdList = "" Then
            If x > sportLr Then
                Exit Sub
            End If
            x = x + 1
            holdlst = ""
            GoTo nextSport
        End If
        'Now we will send the email to the coach
        Set OL = CreateObject("Outlook.Application")
        Set holdEmail = OL.CreateItem(0)
    
<div>        [HTML]strbody = "<style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px;}</style>" _                  & "<table><tr><th no wrap>Student ID</th><th nowrap>Last</th><th nowrap>First</th><th nowrap>FAFSA</th><th nowrap>Room & Board" _                  & "</th><th nowrap>Academic</th><th nowrap>Scholar Event</th><th nowrap>Pell</th><th nowrap>Cal Grant" _                  & "</th><th nowrap>Tot. Free Money</th><th nowrap>Arts Possible</th><th nowrap>Est. Loans</th><th nowrap>Est Costs" _                  & "</th><th nowrap>Est Bal B4 Ath $</th></tr>" & holdList & "</table>"[/HTML]    
        With holdEmail
            .To = Sheets("Tables").Cells(x, 27)
            .Subject = "AUTOMATED 19-20 Financial Aid Package Holds for " & Sheets("Tables").Cells(x, 26)
            .HTMLBody = strHeader & strbody & strFooter
            .BodyFormat = 2
            .Send
        End With
        'Now we reset hold list and move to next sport
        Set OL = Nothing
        Set holdEmail = Nothing
        holdList = ""
    Next x
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With


End If


End Sub

Function GetSignature(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetSignature = ts.ReadAll
    ts.Close
End Function
<style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px;}</style><style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px;}</style>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,477
Office Version
365
Platform
Windows
Try wrapping the whole thing in HTML Code tags.
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,769
Office Version
2013
Platform
Windows
Hi,

see if the following works for you.
I have put a space in all the tags so they display here and I did not check 'studentHold' but the table header appears in my Gmail without issue.
The style is added to the head, where it would be in an html page and all the nowrap removed as I added it to the style.

Code:
PgeStart = "< html >< head >"
PgeEnd = "< /html >"
PgeStyle = "<style>table, th, td {border: 1px solid black; border-collapse: collapse; word-wrap: break-word; text-align: center; padding: 5px; white-space:no wrap;}< /style >< /head >" _



StrHeader = "******>Dear Coach,<br><br>" _
 & "Below is a list of all students for whom we can send a financial aid package to, " _
 & "but do not have an athletic offer:<br><br>"


 strFooter = "<br>The data and amounts in the above table are <b>ESTIMATES</b> based on the most recent information we have. " _
 & "If an art scholarship is <b>POSSIBLE</b> you can confirm with the student whether they plan on actually " _
 & "auditioning. Please let me or Cathy know if anything looks funky or you have any questions.</body></html>" & Signature



 studentHold = "<tr>< td >" & Cells(y, 1) & "< /td >< td >" & Cells(y, 2) & "< /td >< td >" & Cells(y, 3) & "< /td >< td >" & Cells(y, 9) _
 & "< /td >< td >" & Cells(y, 18) & "< /td >< td >" & Format(Cells(y, 21), "#,###") & "< /td >< td >" & Format(Cells(y, 22), "#,###") & "< /td >< td >" & Format(Cells(y, 23), "#,###") _
 & "< /td >< td >" & Format(Cells(y, 24), "#,###") & "< /td >< td >" & Format(Cells(y, 25), "#,###") & "< /td >< td >" & Cells(y, 30) _
 & "< /td >< td >" & Format(Cells(y, 26), "#,###") & "< /td >< td >" & Format(Cells(y, 27), "#,###") & "< /td >< td >" & Format(Cells(y, 28), "#,###") & "< /td >" _
 & holdList = holdList & studentHold & "</tr>"


StrBody = "<table><tr>< th >Student ID< /th >< th >Last< /th >< th >First< /th >< th >FAFSA< /th >< th >Room & Board" _
 & "< /th >< th >Academic< /th ><th nowrap>Scholar Event< /th ><th nowrap>Pell< /th >< th >Cal Grant" _
 & "< /th >< th >Tot. Free Money< /th >< th >Arts Possible< /th >< th >Est. Loans< /th >< th >Est Costs" _
 & "< /th >< th >Est Bal B4 Ath $< /th ></tr>" & holdList & "</table>"



StrMessage = PgeStart & PgeStyle & StrHeader & StrBody & strFooter


'HTMLBody[COLOR="#FF0000"]=strMessage[/COLOR]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,548
Messages
5,415,192
Members
403,571
Latest member
twinlock

This Week's Hot Topics

Top