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
.Attachments.Add ("e:\report.doc")
.Attachments.Add ("e:\report.doc")
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Notes:
Notes:
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".
5) Change the attached file in line .Attachments = "e:\report.doc"
5) Change the attached file in line .Attachments = "e:\report.doc"
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.
Send data from Excel to the body of your outlook email with the table.
The code
Sub esendtable()
Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object
Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
With newEmail
.To = Sheet1.Range("A2").Text
.CC = ""
.BCC = ""
.Subject = "Data"
.Body = "Please find the requested information" & vbCrLf & "Best Regards"
.display
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor
Sheet1.Range("B2:E5").Copy
pageEditor.Application.Selection.Start = Len(.Body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
.display
.Send
Set pageEditor = Nothing
Set xInspect = Nothing
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub
Option Explicit
Private Sub CommandButton1_Click()
Dim outlook As Object
Dim newEmail As Object
Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
With newEmail
.To = Sheet1.Range("B1").Text
.CC = Sheet1.Range("B2").Text
.BCC = Sheet1.Range("B3").Text
.Subject = Sheet1.Range("B4").Text
'.Body = "Hello World."
.Display
Dim xInspect As Object
Dim pageEditor As Object
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor
Sheet1.Range("A6:D11").Copy
pageEditor.Application.Selection.Start = Len(.Body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.Paste
Set pageEditor = Nothing
Set xInspect = Nothing
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub
Note:
'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr
Kill "C:\temp\file1.txt"
*** https://www.extendoffice.com/documents/excel/4662-excel-send-email-if-button-clicked.html
*********************************************************************************
Send data from Excel to the body of your outlook email with the table.
The code
Sub esendtable()
Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object
Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
With newEmail
.To = Sheet1.Range("A2").Text
.CC = ""
.BCC = ""
.Subject = "Data"
.Body = "Please find the requested information" & vbCrLf & "Best Regards"
.display
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor
Sheet1.Range("B2:E5").Copy
pageEditor.Application.Selection.Start = Len(.Body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
.display
.Send
Set pageEditor = Nothing
Set xInspect = Nothing
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub
****************************************************************
Option Explicit
Private Sub CommandButton1_Click()
Dim outlook As Object
Dim newEmail As Object
Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
With newEmail
.To = Sheet1.Range("B1").Text
.CC = Sheet1.Range("B2").Text
.BCC = Sheet1.Range("B3").Text
.Subject = Sheet1.Range("B4").Text
'.Body = "Hello World."
.Display
Dim xInspect As Object
Dim pageEditor As Object
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor
Sheet1.Range("A6:D11").Copy
pageEditor.Application.Selection.Start = Len(.Body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.Paste
Set pageEditor = Nothing
Set xInspect = Nothing
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub
'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr
Kill "C:\temp\file1.txt"
*** https://www.extendoffice.com/documents/excel/4662-excel-send-email-if-button-clicked.html
