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

Blinking Or Flashing Text Of Specified Cell With VBA Code

1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the following code into the Code window.

VBA code: Blinking text of specified cell in Excel

Sub StartBlink()

    Dim xCell As Range

    Dim xTime As Variant

      On Error Resume Next

    Set xCell = Range("Sheet2!A1")

  On Error Resume Next

 

        If xCell.Font.Color = vbRed Then

            xCell.Font.Color = vbWhite

        Else

            xCell.Font.Color = vbRed

        End If

    xTime = Now + TimeSerial(0, 0, 1)

    Application.OnTime xTime, "'" & ThisWorkbook.Name & "'!StartBlink", , True

End Sub

Note: in this VBA code, Sheet2 and A1 indicate the text in cell A1 of Sheet2 will be blinked in current workbook.

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

Now you need a button to start and stop the blinking.

4. Click Developer > Insert > Button (Form Control). See screenshot:

5. Draw a button in your worksheet, then an Assign Macro dialog box pops up, please click StartBlink in the Macro name box, and then click the OK button. See screenshot:

6. Right click the inserted button, and click Edit Text from the right-clicking menu. Then change the button text to the text you need such as Start / Stop Blinking.

From now on, when clicking on the button, the text of cell A1 starts blinking. And when clicking on the button again, the text of cell A1 stops blinking.

***https://www.extendoffice.com/documents/excel/3812-excel-blinking-text.html