Insert an object in Outlook Created from File and Display as Icon from Excel using VBA

← PrevNext →

You can insert a variety of objects like Excel workbook, PDF or Word document, images etc., in your email message using Outlook (any version), by clicking on various tabs, buttons and options. Or, you can automate this entire process. I’ll show you how you can do this dynamically with the click of a button from Excel using VBA.

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.

Select Rich Text format in Outlook

3) Next, click the Insert tab from the top menu. Find the Object option and click it.

Object option inside Insert tab in Ms-Outlook

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.

Create an in Ms-Outlook using file and Display as Icon using VBA

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 = ","    ' 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.

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, _
           End If
       End If
End Sub

Here's the output. 👇 This is how it will look.

Insert object in Outlook created from file and Display as Icon using VBA

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.

← PreviousNext →

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.
Delivered by FeedBurner