Results 1 to 7 of 7

Sending emails with objects

Threaded View

  1. #1
    Registered User
    Join Date
    10-22-2009
    Location
    amsterdam, netherlands
    MS-Off Ver
    Excel 2003
    Posts
    31

    Sending emails with objects

    Hi all,

    I have a macro that will send all the data on a sheet be part of the email in outlook (it doesn't attach the sheet). The only issue is that i have a few checkboxes and one image and when i copy the sheet it doesn't copy any of them.Here is the code that i'm using

    
    Sub Mail_Sheet_Outlook_Body()
    ' Working in Office 2000-2007
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
     
        Set rng = Nothing
        Set rng = ActiveSheet.UsedRange
        'You can also use a sheet name
        'Set rng = Sheets("YourSheet").UsedRange
    
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)
            
        On Error Resume Next
        With OutMail
            .To = 
            .CC = 
            .BCC = ""
            .Subject = 
            .HTMLBody = RangetoHTML(rng)
            .Display
        End With
        On Error GoTo 0
     
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
     
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    
    Function RangetoHTML(rng As Range)
    ' Working in Office 2000-2007
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
     
        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
     
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
     
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
     
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
     
        'Close TempWB
        TempWB.Close savechanges:=False
     
        'Delete the htm file we used in this function
        Kill TempFile
     
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    Is there anything i can do to include the checkboxes in the mail.

    Thanks
    Last edited by DonkeyOte; 02-08-2010 at 12:19 PM. Reason: CODE tags not QUOTE tags - thanks

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