VBA - How to add Text after Pasting an object from Excel to Outlook

Florida1510

New Member
Joined
Mar 13, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
Folks,

I need some assistance I'm trying to create a template where I will paste an excel range as an image to Outlook. Here is my issue I can get the image to paste however I would like to add three additional sections to my email below the imaged where I could provide items to my clients. Those sections being Billing, Delivery, and Maintenance. I would like spaces in-between each of these sections. For the life of me I can't seem to figure out the VBA code to do this. Below is my current code which creates my email and inserts my image. Any help would be appreciated. I'm very green to VBA coding but I feel I'm going to really enjoy it. Thanks in advance.

Sub CopyRangeToOutlook_single()
'Declare Outlook Variables
Dim oLookApp As Outlook.Application
Dim oLookItm As Outlook.MailItem
Dim oLookIns As Outlook.Inspector

'Declare Word Variables
Dim oWrdDoc As Word.Document
Dim oWrdRng As Word.Range

'Delcare Excel Variables
Dim ExcRng As Range

On Error Resume Next

'Get the Active instance of Outlook if there is one
Set oLookApp = GetObject(, "Outlook.Application")

'If Outlook isn't open then create a new instance of Outlook
If Err.Number = 429 Then

'Clear Error
Err.Clear

'Create a new instance of Outlook
Set oLookApp = New Outlook.Application

End If

'Create a new email
Set oLookItm = oLookApp.CreateItem(olMailItem)


'Create an array to hold ranges
Set ExcRng = Sheet1.Range("A1:D24")

With oLookItm

'Define some basic info of our email
.To = "xy1542@tyu.com"
.CC = "xy1542@tyu.com"
.Subject = "Here are all of my Ranges"
.Body = "Here are all the Ranges from my worksheet."

'Display the email
.Display

'Get the Active Inspector
Set oLookIns = .GetInspector

'Get the document within the inspector
Set oWrdDoc = oLookIns.WordEditor


ExcRng.Copy

'Define the range, insert a blank line, collapse the selection.
Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
oWrdRng.Collapse Direction:=wdCollapseEnd

'Add a new paragragp and then a break
Set oWrdRng = oWdEditor.Paragraphs.Add
oWrdRng.InsertBreak

'Paste the object.
oWrdRng.PasteSpecial DataType:=wdPasteMetafilePicture


End With


End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top