| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Question for dummy
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
Thx Ron,
I think I got it working. Is there a way to preserve format of XLS file? "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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
I use the following VBA and it really helps.
However, if i want to add a textbody from cell A1 at worksheet "data" before the sheettohtml, how? "Yuri Weinstein (HotMail)" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
| Another question for Jon | Charts and Charting in Excel | |||
| Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
| An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
| Have a question on scrolling sum with Excel. | Excel Worksheet Functions | |||
Linear Mode
