It works now! Thx Ron.

"Ron de Bruin" wrote in message
...
I will look at the file you send me.
Which excel version do you use

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Yuri Weinstein (HotMail)" wrote in message
...
I moved the function to miduel and is still does not work :-(

"Yuri Weinstein (HotMail)" wrote in message
...
Here is the code I came up with.

Two problems:

1. When user clicks No to send an email - I get an error
2. I get an error in line "Set ts =
fso.GetFile(TempFile).OpenAsTextStream(1, -2)" on close too

Any help is appreciated.

Thx

=================================================

Private Sub Workbook_Open()
'WriteTwiki
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
WriteTwiki
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Mail_ActiveSheet_Body
End Sub
Sub Mail_ActiveSheet_Body()
Dim OutApp As Object
Dim OutMail As Object
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Test Systems useage has changed, please review"
.HTMLBody = SheetToHTML(ActiveSheet)
'.Display
.Send 'or use .Display
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
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

Sub WriteTwiki()
ActiveWorkbook.SaveAs _

Filename:="\\files.mercedsystems.com\qa-public\QA\DOC\instance_usage_new.html",
_
FileFormat:=xlHtml
'ActiveWorkbook.SendMail "
End Sub

===============================================

"Ron de Bruin" wrote in message
...
Hi Yuri

Do you have Outlook ?
Do you set the reference (read the page)
Do you copy the function also in the module


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Yuri Weinstein (HotMail)" wrote in message
...
It's actually does not work for me. Any tricks I have to have?

Thx

"Ron de Bruin" wrote in message
...
Hi

Try this
http://www.rondebruin.nl/mail/folder3/mail2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Yuri Weinstein (HotMail)" wrote in
message ...
Hi all,

I am trying to do the following:

- have and Excel file that can be modified by multiple users
- when user modifies it on close, save it as HTML and
- send an email (with Active Sheet in the email body)

I can get email be embedded into email body.
Thank in advance for your help.

YuriW