Excel / Email Macro

[carl]

Hi Ron et al...

Just to refresh - from Excel, I am trying to run a macro
which will email the body of the active worksheet.

I set the reference in the VB Editor as instructed on the
website.

Then I opened a new module and pasted your macro (which I
slightly modded - email address / subject) into it.


The code got hung up at: .HTMLBody = SheetToHTML
(ActiveSheet)

The macro I pasted:

Sub Mail_ActiveSheet_Body()

Dim OutApp As Outlook.Application

Dim OutMail As Outlook.MailItem

Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail

.To = "(e-mail address removed)"

.CC = ""

.BCC = ""

.Subject = "Test"

.HTMLBody = SheetToHTML(ActiveSheet)

.Send 'or use .Display

End With

Application.ScreenUpdating = True

Set OutMail = Nothing

Set OutApp = Nothing

End Sub


Thanks for your assistance - it is much appreciated.

 

[Ron de Bruin]

You must copy the function also in the module

This macro will send the activesheet in the body
of the mail.

If you use this Outlook example you must add a reference
to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number

Sub Mail_ActiveSheet_Body()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = SheetToHTML(ActiveSheet)
.Send 'or use .Display
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Public Function SheetToHTML(sh As Worksheet)
'Function from Dick Kusleika his site
'http://www.dicks-clicks.com/excel/sheettohtml.htm
'Changed by Ron de Bruin 04-Nov-2003
Dim TempFile As String
Dim Nwb As Workbook
Dim myshape As Shape
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook
For Each myshape In Nwb.Sheets(1).Shapes
myshape.Delete
Next
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
Nwb.SaveAs TempFile, xlHtml
Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function

 

[Matt]

Hi
I was reading this post and wanted to try this but I can not find
the Microsoft Outlook Object reference that is mentioned below. How do
I locate it? I did the 3 steps listed below but I do not see it there
I am using Outlook Express

This is from the thread
If you use this Outlook example you must add a reference
to the Microsoft outlook Library.


1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number


Hi Ron et al...

 

[Charles Maxson]

Matt,

You mentioned Outlook Express which is not automation server; Outlook full
version is. If you can't find it in the Reference dialog you can browse to
the Outlook type library. For Office 11 it is called "MSOUTL.OLB" and by
default will be installed in C:\Program Files\Microsoft Office\OFFICE11

BTW you should use the microsoft.public.office.developer.outlook.vba once
you get busy with this...