Excel - Send sheet & use range as body

[hmltnangel]

Ok folks,

My VBA isnt the greatest but I can normally piece together most bits and make it work

This however has me scratching my head.

I want to click a button and have the following happen

Sheet 1 - gets sent on an email as an attachment, a range from Sheet 2 will be the body of the email.

I so far mad ethis - which fails

Sub Mail()
    Dim OutlookApp As Object
    Dim Mess As Object, Recip As String
    ActiveSheet.Copy
        ActiveWorkbook.SaveAs Range("C6").Value
    Recip = [F28].Value
    Set Sendrng = Worksheets("Sheet2").Range("B1:B10")
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = [C6]
        .Body = [Sendrng]
        .Recipients.Add Recip
        .Send
    End With
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
End Sub

Any help to finish it off?

[hmltnangel]

Right, got a bit further now ..... I had a few things in the wrong order.

Now its stuck on the "Body" Line

Sub Mail()
    Dim OutlookApp As Object
    Dim Mess As Object, Recip As String
    Recip = [F28].Value
    Set Sendrng = Worksheets("Sheet2").Range("B1:B10")
    ActiveSheet.Copy
        ActiveWorkbook.SaveAs Range("C6").Value
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = [C6]
        .Body = Sendrng
        .Recipients.Add Recip
        .Send
    End With
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
End Sub

[snb]

This might help:

http://www.snb-vba.eu/VBA_Excelgegev...en_en.html#L27