How to send email if button is clicked in Excel?
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
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)
Related articles:
- How to automatically send email based on cell value in Excel?
- How to send an email through Outlook when workbook is saved in Excel?
- How to send email if a certain cell is modified in Excel?
- How to send email if due date has been met in Excel?
- How to send email reminder or notification if workbook is updated in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
