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

Excel MATCH Function

Summary 

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Purpose 

Get the position of an item in an array

Return value 

A number representing a position in lookup_array.

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments 

lookup_value - The value to match in lookup_array.

lookup_array - A range of cells or an array reference.

match_type - [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Usage notes 

Use the MATCH function to get the position of a value in an array. Match offers three different matching modes, which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position.

Match type information

Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as "approximate match". However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:

Match type      Behavior

1                 Exact match or next smallest value. The lookup_array must be sorted in ascending order.

0                      Exact match only. The lookup_array does not need to be sorted.

-1                Exact match or next largest value. The lookup_array must be sorted in descending order.

Note: Be sure to set match type to zero (0) if you require an exact match. The default setting of 1 can cause MATCH to return results that look "normal" but are in fact incorrect. I recommend always providing a value for match_type, as a reminder of what behavior is expected.

Basic exact match

When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:

=MATCH(E1,B2:B7,0)

Basic exact match with MATCH function

Basic approximate match

When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:

=MATCH(E1,B2:B7,1)

Basic approximate match with MATCH function

Basic wildcard match

When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E1 is:

=MATCH(E1,B2:B7,0)

This is equivalent to:

=MATCH("pq*",B3:B11,0)

Excel IF Function

Summary 

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

Purpose 

Test for a specific condition

Return value 

The values you supply for TRUE or FALSE

Syntax 

=IF (logical_test, [value_if_true], [value_if_false])

Arguments 

logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.

value_if_true - [optional] The value to return when logical_test evaluates to TRUE.

value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Usage notes 

Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

In the example shown, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in C2, copied down, is:

=IF(B2>=70,"Pass","Fail")

Translation: If the value in B2 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".

The logical flow this formula can be reversed. The formula below returns the same result:

=IF(B2<70,"Fail","Pass")

Translation: If the value in B2 is less than 70, return "Fail". Otherwise, return "Pass".

Both formulas above, when copied down, will return correct results.

Logical operators

When you are constructing a test with IF, you can use any of the following logical operators:

Comparison operator	Meaning	Example
=	equal to	A1=D1
>	greater than	A1>D1
>=	greater than or equal to	A1>=D1
<	less than	A1<d1< td=""></d1<>
<=	less than or equal to	A1<=D1
<>	not equal to	A1<>D1

IF with AND, OR

The IF function can be combined with the AND function and the OR function. For example, to return "OK" when A1 is between 7 and 10, you can use use a formula like this:

=IF(AND(A1>7,A1<10),"OK","")

Translation: if A1 is greater than 7 and less than 10, return "OK". Otherwise, return nothing ("").

To return B1+10 when A1 is "red" or "blue" you can use the OR function like this:

=IF(OR(A1="red",A1="blue"),B1+10,B1)

Translation: if A1 is red or blue, return B1+10, otherwise return B1.

Calculate Due Dates for Payments or Years Toward Retirement with EDATE Function

Summary 

The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates in the past. 

Purpose 

Shift date n months in future or past

Return value 

New date as Excel serial number

Syntax 

=EDATE (start_date, months)

Arguments 

start_date - Start date as a valid Excel date.

months - Number of months before or after start_date.

Example

EDATE is a date and time function in excel which adds a given number of months into a date and gives us a date in a numerical format of date, the arguments this function takes is date and integers, date as the start date to begin with and number of months are integers to add it to the given start date, the output returned by this function is also a date value, the method to use this function is =Edate( Start Date, Months).

Difference between Salary & Salary Payable

Salary payable is an accrued liability and therefore credited in the books of accounts. The journal entry for salary payable is shown below. on one hand salary is debited being the expense for the company and on other hand a liability is created with the name of salary payable.

Date Particulars              Dr.          Cr.

xx.xx.xxxx Salary                     Rs xxx

xx.xx.xxxx Salary payable                        Rs xxx

Example:-

Salary Payable Journal Entry 

On 31 December 2014 salary for the month of December amounting Rs 20,000 to be paid in January, 2014 shall be recorded as under. Salary account shall be debited, while salary payable account will be credited.

Date Particulars              Dr.                  Cr.

2014 Salary                      Rs 20,000

2014 Salary payable                               Rs 20,000

Salary Payment Journal Entry

In next month, when the salary shall be paid, then following entry would be recorded in the books of accounts. The liability would be removed upon payment of liability by debiting the salary payable and crediting the cash.

Date Particulars          Dr.                   Cr.

2014         Salary payable      Rs 20,000

2014         Cash                                          Rs 20,000

The above scheme of entries can be used to record all accrued expenses. In first place the expense is debit and related liability is created and then in next stage the liability is removed , when liability is paid off.

Difference between Salary & Salary Payable

How to Create a Data Entry Form in Excel [One-Click, No VBA]

In a short while, you will discover that how helpful this tool is.

But, this is hard to believe that there is no listing of this data entry form in the ribbon. So, you have to make it appear before we use it.

And before you activate this form, you need a table to enter data or at least headers of the data.

To activate it, click any of the cells in the table or just select one of the heading cells and use one of the below two methods.

1. Use the keyboard shortcut key: Alt + D + O + O.

2. Add an icon to quick access toolbar.

a. Right-click on any of the existing icons in the Quick Access Toolbar.

b. Click on ‘Customize Quick Access Toolbar’.

c. In the ‘Excel Options’ dialog box that opens, select the ‘All Commands’ option from the drop-down.

d. Scroll down the list of commands and select ‘Form’.
e. Click on the ‘Add’ button.
f. Click OK.

The above steps would add the Form icon to the Quick Access Toolbar  (as shown below).

After using one of the above options, it will create a data entry form for you just like below.

And if you notice, all the headings of your data table automatically captured in the form.