Insert an object in Outlook created from File and display as Icon from Excel using VBA

Please see this image to understand what I mean.

Manually insert or embed object in Outlook from Excel

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" tab 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.

Note: If you are still using Outlook 2007, then click the "Option" tab (instead of Format Text tab).

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 = "arunbanik@rediffmail.com, arun@hotmail.com"    ' 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. 👇

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. Happy coding. ☺

← PreviousNext →