How To Add Default Signature In Outlook Using Excel Vba

Adding Default Signature in Outlook using Excel VBA

Summary:

In this guide, we will explain how to add a default signature in Outlook using Excel VBA with detailed examples.

Prerequisites:

• Microsoft Outlook installed on your system.
• Microsoft Excel installed on your system.
• Basic understanding of Excel VBA programming.

Step-by-Step Instructions:

1. Open Microsoft Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module by clicking on “Insert” > “Module”.
3. In the module window, write the following VBA code:

Sub AddDefaultSignature()
    Dim objOL As Object
    Dim objMail As Object
    Dim strSignature As String
    
    ' Create a new Outlook instance
    Set objOL = CreateObject("Outlook.Application")
    
    ' Access the current user's default signature
    strSignature = objOL.DefaultProfile.GetDefaultFolder(5).Items(1).HTMLBody
    
    ' Create a new mail item
    Set objMail = objOL.CreateItem(0)
    
    ' Set the signature to the default signature
    objMail.HTMLBody = strSignature
    
    ' Display the mail item
    objMail.Display
    
    ' Clean up the objects
    Set objOL = Nothing
    Set objMail = Nothing
End Sub

4. Save the VBA code and close the VBA editor.
5. Go back to the Excel workbook and press Alt + F8 to open the “Macro” dialog box.
6. Select the “AddDefaultSignature” macro and click “Run” to execute it.
7. A new Outlook email will be created with the default signature pre-filled in the body.

Explanation:

The VBA code above uses the Outlook.Application object to create a new instance of Outlook. It then accesses the current user’s default signature by using the GetDefaultFolder method with parameter value 5 (which represents the “Signature” folder). The HTMLBody property is used to retrieve the HTML content of the first item in the folder, which is the default signature. Next, a new mail item is created using the CreateItem method with parameter value 0 (which represents a “MailItem” type). The HTMLBody property of the new mail item is set to the retrieved default signature. Finally, the mail item is displayed using the Display method.

Example:

Let’s say your default signature in Outlook contains the following content:

<p>Best regards,</p>
<p>John Doe</p>

When you run the VBA macro provided above, a new Outlook email will be generated with the following content in the body:

<p>Best regards,</p>
<p>John Doe</p>