Florida1510
New Member
- Joined
- Mar 13, 2020
- Messages
- 35
- Office Version
- 2010
- Platform
- 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
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