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.
Excel Productivity Tools
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 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
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 Sub
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.
- 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?
Excel 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.· 3 days agoDo you have to be an excel user when pushing the button to send an email? or just the person receiving the email has to be an outlook user?
To post as a guest, your comment is unpublished.· 18 days agoHi,
How can I get the mail to automatically add my signature from outlook?
To post as a guest, your comment is unpublished.· 3 months agoHi There,
Can anyone could help me on the following requirement?
I have an excel sheet having two drop down lists.
1 drop down list- list of departments
2 drop down list- category
What i want is when I select a department and category from the lists;(for example if I select "sales" in department and "monthly report" in category)
I need to email the PDF version of that work sheet to the sales team with the email subject is monthly report.
If i select "production" from the department list, the email should go to a group of people in the production.
I appreciate if you can help me on this
To post as a guest, your comment is unpublished.· 2 months agoHi Rasike,
Sorry can't help you with that. Welcome to post any question in our forum: https://www.extendoffice.com/forum.html to get more Excel support from out Excel professional or other Excel fans.
To post as a guest, your comment is unpublished.· 3 months agoCan I reference the value in a cell in the subject with string? Some variation of the below?
.Subject = "New Event:" & Target.Address = "$B$38"
To post as a guest, your comment is unpublished.