HTML column alignment

[Lizard07]

Hello - I have a macro that automatically sends emails. The macro creates a temporary html file from an excel spreadsheet and then copies it in as the body. I have had no issues with it, but for some reason there is a weird cell alignment in one of columns (see email below)


Good Morning,



Please provide the delivery time for the following shipment(s):
****** name=ProgId content=Excel.Sheet>****** name=Generator content="Microsoft Excel 11"><LINK rel=File-List href="19-09-11%2010-43-50_files/filelist.xml"><STYLE id=Sheet1_515_Styles><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl15515 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></STYLE> <!--The following information was generated by Microsoft Office Excel's Publishas Web Page wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!----------------------------->

<TABLE style="WIDTH: 251pt; BORDER-COLLAPSE: collapse; TABLE-LAYOUT: fixed" border=0 cellSpacing=0 cellPadding=0 width=335 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 81pt; HEIGHT: 12.75pt" class=xl15515 height=17 width=108>Carrier</TD><TD style="WIDTH: 83pt" class=xl15515 width=111>Tie Back #</TD><TD style="WIDTH: 87pt" class=xl15515 width=116>Trailer</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" class=xl15515 height=17>BOUF</TD><TD class=xl15515 align=right x:num>17147325</TD><TD class=xl15515>lctv967567</TD></TR></TBODY></TABLE>​

<!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!----------------------------->****** name=Generator content="MS Exchange Server version 6.5.7036.0"><!-- Converted from text/rtf format -->

Thank-you,

I need the tie back # to shift over - how can I do this. I've tried copy and pasting the formats of the cells and that works, but then my signature is not included

Any ideas, thanks?

 

[Norie]

Is it only that small amount of data you want to generate the HTML for?

If it is it would probably be easier to write it yourself, using VBA of course, than saving as HTML etc.

 

[Lizard07]

The data is being pulled from a spreadsheet though and amount of rows of data varies each week.

How would I create this in VBA?

 

[Norie]

You can use a simple piece of code to create a simple table in HTML.

I'll try an post something but the last time I tried the board ate the HTML parts.

This is how the psuedo-code would look.

Option Explicit
Sub CreateHTMLTable()
Dim rngData As Range
Dim cl As Range
Dim rw As Range
Dim strHTML As String
Dim gt As String
Dim lt As String
 
    Set rngData = Range("A1:C10")    ' change to range with data
 
    lt = Chr(60)    ' open tag character
    gt = Chr(62)    ' close tag character
 
      ' start table
    strHTML = lt & "table border=1 cellspacing=0" & gt

    For Each rw In rngData.Rows
        ' add row to table
        strHTML = strHTML & lt & "tr" & gt
 
        For Each cl In rw.Cells
            'add data to row
            strHTML = strHTML & lt & "td" & gt & cl.Text & lt & "/td" & gt
        Next cl
 
        strHTML = strHTML & lt & "/tr" & gt
    Next rw
 
    strHTML = strHTML & lt & "/table" & gt
 
End Sub

This will create the code, in a string, for a simple HTML table using the data on the worksheet.

You can then insert this into the body of your email.

 

[Lizard07]

Ok great, I'll give it a try. And just to be clear - I can put this code in a separate module and then in order to incorporate it into the body of the email, I would replace the following

.HTMLBody = strbody & RangetoHTML(rng) & Signature

with

.HTMLBody = strbody & strHTML & Signature

Is there anything else that must be done in order to use the HTML table?

Thanks