+ Reply to Thread
Results 1 to 10 of 10

Question for dummy

  1. #1
    Yuri Weinstein \(HotMail\)
    Guest

    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. #2
    Ron de Bruin
    Guest

    Re: Question for dummy

    Hi

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



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


    "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    > 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. #3
    Yuri Weinstein \(HotMail\)
    Guest

    Re: Question for dummy

    It's actually does not work for me. Any tricks I have to have?

    Thx

    "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    > Hi
    >
    > Try this
    > http://www.rondebruin.nl/mail/folder3/mail2.htm
    >
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message
    > news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >> 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. #4
    Ron de Bruin
    Guest

    Re: Question for dummy

    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)" <yuriw_NOSPAM@hotmail.com> wrote in message news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    > It's actually does not work for me. Any tricks I have to have?
    >
    > Thx
    >
    > "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    >> Hi
    >>
    >> Try this
    >> http://www.rondebruin.nl/mail/folder3/mail2.htm
    >>
    >>
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >>> 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. #5
    Yuri Weinstein \(HotMail\)
    Guest

    Re: Question for dummy

    Thx Ron,

    I think I got it working. Is there a way to preserve format of XLS file?

    "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    news:ezUocEfcFHA.3120@TK2MSFTNGP12.phx.gbl...
    > 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)" <yuriw_NOSPAM@hotmail.com> wrote in message
    > news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    >> It's actually does not work for me. Any tricks I have to have?
    >>
    >> Thx
    >>
    >> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    >> news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    >>> Hi
    >>>
    >>> Try this
    >>> http://www.rondebruin.nl/mail/folder3/mail2.htm
    >>>
    >>>
    >>>
    >>> --
    >>> Regards Ron de Bruin
    >>> http://www.rondebruin.nl
    >>>
    >>>
    >>> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message
    >>> news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >>>> 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. #6
    Yuri Weinstein \(HotMail\)
    Guest

    Re: Question for dummy

    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 = "yuriw@mercedsystems.com"
    .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 **** Kusleika his site
    'http://www.*****-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 Recipients:="yuriw@mercedsystems.com"
    End Sub

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

    "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    news:ezUocEfcFHA.3120@TK2MSFTNGP12.phx.gbl...
    > 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)" <yuriw_NOSPAM@hotmail.com> wrote in message
    > news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    >> It's actually does not work for me. Any tricks I have to have?
    >>
    >> Thx
    >>
    >> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    >> news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    >>> Hi
    >>>
    >>> Try this
    >>> http://www.rondebruin.nl/mail/folder3/mail2.htm
    >>>
    >>>
    >>>
    >>> --
    >>> Regards Ron de Bruin
    >>> http://www.rondebruin.nl
    >>>
    >>>
    >>> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message
    >>> news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >>>> 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. #7
    Yuri Weinstein \(HotMail\)
    Guest

    Re: Question for dummy

    I moved the function to miduel and is still does not work :-(

    "Yuri Weinstein (HotMail)" <yuriw@hotmail.com> wrote in message
    news:b3ise.2681$NU5.597@newssvr13.news.prodigy.com...
    > 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 = "yuriw@mercedsystems.com"
    > .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 **** Kusleika his site
    > 'http://www.*****-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 Recipients:="yuriw@mercedsystems.com"
    > End Sub
    >
    > ===============================================
    >
    > "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    > news:ezUocEfcFHA.3120@TK2MSFTNGP12.phx.gbl...
    >> 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)" <yuriw_NOSPAM@hotmail.com> wrote in message
    >> news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    >>> It's actually does not work for me. Any tricks I have to have?
    >>>
    >>> Thx
    >>>
    >>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    >>> news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    >>>> Hi
    >>>>
    >>>> Try this
    >>>> http://www.rondebruin.nl/mail/folder3/mail2.htm
    >>>>
    >>>>
    >>>>
    >>>> --
    >>>> Regards Ron de Bruin
    >>>> http://www.rondebruin.nl
    >>>>
    >>>>
    >>>> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message
    >>>> news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >>>>> 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. #8
    Ron de Bruin
    Guest

    Re: Question for dummy

    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)" <yuriw@hotmail.com> wrote in message news:mQise.2703$NU5.283@newssvr13.news.prodigy.com...
    >I moved the function to miduel and is still does not work :-(
    >
    > "Yuri Weinstein (HotMail)" <yuriw@hotmail.com> wrote in message news:b3ise.2681$NU5.597@newssvr13.news.prodigy.com...
    >> 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 = "yuriw@mercedsystems.com"
    >> .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 **** Kusleika his site
    >> 'http://www.*****-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 Recipients:="yuriw@mercedsystems.com"
    >> End Sub
    >>
    >> ===============================================
    >>
    >> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:ezUocEfcFHA.3120@TK2MSFTNGP12.phx.gbl...
    >>> 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)" <yuriw_NOSPAM@hotmail.com> wrote in message news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    >>>> It's actually does not work for me. Any tricks I have to have?
    >>>>
    >>>> Thx
    >>>>
    >>>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    >>>>> Hi
    >>>>>
    >>>>> Try this
    >>>>> http://www.rondebruin.nl/mail/folder3/mail2.htm
    >>>>>
    >>>>>
    >>>>>
    >>>>> --
    >>>>> Regards Ron de Bruin
    >>>>> http://www.rondebruin.nl
    >>>>>
    >>>>>
    >>>>> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in message news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >>>>>> 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. #9
    Yuri Weinstein \(HotMail\)
    Guest

    Re: Question for dummy

    It works now! Thx Ron.

    "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    news:OU%23wwrpcFHA.1324@tk2msftngp13.phx.gbl...
    >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)" <yuriw@hotmail.com> wrote in message
    > news:mQise.2703$NU5.283@newssvr13.news.prodigy.com...
    >>I moved the function to miduel and is still does not work :-(
    >>
    >> "Yuri Weinstein (HotMail)" <yuriw@hotmail.com> wrote in message
    >> news:b3ise.2681$NU5.597@newssvr13.news.prodigy.com...
    >>> 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 = "yuriw@mercedsystems.com"
    >>> .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 **** Kusleika his site
    >>> 'http://www.*****-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 Recipients:="yuriw@mercedsystems.com"
    >>> End Sub
    >>>
    >>> ===============================================
    >>>
    >>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    >>> news:ezUocEfcFHA.3120@TK2MSFTNGP12.phx.gbl...
    >>>> 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)" <yuriw_NOSPAM@hotmail.com> wrote in message
    >>>> news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    >>>>> It's actually does not work for me. Any tricks I have to have?
    >>>>>
    >>>>> Thx
    >>>>>
    >>>>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    >>>>> news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    >>>>>> Hi
    >>>>>>
    >>>>>> Try this
    >>>>>> http://www.rondebruin.nl/mail/folder3/mail2.htm
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>> --
    >>>>>> Regards Ron de Bruin
    >>>>>> http://www.rondebruin.nl
    >>>>>>
    >>>>>>
    >>>>>> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in
    >>>>>> message news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    >>>>>>> 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. #10
    Angus
    Guest

    Re: Question for dummy

    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" <rondebruin@kabelfoon.nl> wrote in message
    > news:OU%23wwrpcFHA.1324@tk2msftngp13.phx.gbl...
    > >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)" <yuriw@hotmail.com> wrote in message
    > > news:mQise.2703$NU5.283@newssvr13.news.prodigy.com...
    > >>I moved the function to miduel and is still does not work :-(
    > >>
    > >> "Yuri Weinstein (HotMail)" <yuriw@hotmail.com> wrote in message
    > >> news:b3ise.2681$NU5.597@newssvr13.news.prodigy.com...
    > >>> 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 = "yuriw@mercedsystems.com"
    > >>> .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 **** Kusleika his site
    > >>> 'http://www.*****-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 Recipients:="yuriw@mercedsystems.com"
    > >>> End Sub
    > >>>
    > >>> ===============================================
    > >>>
    > >>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    > >>> news:ezUocEfcFHA.3120@TK2MSFTNGP12.phx.gbl...
    > >>>> 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)" <yuriw_NOSPAM@hotmail.com> wrote in message
    > >>>> news:wN0se.1460$NU5.434@newssvr13.news.prodigy.com...
    > >>>>> It's actually does not work for me. Any tricks I have to have?
    > >>>>>
    > >>>>> Thx
    > >>>>>
    > >>>>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    > >>>>> news:%23Rk8XjecFHA.3504@TK2MSFTNGP12.phx.gbl...
    > >>>>>> Hi
    > >>>>>>
    > >>>>>> Try this
    > >>>>>> http://www.rondebruin.nl/mail/folder3/mail2.htm
    > >>>>>>
    > >>>>>>
    > >>>>>>
    > >>>>>> --
    > >>>>>> Regards Ron de Bruin
    > >>>>>> http://www.rondebruin.nl
    > >>>>>>
    > >>>>>>
    > >>>>>> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@hotmail.com> wrote in
    > >>>>>> message news:L80se.1438$NU5.588@newssvr13.news.prodigy.com...
    > >>>>>>> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1