Outlook problem using 2 Excel HTML sources for HTML body 1

Sweenster · May 9, 2006

Hi,

I am trying to automate e-mail sending of two sets of figures in the same Outlook mail but I am having problems.

I am having Excel save 2 spreadsheets to HTML and then combining them in Outlook using:

Code:

    'Read first file
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile("C:\Area.htm")
    Set ts = f.OpenAsTextStream(ForReading, tristateUseDefault)
    s = ts.ReadAll
    ts.Close
    
    'Read second file
    Set f = fs.GetFile("C:\Div.htm")
    Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
    s = s & ts.ReadAll
    myMailItem.HTMLBody = s
    ts.Close
    
    myMailItem.Save

It works fine apart from the fact that the colour schemes for each 'table' clash. Headers are grey, negative numbers red and postive green. I can not combine into one spreadsheets as number of columns etc are very different.

Our prefered solution was to insert Excel files as "objects" as you would do using Insert, Object but having read nearly all of the internet it would seem you can only add as attachments?!

Can I get around this at all?

CautionMP · May 10, 2006

Sweenster,
Here is a shot out of left field. Create the HTML source yourself, directly from the Excel worksheets. This will give you complete control of how the HTML is formatted.

Here is a sample routine in Excel (2k) that will take the used cells from Sheet1 and output them as a HTML file. It could be incorporated into your process to dynamically build [tt]myMailItem.HTMLBody[/tt].

Code:

Sub OutputHTMLTable()
Dim wksSource As Worksheet
Dim rngTable As Range
Dim rngRow As Range
Dim rngCell As Range
Dim s As String

'Get the table object
Set wksSource = Worksheets("sheet1")
Set rngTable = wksSource.UsedRange

'Write the HTML header
s = "<HTML><BODY>" & vbCrLf

'* This would begin the loop for each table you need to write
  'Write the table header
  s = s & "<TABLE>" & vbCrLf
  'Now write the rows and cells
  For Each rngRow In rngTable.Rows
    s = s & "<TR>"
    For Each rngCell In rngRow.Columns
      s = s & "<TD>" & rngCell.Value & "</TD>"
    Next rngCell
    s = s & "</TR>" & vbCrLf
  Next rngRow
  'Write the Table footer
  s = s & "</TABLE>" & vbCrLf
  '* This is the end of the table loop

'Write the HTML footer
s = s & "</BODY></HTML>"

'Clean up
Set rngCell = Nothing
Set rngRow = Nothing
Set wksSource = Nothing

'For demonstration I copied this text from the Immediate window,
'pasted it in notepad and saved as Test.htm. Then double clicked
'on Test.htm to view in IE
Debug.Print s
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

Sweenster · May 11, 2006

This works like a treat thanks very much.

Mark

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Outlook problem using 2 Excel HTML sources for HTML body 1

Sweenster

MIS

CautionMP

Programmer

Sweenster

MIS

Similar threads

Part and Inventory Search

Sponsor