• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Range to email with text


New Member

I wonder if anyone could help me with tweaking some code for sending an email with range and text.

I'm nearly there but got stuck at one thing which hopefully will be an easy thing for more experienced users.

I'm still in a "don't know what I'm doing" phase but gone through numerous tutorials and managed to put together a code that kinda does the thing that I want but not in the right order.

The code below selects and copies a range (dynamic - spitted out into a sheet by another piece of code - thus the formula), creates an email, put all the greetings and question first but pastes the range below the signature.

Could you suggest the change so it pastes the range between the question and the signature please?

I appreciate any suggestions.

Here's the code:

>>> use code - tags <<<
Sub EmailGRE05()
' EmailGRE05 Macro

    Dim R As Range
    Set R = Range("A1").CurrentRegion
    Dim emailApplication As Object
    Dim emailItem As Object
    Dim xInspect As Object
    Dim pageEditor As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

emailItem.to = "email@company.com"

emailItem.Subject = "Transfer Prices for Europe (ICP3)"

emailItem.Body = "Hi," & vbNewLine & vbNewLine & "Could you advise on some prices for Europe please?" & vbNewLine & vbNewLine & "Thanks," & vbNewLine & "Rafal"


Set xInspect = emailItem.GetInspector
Set pageEditor = xInspect.WordEditor

            pageEditor.Application.Selection.Start = Len(emailItem.Body)
            pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
            pageEditor.Application.Selection.PasteAndFormat (wdFormatRichText)
End Sub
Thanks a lot,
Last edited by a moderator:


New Member
Hello, is there anyone who'd give it a quick look please, or point me in the right direction? Appreciate any form of help.



New Member
1. In the worksheet containing the range you need to copy, press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the opening Microsoft Visual Basic for Applications window, please click Tools > References as the below screenshot shows.

3. In the References – VBAProject dialog box, please find and check the Microsoft Outlook Object Library option, and then click the OK button.

4. Click Insert > Module, then copy and paste the below VBA code into the Module window.

5. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, please select the range you need to paste in the email body, and then click the OK button.

6. Now an email is created with a specified recipient, subject, body, and selected Excel range, please click the Send button to send this email.

This May Help,


New Member
Thanks a lot for your reply and help Peter :)

I've added up Microsoft Outlook Object Library to the References and inserted the module but which code to paste do you mean? Mine or is there something that didn't come across? I'm having a trouble with finding Kutools too :/.

The other question - if anyone else would like to use my file with the script, would they need to add the references on their computers too?

Thanks a lot again.