+ Reply to Thread
Results 1 to 11 of 11

send sheet to email as html attachment

  1. #1
    Pieter
    Guest

    send sheet to email as html attachment

    Hello Readers,

    I use the following working code (thanks to ron de bruin) :

    Sub Mail_Loadingorder()
    Dim OutApp As Object
    Dim OutMail As Object
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    .CC = ""
    .BCC = ""
    .Subject = "Loadingorder " & Sheets("Loadingorder").Range("h2").Value
    .HTMLBody = SheetToHTML(ActiveSheet)
    .Send 'or use .Display
    End With
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub


    Now i want to change this to attach the sheet as an html attachment instead
    of the
    the sheet is standing in the body.

    Anyone know this solution..

    Greetings


  2. #2
    Tom Ogilvy
    Guest

    Re: send sheet to email as html attachment

    copy the sheet to a new workbook. SaveAs HTML and close the workbook. Use
    code at Ron's site to attach it to the email and send. Delete the file

    You can get most of the code you need by turning on the macro recorder and
    performing the actions manually.

    --
    Regards,
    Tom Ogilvy



    "Pieter" <[email protected]> wrote in message
    news:[email protected]...
    > Hello Readers,
    >
    > I use the following working code (thanks to ron de bruin) :
    >
    > Sub Mail_Loadingorder()
    > Dim OutApp As Object
    > Dim OutMail As Object
    > Application.ScreenUpdating = False
    > Set OutApp = CreateObject("Outlook.Application")
    > Set OutMail = OutApp.CreateItem(0)
    > With OutMail
    > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    > .CC = ""
    > .BCC = ""
    > .Subject = "Loadingorder " &

    Sheets("Loadingorder").Range("h2").Value
    > .HTMLBody = SheetToHTML(ActiveSheet)
    > .Send 'or use .Display
    > End With
    > Application.ScreenUpdating = True
    > Set OutMail = Nothing
    > Set OutApp = Nothing
    > End Sub
    >
    >
    > Now i want to change this to attach the sheet as an html attachment

    instead
    > of the
    > the sheet is standing in the body.
    >
    > Anyone know this solution..
    >
    > Greetings
    >




  3. #3
    Tom Ogilvy
    Guest

    Re: send sheet to email as html attachment

    In fact, his SheettoHTML function already creates the html file

    Nwb.SaveAs TempFile, xlHtml

    so you would just attach it instead of reading it

    --
    Regards,
    Tom Ogilvy


    "Pieter" <[email protected]> wrote in message
    news:[email protected]...
    > Hello Readers,
    >
    > I use the following working code (thanks to ron de bruin) :
    >
    > Sub Mail_Loadingorder()
    > Dim OutApp As Object
    > Dim OutMail As Object
    > Application.ScreenUpdating = False
    > Set OutApp = CreateObject("Outlook.Application")
    > Set OutMail = OutApp.CreateItem(0)
    > With OutMail
    > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    > .CC = ""
    > .BCC = ""
    > .Subject = "Loadingorder " &

    Sheets("Loadingorder").Range("h2").Value
    > .HTMLBody = SheetToHTML(ActiveSheet)
    > .Send 'or use .Display
    > End With
    > Application.ScreenUpdating = True
    > Set OutMail = Nothing
    > Set OutApp = Nothing
    > End Sub
    >
    >
    > Now i want to change this to attach the sheet as an html attachment

    instead
    > of the
    > the sheet is standing in the body.
    >
    > Anyone know this solution..
    >
    > Greetings
    >




  4. #4
    Pieter
    Guest

    RE: send sheet to email as html attachment

    Yes want just attach it instead of reading it.
    i am not good at vba..



    "Pieter" wrote:

    > Hello Readers,
    >
    > I use the following working code (thanks to ron de bruin) :
    >
    > Sub Mail_Loadingorder()
    > Dim OutApp As Object
    > Dim OutMail As Object
    > Application.ScreenUpdating = False
    > Set OutApp = CreateObject("Outlook.Application")
    > Set OutMail = OutApp.CreateItem(0)
    > With OutMail
    > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    > .CC = ""
    > .BCC = ""
    > .Subject = "Loadingorder " & Sheets("Loadingorder").Range("h2").Value
    > .HTMLBody = SheetToHTML(ActiveSheet)
    > .Send 'or use .Display
    > End With
    > Application.ScreenUpdating = True
    > Set OutMail = Nothing
    > Set OutApp = Nothing
    > End Sub
    >
    >
    > Now i want to change this to attach the sheet as an html attachment instead
    > of the
    > the sheet is standing in the body.
    >
    > Anyone know this solution..
    >
    > Greetings
    >


  5. #5
    Tom Ogilvy
    Guest

    Re: send sheet to email as html attachment

    Untested, but this should be along the lines of what you want:

    Sub Mail_ActiveSheet_Attach_as_HTML()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim sStr as String
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    sStr = SheetToHTMLFile(ActiveSheet)
    .Attachments.Add sStr
    .Send 'or use .Display
    End With
    Kill sSTr
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub






    Public Function SheetToHTMLFile(sh As Worksheet)
    'Function from **** Kusleika his site
    'http://www.*****-clicks.com/excel/sheettohtml.htm
    'Changed by Ron de Bruin 04-Nov-2003
    'Modified to just save the file by TWOgilvy 10/24/2005
    ' and pass back the fully qualified file name
    Dim TempFile As String
    Dim Nwb As Workbook
    Dim myshape As Shape
    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 Nwb = Nothing
    SheetToHTMLFile = TempFile
    End Function

    --
    Regards,
    Tom Ogilvy

    "Pieter" <[email protected]> wrote in message
    news:[email protected]...
    > Yes want just attach it instead of reading it.
    > i am not good at vba..
    >
    >
    >
    > "Pieter" wrote:
    >
    > > Hello Readers,
    > >
    > > I use the following working code (thanks to ron de bruin) :
    > >
    > > Sub Mail_Loadingorder()
    > > Dim OutApp As Object
    > > Dim OutMail As Object
    > > Application.ScreenUpdating = False
    > > Set OutApp = CreateObject("Outlook.Application")
    > > Set OutMail = OutApp.CreateItem(0)
    > > With OutMail
    > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    > > .CC = ""
    > > .BCC = ""
    > > .Subject = "Loadingorder " &

    Sheets("Loadingorder").Range("h2").Value
    > > .HTMLBody = SheetToHTML(ActiveSheet)
    > > .Send 'or use .Display
    > > End With
    > > Application.ScreenUpdating = True
    > > Set OutMail = Nothing
    > > Set OutApp = Nothing
    > > End Sub
    > >
    > >
    > > Now i want to change this to attach the sheet as an html attachment

    instead
    > > of the
    > > the sheet is standing in the body.
    > >
    > > Anyone know this solution..
    > >
    > > Greetings
    > >




  6. #6
    Pieter
    Guest

    Re: send sheet to email as html attachment

    Thanks for your time but does not work...


    "Tom Ogilvy" wrote:

    > Untested, but this should be along the lines of what you want:
    >
    > Sub Mail_ActiveSheet_Attach_as_HTML()
    > Dim OutApp As Outlook.Application
    > Dim OutMail As Outlook.MailItem
    > Dim sStr as String
    > Application.ScreenUpdating = False
    > Set OutApp = CreateObject("Outlook.Application")
    > Set OutMail = OutApp.CreateItem(olMailItem)
    > With OutMail
    > .To = "[email protected]"
    > .CC = ""
    > .BCC = ""
    > .Subject = "This is the Subject line"
    > sStr = SheetToHTMLFile(ActiveSheet)
    > .Attachments.Add sStr
    > .Send 'or use .Display
    > End With
    > Kill sSTr
    > Application.ScreenUpdating = True
    > Set OutMail = Nothing
    > Set OutApp = Nothing
    > End Sub
    >
    >
    >
    >
    >
    >
    > Public Function SheetToHTMLFile(sh As Worksheet)
    > 'Function from **** Kusleika his site
    > 'http://www.*****-clicks.com/excel/sheettohtml.htm
    > 'Changed by Ron de Bruin 04-Nov-2003
    > 'Modified to just save the file by TWOgilvy 10/24/2005
    > ' and pass back the fully qualified file name
    > Dim TempFile As String
    > Dim Nwb As Workbook
    > Dim myshape As Shape
    > 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 Nwb = Nothing
    > SheetToHTMLFile = TempFile
    > End Function
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Pieter" <[email protected]> wrote in message
    > news:[email protected]...
    > > Yes want just attach it instead of reading it.
    > > i am not good at vba..
    > >
    > >
    > >
    > > "Pieter" wrote:
    > >
    > > > Hello Readers,
    > > >
    > > > I use the following working code (thanks to ron de bruin) :
    > > >
    > > > Sub Mail_Loadingorder()
    > > > Dim OutApp As Object
    > > > Dim OutMail As Object
    > > > Application.ScreenUpdating = False
    > > > Set OutApp = CreateObject("Outlook.Application")
    > > > Set OutMail = OutApp.CreateItem(0)
    > > > With OutMail
    > > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    > > > .CC = ""
    > > > .BCC = ""
    > > > .Subject = "Loadingorder " &

    > Sheets("Loadingorder").Range("h2").Value
    > > > .HTMLBody = SheetToHTML(ActiveSheet)
    > > > .Send 'or use .Display
    > > > End With
    > > > Application.ScreenUpdating = True
    > > > Set OutMail = Nothing
    > > > Set OutApp = Nothing
    > > > End Sub
    > > >
    > > >
    > > > Now i want to change this to attach the sheet as an html attachment

    > instead
    > > > of the
    > > > the sheet is standing in the body.
    > > >
    > > > Anyone know this solution..
    > > >
    > > > Greetings
    > > >

    >
    >
    >


  7. #7
    Ron de Bruin
    Guest

    Re: send sheet to email as html attachment

    Hi Pieter

    I promise to add a example on my site this evening


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


    "Pieter" <[email protected]> wrote in message news:[email protected]...
    > Thanks for your time but does not work...
    >
    >
    > "Tom Ogilvy" wrote:
    >
    >> Untested, but this should be along the lines of what you want:
    >>
    >> Sub Mail_ActiveSheet_Attach_as_HTML()
    >> Dim OutApp As Outlook.Application
    >> Dim OutMail As Outlook.MailItem
    >> Dim sStr as String
    >> Application.ScreenUpdating = False
    >> Set OutApp = CreateObject("Outlook.Application")
    >> Set OutMail = OutApp.CreateItem(olMailItem)
    >> With OutMail
    >> .To = "[email protected]"
    >> .CC = ""
    >> .BCC = ""
    >> .Subject = "This is the Subject line"
    >> sStr = SheetToHTMLFile(ActiveSheet)
    >> .Attachments.Add sStr
    >> .Send 'or use .Display
    >> End With
    >> Kill sSTr
    >> Application.ScreenUpdating = True
    >> Set OutMail = Nothing
    >> Set OutApp = Nothing
    >> End Sub
    >>
    >>
    >>
    >>
    >>
    >>
    >> Public Function SheetToHTMLFile(sh As Worksheet)
    >> 'Function from **** Kusleika his site
    >> 'http://www.*****-clicks.com/excel/sheettohtml.htm
    >> 'Changed by Ron de Bruin 04-Nov-2003
    >> 'Modified to just save the file by TWOgilvy 10/24/2005
    >> ' and pass back the fully qualified file name
    >> Dim TempFile As String
    >> Dim Nwb As Workbook
    >> Dim myshape As Shape
    >> 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 Nwb = Nothing
    >> SheetToHTMLFile = TempFile
    >> End Function
    >>
    >> --
    >> Regards,
    >> Tom Ogilvy
    >>
    >> "Pieter" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Yes want just attach it instead of reading it.
    >> > i am not good at vba..
    >> >
    >> >
    >> >
    >> > "Pieter" wrote:
    >> >
    >> > > Hello Readers,
    >> > >
    >> > > I use the following working code (thanks to ron de bruin) :
    >> > >
    >> > > Sub Mail_Loadingorder()
    >> > > Dim OutApp As Object
    >> > > Dim OutMail As Object
    >> > > Application.ScreenUpdating = False
    >> > > Set OutApp = CreateObject("Outlook.Application")
    >> > > Set OutMail = OutApp.CreateItem(0)
    >> > > With OutMail
    >> > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    >> > > .CC = ""
    >> > > .BCC = ""
    >> > > .Subject = "Loadingorder " &

    >> Sheets("Loadingorder").Range("h2").Value
    >> > > .HTMLBody = SheetToHTML(ActiveSheet)
    >> > > .Send 'or use .Display
    >> > > End With
    >> > > Application.ScreenUpdating = True
    >> > > Set OutMail = Nothing
    >> > > Set OutApp = Nothing
    >> > > End Sub
    >> > >
    >> > >
    >> > > Now i want to change this to attach the sheet as an html attachment

    >> instead
    >> > > of the
    >> > > the sheet is standing in the body.
    >> > >
    >> > > Anyone know this solution..
    >> > >
    >> > > Greetings
    >> > >

    >>
    >>
    >>




  8. #8
    Ron de Bruin
    Guest

    Re: send sheet to email as html attachment

    Try this untested example

    Sub Mail_ActiveSheet_HTM_File()
    'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim wb As Workbook
    Dim strdate As String

    strdate = Format(Now, "dd-mm-yy h-mm-ss")
    Application.ScreenUpdating = False

    ActiveSheet.Copy
    Set wb = ActiveWorkbook
    wb.Sheets(1).DrawingObjects.Visible = True
    wb.Sheets(1).DrawingObjects.Delete

    With wb
    .SaveAs "Part of " & ThisWorkbook.Name _
    & " " & strdate & ".htm", FileFormat:=xlHtml

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = "Hi there"
    .Attachments.Add wb.FullName
    'You can add other files also like this
    '.Attachments.Add ("C:\test.txt")
    .display 'or use .Display
    End With

    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close False
    End With

    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub


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


    "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
    > Hi Pieter
    >
    > I promise to add a example on my site this evening
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Pieter" <[email protected]> wrote in message news:[email protected]...
    >> Thanks for your time but does not work...
    >>
    >>
    >> "Tom Ogilvy" wrote:
    >>
    >>> Untested, but this should be along the lines of what you want:
    >>>
    >>> Sub Mail_ActiveSheet_Attach_as_HTML()
    >>> Dim OutApp As Outlook.Application
    >>> Dim OutMail As Outlook.MailItem
    >>> Dim sStr as String
    >>> Application.ScreenUpdating = False
    >>> Set OutApp = CreateObject("Outlook.Application")
    >>> Set OutMail = OutApp.CreateItem(olMailItem)
    >>> With OutMail
    >>> .To = "[email protected]"
    >>> .CC = ""
    >>> .BCC = ""
    >>> .Subject = "This is the Subject line"
    >>> sStr = SheetToHTMLFile(ActiveSheet)
    >>> .Attachments.Add sStr
    >>> .Send 'or use .Display
    >>> End With
    >>> Kill sSTr
    >>> Application.ScreenUpdating = True
    >>> Set OutMail = Nothing
    >>> Set OutApp = Nothing
    >>> End Sub
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> Public Function SheetToHTMLFile(sh As Worksheet)
    >>> 'Function from **** Kusleika his site
    >>> 'http://www.*****-clicks.com/excel/sheettohtml.htm
    >>> 'Changed by Ron de Bruin 04-Nov-2003
    >>> 'Modified to just save the file by TWOgilvy 10/24/2005
    >>> ' and pass back the fully qualified file name
    >>> Dim TempFile As String
    >>> Dim Nwb As Workbook
    >>> Dim myshape As Shape
    >>> 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 Nwb = Nothing
    >>> SheetToHTMLFile = TempFile
    >>> End Function
    >>>
    >>> --
    >>> Regards,
    >>> Tom Ogilvy
    >>>
    >>> "Pieter" <[email protected]> wrote in message
    >>> news:[email protected]...
    >>> > Yes want just attach it instead of reading it.
    >>> > i am not good at vba..
    >>> >
    >>> >
    >>> >
    >>> > "Pieter" wrote:
    >>> >
    >>> > > Hello Readers,
    >>> > >
    >>> > > I use the following working code (thanks to ron de bruin) :
    >>> > >
    >>> > > Sub Mail_Loadingorder()
    >>> > > Dim OutApp As Object
    >>> > > Dim OutMail As Object
    >>> > > Application.ScreenUpdating = False
    >>> > > Set OutApp = CreateObject("Outlook.Application")
    >>> > > Set OutMail = OutApp.CreateItem(0)
    >>> > > With OutMail
    >>> > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    >>> > > .CC = ""
    >>> > > .BCC = ""
    >>> > > .Subject = "Loadingorder " &
    >>> Sheets("Loadingorder").Range("h2").Value
    >>> > > .HTMLBody = SheetToHTML(ActiveSheet)
    >>> > > .Send 'or use .Display
    >>> > > End With
    >>> > > Application.ScreenUpdating = True
    >>> > > Set OutMail = Nothing
    >>> > > Set OutApp = Nothing
    >>> > > End Sub
    >>> > >
    >>> > >
    >>> > > Now i want to change this to attach the sheet as an html attachment
    >>> instead
    >>> > > of the
    >>> > > the sheet is standing in the body.
    >>> > >
    >>> > > Anyone know this solution..
    >>> > >
    >>> > > Greetings
    >>> > >
    >>>
    >>>
    >>>

    >
    >




  9. #9
    Tom Ogilvy
    Guest

    Re: send sheet to email as html attachment

    As you say, apparently you are not very good with VBA.

    In contrast to your failed attempt(s), the routine ran perfectly for me
    copied right out of the posting (changing only the email address - didnt'
    think Ron wanted a copy of the file <g>).

    It appears Ron wants to work with you, so I will leave you with him.
    --
    Regards,
    Tom Ogilvy


    "Pieter" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks for your time but does not work...
    >
    >
    > "Tom Ogilvy" wrote:
    >
    > > Untested, but this should be along the lines of what you want:
    > >
    > > Sub Mail_ActiveSheet_Attach_as_HTML()
    > > Dim OutApp As Outlook.Application
    > > Dim OutMail As Outlook.MailItem
    > > Dim sStr as String
    > > Application.ScreenUpdating = False
    > > Set OutApp = CreateObject("Outlook.Application")
    > > Set OutMail = OutApp.CreateItem(olMailItem)
    > > With OutMail
    > > .To = "[email protected]"
    > > .CC = ""
    > > .BCC = ""
    > > .Subject = "This is the Subject line"
    > > sStr = SheetToHTMLFile(ActiveSheet)
    > > .Attachments.Add sStr
    > > .Send 'or use .Display
    > > End With
    > > Kill sSTr
    > > Application.ScreenUpdating = True
    > > Set OutMail = Nothing
    > > Set OutApp = Nothing
    > > End Sub
    > >
    > >
    > >
    > >
    > >
    > >
    > > Public Function SheetToHTMLFile(sh As Worksheet)
    > > 'Function from **** Kusleika his site
    > > 'http://www.*****-clicks.com/excel/sheettohtml.htm
    > > 'Changed by Ron de Bruin 04-Nov-2003
    > > 'Modified to just save the file by TWOgilvy 10/24/2005
    > > ' and pass back the fully qualified file name
    > > Dim TempFile As String
    > > Dim Nwb As Workbook
    > > Dim myshape As Shape
    > > 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 Nwb = Nothing
    > > SheetToHTMLFile = TempFile
    > > End Function
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > > "Pieter" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Yes want just attach it instead of reading it.
    > > > i am not good at vba..
    > > >
    > > >
    > > >
    > > > "Pieter" wrote:
    > > >
    > > > > Hello Readers,
    > > > >
    > > > > I use the following working code (thanks to ron de bruin) :
    > > > >
    > > > > Sub Mail_Loadingorder()
    > > > > Dim OutApp As Object
    > > > > Dim OutMail As Object
    > > > > Application.ScreenUpdating = False
    > > > > Set OutApp = CreateObject("Outlook.Application")
    > > > > Set OutMail = OutApp.CreateItem(0)
    > > > > With OutMail
    > > > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    > > > > .CC = ""
    > > > > .BCC = ""
    > > > > .Subject = "Loadingorder " &

    > > Sheets("Loadingorder").Range("h2").Value
    > > > > .HTMLBody = SheetToHTML(ActiveSheet)
    > > > > .Send 'or use .Display
    > > > > End With
    > > > > Application.ScreenUpdating = True
    > > > > Set OutMail = Nothing
    > > > > Set OutApp = Nothing
    > > > > End Sub
    > > > >
    > > > >
    > > > > Now i want to change this to attach the sheet as an html attachment

    > > instead
    > > > > of the
    > > > > the sheet is standing in the body.
    > > > >
    > > > > Anyone know this solution..
    > > > >
    > > > > Greetings
    > > > >

    > >
    > >
    > >




  10. #10
    Ron de Bruin
    Guest

    Re: send sheet to email as html attachment

    Hi Tom

    I think the OP have not copy the changed function in the module

    >> think Ron wanted a copy of the file <g>).

    Not my address Tom<g>


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


    "Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
    > As you say, apparently you are not very good with VBA.
    >
    > In contrast to your failed attempt(s), the routine ran perfectly for me
    > copied right out of the posting (changing only the email address - didnt'
    > think Ron wanted a copy of the file <g>).
    >
    > It appears Ron wants to work with you, so I will leave you with him.
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Pieter" <[email protected]> wrote in message
    > news:[email protected]...
    >> Thanks for your time but does not work...
    >>
    >>
    >> "Tom Ogilvy" wrote:
    >>
    >> > Untested, but this should be along the lines of what you want:
    >> >
    >> > Sub Mail_ActiveSheet_Attach_as_HTML()
    >> > Dim OutApp As Outlook.Application
    >> > Dim OutMail As Outlook.MailItem
    >> > Dim sStr as String
    >> > Application.ScreenUpdating = False
    >> > Set OutApp = CreateObject("Outlook.Application")
    >> > Set OutMail = OutApp.CreateItem(olMailItem)
    >> > With OutMail
    >> > .To = "[email protected]"
    >> > .CC = ""
    >> > .BCC = ""
    >> > .Subject = "This is the Subject line"
    >> > sStr = SheetToHTMLFile(ActiveSheet)
    >> > .Attachments.Add sStr
    >> > .Send 'or use .Display
    >> > End With
    >> > Kill sSTr
    >> > Application.ScreenUpdating = True
    >> > Set OutMail = Nothing
    >> > Set OutApp = Nothing
    >> > End Sub
    >> >
    >> >
    >> >
    >> >
    >> >
    >> >
    >> > Public Function SheetToHTMLFile(sh As Worksheet)
    >> > 'Function from **** Kusleika his site
    >> > 'http://www.*****-clicks.com/excel/sheettohtml.htm
    >> > 'Changed by Ron de Bruin 04-Nov-2003
    >> > 'Modified to just save the file by TWOgilvy 10/24/2005
    >> > ' and pass back the fully qualified file name
    >> > Dim TempFile As String
    >> > Dim Nwb As Workbook
    >> > Dim myshape As Shape
    >> > 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 Nwb = Nothing
    >> > SheetToHTMLFile = TempFile
    >> > End Function
    >> >
    >> > --
    >> > Regards,
    >> > Tom Ogilvy
    >> >
    >> > "Pieter" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> > > Yes want just attach it instead of reading it.
    >> > > i am not good at vba..
    >> > >
    >> > >
    >> > >
    >> > > "Pieter" wrote:
    >> > >
    >> > > > Hello Readers,
    >> > > >
    >> > > > I use the following working code (thanks to ron de bruin) :
    >> > > >
    >> > > > Sub Mail_Loadingorder()
    >> > > > Dim OutApp As Object
    >> > > > Dim OutMail As Object
    >> > > > Application.ScreenUpdating = False
    >> > > > Set OutApp = CreateObject("Outlook.Application")
    >> > > > Set OutMail = OutApp.CreateItem(0)
    >> > > > With OutMail
    >> > > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    >> > > > .CC = ""
    >> > > > .BCC = ""
    >> > > > .Subject = "Loadingorder " &
    >> > Sheets("Loadingorder").Range("h2").Value
    >> > > > .HTMLBody = SheetToHTML(ActiveSheet)
    >> > > > .Send 'or use .Display
    >> > > > End With
    >> > > > Application.ScreenUpdating = True
    >> > > > Set OutMail = Nothing
    >> > > > Set OutApp = Nothing
    >> > > > End Sub
    >> > > >
    >> > > >
    >> > > > Now i want to change this to attach the sheet as an html attachment
    >> > instead
    >> > > > of the
    >> > > > the sheet is standing in the body.
    >> > > >
    >> > > > Anyone know this solution..
    >> > > >
    >> > > > Greetings
    >> > > >
    >> >
    >> >
    >> >

    >
    >




  11. #11
    Ron de Bruin
    Guest

    Re: send sheet to email as html attachment

    Add the basic macro with a on error for the drawingobjects lines on this page
    http://www.rondebruin.nl/mail/folder2/mail2.htm


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


    "Ron de Bruin" <[email protected]> wrote in message news:Ov4CG%[email protected]...
    > Try this untested example
    >
    > Sub Mail_ActiveSheet_HTM_File()
    > 'You must add a reference to the Microsoft outlook Library
    > Dim OutApp As Outlook.Application
    > Dim OutMail As Outlook.MailItem
    > Dim wb As Workbook
    > Dim strdate As String
    >
    > strdate = Format(Now, "dd-mm-yy h-mm-ss")
    > Application.ScreenUpdating = False
    >
    > ActiveSheet.Copy
    > Set wb = ActiveWorkbook
    > wb.Sheets(1).DrawingObjects.Visible = True
    > wb.Sheets(1).DrawingObjects.Delete
    >
    > With wb
    > .SaveAs "Part of " & ThisWorkbook.Name _
    > & " " & strdate & ".htm", FileFormat:=xlHtml
    >
    > Set OutApp = CreateObject("Outlook.Application")
    > Set OutMail = OutApp.CreateItem(olMailItem)
    > With OutMail
    > .To = "[email protected]"
    > .CC = ""
    > .BCC = ""
    > .Subject = "This is the Subject line"
    > .Body = "Hi there"
    > .Attachments.Add wb.FullName
    > 'You can add other files also like this
    > '.Attachments.Add ("C:\test.txt")
    > .display 'or use .Display
    > End With
    >
    > .ChangeFileAccess xlReadOnly
    > Kill .FullName
    > .Close False
    > End With
    >
    > Application.ScreenUpdating = True
    > Set OutMail = Nothing
    > Set OutApp = Nothing
    > End Sub
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
    >> Hi Pieter
    >>
    >> I promise to add a example on my site this evening
    >>
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Pieter" <[email protected]> wrote in message news:[email protected]...
    >>> Thanks for your time but does not work...
    >>>
    >>>
    >>> "Tom Ogilvy" wrote:
    >>>
    >>>> Untested, but this should be along the lines of what you want:
    >>>>
    >>>> Sub Mail_ActiveSheet_Attach_as_HTML()
    >>>> Dim OutApp As Outlook.Application
    >>>> Dim OutMail As Outlook.MailItem
    >>>> Dim sStr as String
    >>>> Application.ScreenUpdating = False
    >>>> Set OutApp = CreateObject("Outlook.Application")
    >>>> Set OutMail = OutApp.CreateItem(olMailItem)
    >>>> With OutMail
    >>>> .To = "[email protected]"
    >>>> .CC = ""
    >>>> .BCC = ""
    >>>> .Subject = "This is the Subject line"
    >>>> sStr = SheetToHTMLFile(ActiveSheet)
    >>>> .Attachments.Add sStr
    >>>> .Send 'or use .Display
    >>>> End With
    >>>> Kill sSTr
    >>>> Application.ScreenUpdating = True
    >>>> Set OutMail = Nothing
    >>>> Set OutApp = Nothing
    >>>> End Sub
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>> Public Function SheetToHTMLFile(sh As Worksheet)
    >>>> 'Function from **** Kusleika his site
    >>>> 'http://www.*****-clicks.com/excel/sheettohtml.htm
    >>>> 'Changed by Ron de Bruin 04-Nov-2003
    >>>> 'Modified to just save the file by TWOgilvy 10/24/2005
    >>>> ' and pass back the fully qualified file name
    >>>> Dim TempFile As String
    >>>> Dim Nwb As Workbook
    >>>> Dim myshape As Shape
    >>>> 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 Nwb = Nothing
    >>>> SheetToHTMLFile = TempFile
    >>>> End Function
    >>>>
    >>>> --
    >>>> Regards,
    >>>> Tom Ogilvy
    >>>>
    >>>> "Pieter" <[email protected]> wrote in message
    >>>> news:[email protected]...
    >>>> > Yes want just attach it instead of reading it.
    >>>> > i am not good at vba..
    >>>> >
    >>>> >
    >>>> >
    >>>> > "Pieter" wrote:
    >>>> >
    >>>> > > Hello Readers,
    >>>> > >
    >>>> > > I use the following working code (thanks to ron de bruin) :
    >>>> > >
    >>>> > > Sub Mail_Loadingorder()
    >>>> > > Dim OutApp As Object
    >>>> > > Dim OutMail As Object
    >>>> > > Application.ScreenUpdating = False
    >>>> > > Set OutApp = CreateObject("Outlook.Application")
    >>>> > > Set OutMail = OutApp.CreateItem(0)
    >>>> > > With OutMail
    >>>> > > .To = ThisWorkbook.Sheets("Loadingorder").Range("a1").Value
    >>>> > > .CC = ""
    >>>> > > .BCC = ""
    >>>> > > .Subject = "Loadingorder " &
    >>>> Sheets("Loadingorder").Range("h2").Value
    >>>> > > .HTMLBody = SheetToHTML(ActiveSheet)
    >>>> > > .Send 'or use .Display
    >>>> > > End With
    >>>> > > Application.ScreenUpdating = True
    >>>> > > Set OutMail = Nothing
    >>>> > > Set OutApp = Nothing
    >>>> > > End Sub
    >>>> > >
    >>>> > >
    >>>> > > Now i want to change this to attach the sheet as an html attachment
    >>>> instead
    >>>> > > of the
    >>>> > > the sheet is standing in the body.
    >>>> > >
    >>>> > > Anyone know this solution..
    >>>> > >
    >>>> > > Greetings
    >>>> > >
    >>>>
    >>>>
    >>>>

    >>
    >>

    >
    >




+ 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