[VBA/Macro/ How do I copy a range of cells and then paste that data in the CURRENT e-mail you are in on Outlook?

[booleanboolean]

Hi fellow coders,

I currently have a Macro that copies a specific range of cells and, with a click of a button (titled: Send E-mail), opens Microsoft Outlook with a new e-mail message with the information pasted in the body.

This is what I want to do:

1) In Microsoft Outlook, search for whatever e-mail thread I find, click "Reply/Reply All" which then gives me a new e-mail (reply thread) for me to paste my data in.

2) Press a Macro Button on Excel and have that macro copy my desired range into the above (current e-mail I am replying to.)

Can this be done? I have created two macros that a) copies data from excel, click macro button, opens Outlook with a a brand new e-mail and b) I created a simple "Copy" Macro button that copies the selected range to a clipboard, which then I go to my respective e-mail thread on Outlook and paste the data in there.

Please, any help would be appreciated. I'm almost done with this project and it would be SO beneficial to have it work the way I want to . Please reply with any advice or if you need anything from me.

Cheers!

 

[John_w]

This code copies and pastes specific Excel cells (or the currently selected cells) into the email you're currently replying to. Note the email must be in a Pop Out window; the code won't work if you're replying to the email in the reading pane.

Public Sub Copy_and_Paste_Cells_To_Email_Body_LB()

    Dim OutApp As Object 'Outlook.Application
    Dim wDocument As Object 'Word.Document
    Dim wSelection As Object 'Word.Selection
        
    Set OutApp = CreateObject("Outlook.Application")
    If OutApp.ActiveInspector Is Nothing Then
        MsgBox "An email is not being edited in Outlook"
        Exit Sub
    End If
    
    'Copy specific Excel cells to the Clipboard
    
    Worksheets("Sheet1").Range("M7").CurrentRegion.Copy
    
    'Or currently selected cells
    
    'Selection.Copy
    
    Set wDocument = OutApp.ActiveInspector.WordEditor
    Set wSelection = wDocument.Windows(1).Selection
    
    'Paste Clipboard in email body
    
    wDocument.Application.Selection.Paste
    
    'Insert extra text
    
    wSelection.TypeText vbNewLine & "Excel cells are shown above"
    
    Application.CutCopyMode = False
    
End Sub

 

[booleanboolean]

Hi!

This currently works, however after changing the Sheet Name and Range, it still happens to paste the ENTIRE spreadsheet in the body of the e-mail, where I would only like it to copy from B1:J2.

This is great - if you could just help with this final touch, I'd be so grateful!

 

[booleanboolean]

PLease help!!!

 

[John_w]

To copy A1:E6 on Sheet1:

    Worksheets("Sheet1").Range("A1:E6").Copy

 

[booleanboolean]

To copy A1:E6 on Sheet1:

    Worksheets("Sheet1").Range("A1:E6").Copy

I guess what I’m trying to say is that the code you provided me works - I can open up and email I want to reply from, run your macro, and have it paste into the e-mail on Outlook.

The problem is that even though I changed the parameters of the range of cells it’s copying and pasting, it seems to be pasting ALL of the cells on my spreadsheet into the email and not the specific cells that I want.

Could you please help out with this John? I can’t seem to figure out WHY it isn’t pasting ONLY what I need into the body of the email when I’ve changed the range and inputted the respective sheet I am in. Everything else works flawlessly.

 

[John_w]

I'm unable to reproduce your problem. Running the macro (Excel 2016) always copies and pastes the exact cells specified in the Copy line, and I can't see why it would copy all the cells, as that is the only line which is copying a range of cells.

 

[booleanboolean]

Public Sub Copy_and_Paste_Cells_To_Email_Body_LB()


Dim OutApp As Object
Dim wDocument As Object
Dim wSelection As Object

Set OutApp = CreateObject("Outlook.Application")
If OutApp.ActiveInspector Is Nothing Then
MsgBox "An email is not being edited in Outlook"
Exit Sub
End If

Worksheets("Call Log").Range("B1:J2").CurrentRegion.Copy


Set wDocument = OutApp.ActiveInspector.WordEditor
Set wSelection = wDocument.Windows(1).Selection

wDocument.Application.Selection.Paste



wSelection.TypeText vbNewLine & ""

Application.CutCopyMode = False

End Sub

This is what I currently have, copied from you yesterday. In Worksheets, it has my Proper sheet name and the range is from B1:J2.

Is there any way to copy selected cells instead?

 

[John_w]

Look closely at my modified Copy line - you haven't changed the code correctly.

Yours should be:

Worksheets("Call Log").Range("B1:J2").Copy

Please use CODE tags when posting VBA code - click the # icon in the message editor.

 

[booleanboolean]

Thank you very much! That definitely works now. After thinking about what I am doing here, I believe it would be more beneficial if there was a way to perform the same function as your code, but instead of selecting a range of cells, is there any way to tell the macro to either: 1) Copy Cells you have manually selected yourself, or B) Copy the most recent cells entered ?

I ask this because every time I update my Call Log, I will have to send that specific information in the e-mail, not just the specified range. Is there any way to perform this John?

Thank you for all of your help so far.