Excel VB to send selected range as html email attachment

  • Thread starter Thread starter strober
  • Start date Start date
S

strober

I need to generate a html file from the selected range on
a worksheet and then email it as an attachment to an email
address located in a cell on the sheet.

Can anyone shed some light on how to do this?

I can get it into the message itself, but need to be able
to get it as an attachment.

Cheers,

strober
 
Try this one strober with the email address in A1

Sub Mail_HTML_File__Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim TempFile As String

TempFile = Environ$("temp") & "/" & ActiveWorkbook.Name & _
Format(Now, " dd-mm-yy h-mm-ss") & ".htm"

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
source:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)

End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add TempFile
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill TempFile ' delete the htm file
End Sub
 
Back
Top