Use Excel HTML File as HTML body text in email

[PBAPaul]

Hi All

I have a system that will generate an invoice by using a worksheet "Invoice" and using VBA to enter the various details. The worksheet "Invoice" is formatted with borders, left and right aligned text, emboldened text etc.

I can send this as an attachment but some recipients may not have Excel on their systems. What I would like to do is use the formatted worksheet "Invoice" as the body of an email.

After creating an invoice, I copy the "Invoice" worksheet as a stand alone workbook eg "Inv_01.xls" and I have then saved that as a webpage creating an HTML document.

Is there any way of using that HTML page as the body of an email?

I have played around with the following code but with no joy.

 Set appOutLook = CreateObject("Outlook.Application")
 Set MailOutLook = appOutLook.CreateItem(olMailItem)
 With MailOutLook
  .To = MyRecipient
  .Subject = MySubject
  .HTMLBody = MyHTMFile
  .Send
 End With

 Set MailOutLook = Nothing
 Set appOutLook = Nothing

I set the variable MyHTMFile as the full path of my created HTML page.

Any ideas please?

Paul

 

[CautionMP]

[tt].HTMLBody[/tt] is a string. You can open the file, get the contents and then drop them into [tt].HTMLBody[/tt]

'...
Dim intFile As Integer
Dim strLine As String, strHTML As String
'...
intFile = FreeFile
Open MyHTMLFile For Input As #intFile
While Not EOF(intFile)
   Line Input #intFile, strLine
   strHTML = strHTML & strLine
Wend
Close #intFIle
'...
   .HTMLBody = strHTML
'...

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[PBAPaul]

Thank you CMP.

The technique works like a dream!

Paul