VBA + Excel - Save ActiveSheet as hmt then Send Email

[mariocatch]

Hey All,

Is it possible to create a macro that saves the active sheet out as an hmt/htm format, then send that file in an email to someone?

I can save it out fine, just not sure how to create the email (i'm using outlook), then attach it automatically with vba.

-Mario

[tonyjambo]

i use something like this :

You will have to include the reference to your Microsoft outlook library (tools->references->Microsoft Outlook xxxx Object Library)

Private Sub sendMail()
Dim objOutlook As Outlook.Application
Set objOutlk = CreateObject("Outlook.Application")
Dim objMail As Outlook.MailItem
Set objMail = objOutlk.createitem(MailItem)

Dim mailTo

'Gather all of the mail contacts from the excel named range
For Each Value In range("Static!MainMail")
mailTo = mailTo & Value & ";"
Next

objMail.To = mailTo 'I like to get my address list from an excel range
objMail.Subject = "Subject" 'Enter Subject line here
objMail.cc = "" 'Enter an address here to include a carbon copy
strMsg = "Message Body" ' Enter your message Text here

'Attach the string to the mail object
objMail.Body = strMsg
objMail.Attachments.Add (filename) 'I grab my filename from my other code
objMail.Display 'Use this to display before sending, otherwise call objMail.Send to send without reviewing
'objMail.send Use this to send automatically

Set objMail = Nothing
Set objOutlk = Nothing
Set strMsg = Nothing
End Sub