Thread: Pasting Range from worksheet into Body of Email Message

I want it to take a set range on a specified worksheet and paste it into the body of an email and send it off. Then close the workbook without saving. I have some code that works, but only about 40% of the time. I've tried it on Excel 2007 & 2010 and cannot consistantly get the range to paste. Any assistance would be greatly appreciated. Here is the code:

Sub EmailRange()
     
    Dim MailSelection As Object
    Dim cell As Range
    Dim Subject As String
    Dim EmailAddress As String
     
     
     
    ThisWorkbook.Sheets("BI Request").Range("B2:C18").Copy
     
     
     
    Set OutlookApp = CreateObject("Outlook.Application")
     
  
        
            Subject = "BI Request"
            EmailAddress = "Stephen_Vaaler@shamrockfoods.com"
             
            Set MailSelection = OutlookApp.CreateItem(0)
            With MailSelection
                .To = EmailAddress
                .Subject = Subject
                .Display
                SendKeys "^({v})", True
            End With
            With MailSelection
                .Send
            End With
            
    ThisWorkbook.Close Saved = True
       

End Sub

Hi AZGryphon
Please wrap your code in code tags. To edit your post, press Edit Post, press Go Advanced to see the [#] button). Highlight your code then press the [#] button. Press Save Changes.
Then, see this link http://www.rondebruin.nl/mail/folder3/mail4.htm
I've used this code many times and it works wonderfully well. If you need help adapting the code, let me know.

Hi AZGryphon
You can do a couple of things.
You can unprotect the sheet (with code), allow the procedure to run, then protect the sheet (with code).
Or, you can place this kind of code in the Workbook Module such that it'll allow macros to run on a protected sheet.

Option Explicit

Private Sub Workbook_Open()
Dim wSht As Worksheet
Dim PW As String
 
 PW = "password" 
 'set protection using UserInterface to allow macros to work
  For Each wSht In ActiveWorkbook.Sheets
        wSht.Protect _
            Password:=PW, _
            DrawingObjects:=True, _
            Contents:=True, _
            Scenarios:=True, _
            UserInterfaceOnly:=True
         wSt.EnableSelection = xlUnlockedCells	
    Next wSht
End Sub

If you decide to use the above code and don't use password protection, remove the lines that refer to password protection. You'll need to save and close the workbook then reopen to allow the macro be operational.

Hi AZGryphon

You're welcome...glad I could be of help...and thanks for the Rep.