Inserting an Object using VBA

[Tbomb65]

I'm going crazy.

I feel like what I am trying to do is very simple but I cant work out how to do it nor find anywhere that specifically gives me my answers.

I am trying to add into the body of an already open email (Be it a reply or a new email) at whichever point the cursor currently is an "object" using VBA.

Effectively I want to automate doing the Insert > Object > Create from file commands and specify a file path so that I can create a button that allows a one click solution.

I started investigating using templates but that only seemed to work well when creating a brand new email and didn't appear to be able to be done multiple times in the same email (in the case where 2 or 3 templates needed to be used).

As there is no ability to record in Outlook I cant replicate the steps without writing the code, which outside of excel I have no idea how to do.

I have this code which im trying to modify to be relevent but im not getting anywhere


Sub CreateFromTemplateCPDR()


Dim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItemFromTemplate("C:\Users\File.xls")


myItem.Display

End Sub

[gmayor]

It sounds like you want to insert an Excel (xls format) object at the cursor of an e-mail message. To do that you need to take control of the WordEditor inspector related to the message and then the vba is pretty much the same as it would be for Word itself, and you can probably use the Word VBA editor to record a similar process to adopt. The main issue is that you cannot use Word specific commands e.g. those beginning 'wd'. For those you need to use the numeric equivalents and you must define Word variables as Objects, as in the case of Dim oRng.


Sub InsertAnObject()
Dim oRng As Object
Const strFilename As String = "C:\Users\File.xls" 'The Excel file to be inserted

On Error GoTo ErrHandler
If TypeName(ActiveWindow) = "Inspector" Then
'ensure that the cursor is in the body of the message
If ActiveInspector.IsWordMail And ActiveInspector.EditorType = olEditorWord Then
'Set a range to the cursor position
Set oRng = ActiveInspector.WordEditor.Application.Selection
'Insert the file at the range
oRng.InlineShapes.AddOLEObject _
ClassType:="Excel.Sheet.8", _
fileName:=strFilename, _
LinkToFile:=False, _
DisplayAsIcon:=False
End If
End If
lbl_Exit:
Exit Sub
ErrHandler:
Beep
Resume lbl_Exit
End Sub

[Tbomb65]

You're a genius. Thank you so much it works perfectly. I also definitely would not have come to that conclusion by myself!