How to insert signature into Outlook email when sending by vba in Excel?
When sending email by running VBA code in Excel, the Outlook signature will not be displayed in the email body automatically. This article is talking about sending email with Outlook signature displaying in the email body by VBA code in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Supposing you need to send emails to a list of email addresses include the Outlook signature in all sending emails. Please do as follows.
1. Please replace the .Body line in below VBA 2 with the code in VBA 1. And in the VBA 2, move the .Display line up until it locates below 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 to easily find the differences after changing the VBA code.
After running the VBA code, you can see the default Outlook signature is inserted into the created emails’ body automatically as below screenshot shown.
- How to send email to email addresses specified in cells in Excel?
- How to send email with copying and pasting a specified range into email body in Excel?
- How to send email with multiple attachments attached in Excel?
- How to send email if due date has been met in Excel?
- How to automatically send email based on cell value in Excel?
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 27 days agoThanks a lot...
To post as a guest, your comment is unpublished.· 5 months agoSuperb!!!!
To post as a guest, your comment is unpublished.· 8 months agoI'm having trouble running this on excel 2016. I get a "Compile Error: User Defined Type Not Defined" message. Please help!
To post as a guest, your comment is unpublished.· 8 months agoIf my body text is linked to pull from excel fields, the use of & .HTMLBody at the end of the string erases all of the body text and just leaves the signature.
To post as a guest, your comment is unpublished.· 11 months agohow to add signature if the macro is used by multiple user.
for eg my macro will be run by 3 other persons as well. So how can the macro use the signature of the user who is running the macro.
thanks in advance