Typically, this is what you will do to embed an object in Outlook (manually).
1) Open Ms-Outlook. Click the New Email tab (New in Outlook 2007) to create a new email message.
2) Click Format Text (or Options if you are using Office 2007) from the top menu and click the Rich Text button. Since we want to add an Excel object, we have to change the format from default, which is HTML to Rich Text.
3) Next, click the Insert tab from the top menu. Find the Object option and click it.
4) It will open an Object dialog box. Click the Create from File tab.
5) Browse the file you want to insert, such as an Excel file. Choose the Display as icon check box and click OK.
Done! Now add To email id, a Subject line and Send the email.
Macro to Insert an Object from File in Outlook
Now, I’ll show you how you can automate the entire process that I have explained above, using VBA in Excel.
Let us assume, you have file name Books.xlsx in the D:\ drive.
Here’s the macro.
Option Explicit Private Sub CommandButton1_Click() On Error GoTo ErrHandler ' Set Outlook object. Dim objOutlook As Object Set objOutlook = CreateObject("Outlook.Application") ' Create email object. Dim objEmail As Object Set objEmail = objOutlook.CreateItem(olMailItem) With objEmail .To = "email@example.com, firstname.lastname@example.org" ' Assign email ids to the property. .Subject = "This is a text message from Arun" .BodyFormat = olFormatRichText ' Change the body format to Rich Text. (the default is HTML) .Body = "Hi, there. Hope you are doing well." .Display ' Open outlook for display. (Replace Display with Send, when you want to send the email.) End With ' Clear all objects. Set objEmail = Nothing: Set objOutlook = Nothing ' Once the emailing paramters are set and outlook is ready, ' we'll insert or embed an object to the mail message. insertExcelObject ErrHandler: ' End Sub ' Insert an Excel file as object. Sub insertExcelObject() Const sFileName As String = "d:\Books.xlsx" ' The file that we'll insert as object. Dim objRng As Object If TypeName(ActiveWindow) = "Window" Then If ActiveInspector.IsWordMail And ActiveInspector.EditorType = olEditorWord Then Set objRng = ActiveInspector.WordEditor.Application.Selection objRng.InlineShapes.AddOLEObject _ ClassType:="Excel.Sheet", _ DisplayAsIcon:=True, _ IconFileName:="EXCEL.EXE", _ IconLabel:=sFileName, _ fileName:=sFileName End If End If End Sub
Here's the output. 👇 This is how it will look.
Note: 👉 For more on how to send email from Excel using VBA, you can refer this article.
What this macro is doing?
Few things that you need to understand from the above macro. I am creating two objects inside the button’s click event. The first is an Outlook object (objOutlook) and second is the email object (objEmail).
Using the email object objEmail, I have assigned few values like To, Subject etc. and most importantly the value to the BodyFormat property.
.BodyFormat = olFormatRichText
The default format of the Body (of a message) is HTML. I am changing the format to Rich Text. Or else, I may not able to embed or insert an Object into email message.
Now, let’s understand the insertExcelObect procedure. This is where I am actually embedding an object to the Outlook message, created from a file, but displaying as icon. Therefore, I have created an OLE object of type InlineShapes.
Dim objRng As Object Set objRng = ActiveInspector.WordEditor.Application.Selection objRng.InlineShapes.AddOLEObject _
The InlineShapes.AddOLEOject method takes few parameters like,
a) ClassType: Its an Excel file that I am inserting. Therefore, the type is Excel.Sheet.
b) DisplayAsIcon: Yes, we want to display an Excel icon in the message. Hence, its True.
c) IconFileName: Here we’ll assign the application (or an exe) that contains the icon we want to show. Since its an Excel file, I have assigned EXCEL.EXE.
Note: if you want the Word icon, assign WORD.EXE.
d) IconLabel: I want to add a label to icon for better understanding. Therefore, I have assigned the filename itself.
e) fileName: Finally, the file name.
That's it. Thanks for reading. ☺