Supposing you need to send email through Outlook by clicking a button in Excel worksheet, how can you do? This article will introduce a VBA method to achieve it in details.
Send email if button is clicked with VBA code
Please do as follows to send an email through Outlook if a Command Button is clicked in Excel workbook.
1. Insert a Command Button in your worksheet by clicking Developer > Insert > Command Button (ActiveX Control). See screenshot:
2. Right-click the inserted Command Button, then click View Code from the right-clicking menu as below screenshot show.
3. In the opening Microsoft Visual Basic for Applications window, please replace the original code in the Code window with the following VBA script.
VBA code: Send email if button is clicked in Excel
Private Sub CommandButton1_Click()
'Updated by Extendoffice 2017/9/14
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Body content" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Email Address"
.CC = ""
.BCC = ""
.Subject = "Test email send by button clicking"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End SubNotes:
1). Please change the email body as you need in the xMailBody line in the code.
2). Replace the Email Address with the recipient email address in line .To = "Email Address".
3). Specify the Cc and Bcc recipients as you need in .CC = “” and .Bcc = “” sections.
4). Change the email subject in line .Subject = "Test email send by button clicking".
4. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
5. Turn off the Design Mode by clicking Developer > Design Mode. See screenshot:
From now on, every time you click on the Command Button, an email will be created automatically with specified recipients, subject and body. Please send the email by clicking the Send button.
Note: The VBA code is only working when you use Outlook as your email program.
Easily send email through Outlook based on the fields of created mailing list in Excel:
The Send Emails utility of Kutools for Excel helps to send email through Outlook based on the fields of created mailing list in Excel.
Download and try it now! ( 30-day free trail)
