Need Help with Macro extracting data to Email

Hi Guys

I have a macro running that will detach a current worksheet from a workbook and attach it to an email. What im looking to do is use the same code to extract a specific cell range within that worksheet to an email attachment instead, anyone have any ideas?

VBA Code... Any help would be much appreciated guys!

Sub EmailNewBuildProject()
     'Variable declaration
    Dim oApp As Object, _
    oMail As Object, _
    WB As Workbook, _
    FileName As String
     
     'Turn off screen updating
    Application.ScreenUpdating = False
     
     'Make a copy of the active sheet and save it to
     'a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook
    FileName = "New Build Project.xls"
    On Error Resume Next
    Kill "H:\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:="H:\" & FileName
     
     'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
         'Uncomment the line below to hard code a recipient
         '.To = "[email protected]"
         'Uncomment the line below to hard code a subject
         .Subject = "New Build Project Template"
        .Attachments.Add WB.FullName
        .Display
    End With
     
     'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
     
     'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Have you looked at Ron de Bruin's site? He covers pretty much everything.

Note that ...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

This page has a variety of examples for sending a range using SendMail, Outlook, whatever.

http://www.rondebruin.nl/sendmail.htm


Regards, TMS