How to insert Outlook signature when sending email in Excel?
Supposing you want to send an email directly in Excel, how can you add the default Outlook signature in thie email? This article provides two methods to help you adding Outlook signature when sending email in Excel.
For example, there is a list of email address in a worksheet, and you need to email to all these addresses in Excel and include the default Outlook signature in all emails. Please apply the below VBA code to achieve it.
1. Open the worksheet contains the email address list you want to email to, and then press the Alt + F11 keys.
2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA 2 into the Module code window.
3. Now you need to replace the .Body line in VBA 2 with the code in VBA 1. After that, cut the .Display line and then paste it under the With xMailOut line.
VBA 1: Template of send email with signature in Excel
.HTMLBody = "This is a test email sending in Excel" & "<br>" & .HTMLBody
VBA 2: Send email to email addresses specified in cells in Excel
Sub SendEmailToAddressInCells() Dim xRg As Range Dim xRgEach As Range Dim xRgVal As String Dim xAddress As String Dim xOutApp As Outlook.Application Dim xMailOut As Outlook.MailItem On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select email address range", "KuTools For Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub Application.ScreenUpdating = False Set xOutApp = CreateObject("Outlook.Application") Set xRg = xRg.SpecialCells(xlCellTypeConstants, xlTextValues) For Each xRgEach In xRg xRgVal = xRgEach.Value If xRgVal Like "?*@?*.?*" Then Set xMailOut = xOutApp.CreateItem(olMailItem) With xMailOut .To = xRgVal .Subject = "Test" .Body = "Dear " _ & vbNewLine & vbNewLine & _ "This is a test email " & _ "sending in Excel" .Display '.Send End With End If Next Set xMailOut = Nothing Set xOutApp = Nothing Application.ScreenUpdating = True End Sub
The following screenshot can help you easily find the differences after changing the VBA code.
4. Press the F5 key to run the code. Then a Kutools for Excel select box pops up, please select the email addresses you will sent emails to, and then click OK.
Then emails sending to specific addresses are created and displayed. You can see the default Outlook signature is added at the end of the email body.
- 1. You can change the email body in VBA code 1 based on your needs.
- 2. After running the code, if an error dialog box pops up warning that the User-defined type not defined, please close this dialog, and then go to click Tools > References in the Microsoft Visual Basic for Applications window. In the opening References – VBAProject window, check the Microsoft Outlook Object Library box and click OK. And then run the code again.
If you are a newbie in VBA, here highly recommend the Send Emails utility of Kutools for Excel for you. With this feature, you can easily send emails based on certain fields in Excel and add Outlook signature to them. Please do as follows.
Before applying Kutools for Excel, please download and install it firstly.
Firstly, you need to create a mailing list with different fields you will send emails based on.
You can manually create a mailing list as you need or apply the Create Mailing List feature to quickly get it done.
1. Click Kutools Plus > Create Mailing List.
2. In the Create Mailing List dialog box, specify the fields you need, choose where to output the list, and then click the OK button.
3. Now a mailing list sample is created. As it is a sample list, you need to change the fields to certain needed content. (multiple rows are allowed)
4. After that, select the whole list (include headers), click Kutools Plus > Send Emails.
5. In the Send Emails dialog box:
- 5.1) Items in the selected mailing list are placed in corresponding fields automatically;
- 5.2) Finish the email body;
- 5.3) Check both the Send email via Outlook and Use Outlook’s signature settings boxes;
- 5.4) Click the Send button. See screenshot:
Now emails are sent. And the default Outlook signature is added at the end of the email body.