How To Send Email If Button Is Clicked In Excel?

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")

        .Display   'or use .Send

    End With

    On Error GoTo 0

    Set xOutMail = Nothing

    Set xOutApp = Nothing

End Sub

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"

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