Send an excelsheet using vba

[aman1234]

Hi guys

I am writing the following code to send excelsheet through email but it doesn't work.

 ReportDateAndTime = Now
Call Mail_Selection_Range_Outlook_Body("Report")

Sub Mail_Selection_Range_Outlook_Body(a As String)

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
   
    Set rng = ThisWorkbook.Worksheets("BaNCS Cheque Report").UsedRange
    
    On Error GoTo 0

    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olMailItem)

    On Error Resume Next
    With OutMail
     
               .Recipients.Add "amanpreet.kaur@axa-sunlife.co.uk"
                .Recipients.Add "mark.bickers@williamslea.com"
             
                
        .Subject = a
        .HTMLBody = RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

[Pepe Le Mokko]

Perhaps you will want to have a look at Ron de Bruin's solution
The mail add-in he built is very easy to use, and the code is available if needed

[nathansav]

You need to copy the function rangetoHTML also.

[aman1234]

I copied the following code but still doesn't send an email.

Function RangetoHTML(rng As Range)
    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"
    
    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

[aman1234]

The above code works fine for excel 2003 but why not for excel 2007.

Please help me out in this.

Thanks

[moonsaga]

maybe try this..

Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = "name@whatever.com"  '(email address goes here seperated by a ;)
	.CC = "name@whatever.com"  '(email address goes here seperated by a ;)
	.BCC = "name@whatever.com"  '(email address goes here seperated by a ;)
	.Subject = "Hi my name is." '(subject goes here)
        .Body = "See Attached"
        '.Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        .Attachments.Add ("C:\Documents and Settings\Desktop\Filename.pdf") 'Input path to file.
        ' In place of the following statement, you can use ".Display" to
        ' display the Email.
        .Send   
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub