Range to email with text

[rafal]

Hi,

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
    R.Select
    Selection.Copy
  
    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"

emailItem.display

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)
            emailItem.display
          
'
End Sub

Thanks a lot,
Rafal

 

[rafal]

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

Thanks
Raf

 

[petergroft]

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,
Peter

 

[rafal]

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.

Rafal