Skip to main content

How to automatically send email based on cell value in Excel?

Author: Siluvia Last Modified: 2022-03-11

Supposing you want to send an email through Outlook to a certain recipient based on a specified cell value in Excel. For example, when the value of cell D7 in a worksheet is greater than 200, then an email is created automatically. This article introduces a VBA method for you to quickly solve this issue.

Automatically send email based on cell value with VBA code


Automatically send email based on cell value with VBA code

Please do as follows to send an email based on cell value in Excel.

1. In the worksheet you need to send email based on its cell value (here says the cell D7), right-click the sheet tab, and select View Code from the context menu. See screenshot:

2. In the popping up Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the sheet code window.

VBA code: Send email through Outlook based on cell value in Excel

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Notes:

1). In the VBA code, D7 and value > 200 are the cell and cell value you will send email based on.
2). Please change the email body as you need in xMailBody line in the code.
3). Replace the Email Address with the recipient email address in line .To = "Email Address".
4). And specify the Cc and Bcc recipients as you need in .CC = “” and Bcc = “” sections.
5). Finally change the email subject in line .Subject = "send by cell value test".

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

From now on, when the value you entering in cell D7 is greater than 200, an email with specified recipients and body will be created automatically in Outlook. You can click the Send button to send this email. See screenshot:

Notes:

1. The VBA code is only working when you use Outlook as your email program.

2. If the entered data in cell D7 is a text value, the email window will be popped out as well.


Easily send email through Outlook based on fields of created mailing list in Excel:

The Send Emails utility of Kutools for Excel helps users sending email through Outlook based on created mailing list in Excel.
Download and try it now! (30-day free trail)


Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (318)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hello cristal, i need u help, im trying to use this code in a board, so i need that when for example E2 < D2 send a message with the information in C2, and this whit E3,D3,C3........ E4,D4,C4 etc.
This comment was minimized by the moderator on the site
Hi Ernesto,

The following VBA code may help. Please give it a try. Thank you.
Note: If a change is detected and the new value in an "E" row cell is less than its corresponding "D" row cell, it triggers the Mail_small_Text_Outlook subroutine. The content from the corresponding "C" row cell is passed into this subroutine and included in the email body.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by extendoffice 20230315
    Dim xRg As Range
    Dim cell As Range

    ' Only trigger for changes in column E
    Set xRg = Intersect(Me.Range("E:E"), Target)

    If Not xRg Is Nothing Then
        Application.EnableEvents = False ' Prevent further event triggers
        On Error GoTo Finalize ' Ensure events are re-enabled in case of error

        For Each cell In xRg
            ' Check if the cell in column E is less than the corresponding cell in column D
            If IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, -1).Value) Then
                If cell.Value < cell.Offset(0, -1).Value Then
                    ' Call the mail subroutine, passing the content of the corresponding cell in column C
                    Call Mail_small_Text_Outlook(cell.Offset(0, -2).Value)
                End If
            End If
        Next cell

Finalize:
        Application.EnableEvents = True ' Re-enable events
        On Error GoTo 0 ' Clear error handling
    End If
End Sub

Sub Mail_small_Text_Outlook(ByVal mailContent As String)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String

    ' Create the Outlook objects
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)

    ' Customize your email body with the content from column C
    xMailBody = "Hi there" & vbNewLine & vbNewLine & mailContent

    ' Customize the email details
    With xOutMail
        .To = "Email Address" ' Add your recipient here
        .CC = ""
        .BCC = ""
        .Subject = "Send by cell value test"
        .Body = xMailBody
        .Display   ' or use .Send to send the email directly
    End With

    ' Cleanup
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
This comment was minimized by the moderator on the site
Dear, the code works perfectly, but I am still looking to expand it a bit for my project. I am creating a project dashboard where I track the progress of the projects. The end time of the project will then be listed in row G:G and if, for example, he still has 2 weeks before the project must be completed, he must send an email to the project owner who is responsible there, this project owner will then be in, for example, row "I" but each project is placed in a new column and therefore always has a different project owner. How can I process this so that VBA automatically sends me an email to the owner in I7 when the date is reached in BV G7?
And with G8 then send to the owner who is in I8? And so on of course.
Hopefully you can help me.
This comment was minimized by the moderator on the site
Hi Crystal,

This is great. Thank you! Not sure if you're still monitoring this I'm but wondering if you could advise on how to make some modifications to this code. I was able to tweak it to look at a range of cells rather than just a specific cell like in your original example (In my case D4:D41) but what if I want to look for changes made above a certain value in multiple columns? In my case I'd like to look for values >8 in D4:D41 as well as >13 in E4:E41. Also, is there a way to hold off on sending the email until multiple changes have been made instead of immediately after a cell in Column D (or E) is raised above the threshold? I know that by using the 'Developer' tab on the ribbon I can create a "Send Email" button but what tweaks to the code would need to be made so that it doesn't send until that button is clicked?

As a cherry on top, is there a way to call out the corresponding row data from column A in the body of the email? The sheet I'm working with has various room numbers in Column A and target values in Columns D & E. Is there a way to structure it so that if the value in, say, D11 goes above the threshold the email will say "Room # (A11)" needs attention and if E25 goes above its threshold the email will say Room # (E25) needs attention? If that makes sense
This comment was minimized by the moderator on the site
Hi, i have created and excel sheet that uses dates of the year ( todays date to next years expiry date ) the cell i have crated is a column consisting of 60+ candidates. The cell changes value until it reaches the expire date. I would like to use this column dates to trigger an automated email with an attachment of the excel sheet when the cell value reaches a certain amount before the expiry date.
I want to use this as an alert so that i can see which person document will expire.

Please help I've been struggling for a long while...

Thank you!!
This comment was minimized by the moderator on the site
Hi Andries,

Sorry I don't quite understand your question. Would you mind providing a screenshot of your data? Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Hello, I set up the VBA to send an automatic email. Now, when a row with columns A, B, C, D, E, F, G, H, I, J, K, L, M, N is filled in, an email is automatically created and sent shall be. As an example, line 10 has now been filled in from A to N, as soon as this has happened this data should be sent in the email.
This should then be possible up to line 5000.
Can you help me with the problem?
This comment was minimized by the moderator on the site
Hi Michael Schlegel,

To modify the VBA code to automatically create an email when columns A to N in a row are filled out, follow these steps:
In the Worksheet_Change event, monitor the range A:N up to the 5000th row.
Check if all cells in the columns A to N of the changed row contain values.
If all cells in that row have values, trigger the email displaying function.


Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20230817
    Dim EntireRow As Range
    Dim IsRowFilled As Boolean
    Dim i As Integer
    
    ' If multiple cells are changed simultaneously, exit
    If Target.Cells.CountLarge > 1 Then Exit Sub

    ' Only monitor the range A10:N5000
    If Not Intersect(Target, Range("A10:N5000")) Is Nothing Then
        ' Check if the entire row from A to N has values
        Set EntireRow = Range("A" & Target.Row & ":N" & Target.Row)
        IsRowFilled = True
        For Each cell In EntireRow
            If IsEmpty(cell.Value) Then
                IsRowFilled = False
                Exit For
            End If
        Next cell
        ' If the row is filled, send the email
        If IsRowFilled Then
            Call Mail_small_Text_Outlook(EntireRow)
        End If
    End If
End Sub

Sub Mail_small_Text_Outlook(RowData As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)

    ' Modify this line to format the email body as per your requirements
    xMailBody = "The following data has been entered:" & vbNewLine & Join(Application.Transpose(Application.Transpose(RowData.Value)), " ")

    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "New row filled out in Excel"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
This comment was minimized by the moderator on the site
Hi

Thank you so much for posting this VBA Code and instructions. Your code is most definitely continuing to help us rookies! Thank you 🥰! When I found it I felt like I'd won in the Excel VBA game. However, I am stuck on something so I'm hoping you can help. If not, please kindly point me in the right direction. I'm new to VBA, self-taught as much as possible, and only have very basic understanding of most of the commands.

Your code has helped me answer the question of "how to I send to an email address without having to manually enter the address in the code". But, now I'm struggling with "how to route through a list of addresses based on a cell condition". Meaning, I have a protected form with 28 data elements, 21 lines of data, and 5 approvers must approve the data before any action can be taken (data added to SAP). Signature lines have been added to the form for electronic approvals (was initially trying to use the Signature line email addresses but failed to find help on that issue).

Since I was not able to find how to use the Signature line email addresses, I created an email table with email addresses in the range M37:M42 and a Sent indicator in the range O37:O42.

Workflow: once the Originator of the form has completed entering the data, he/she clicks the "Send to Next Approver" button. The code for the button is to loop through checking that M & target row is not blank, check that the Sent indicator in O & target row is blank, then generate an email to the approver and set Sent indicator for that row to "X". The Originator (M37) will be the last to receive a final email showing all approvals. At the end of routing, all Sent indicator fields should have an "X" and the form is digitally signed and protected.

I'm getting a compile error : "Procedure declaration does not match description of event or procedure having the same name". I think it is something very simple look right at me, but I cannot see to find it. Thank you in advance.

Here's my code:

Dim xRg As Range
Private Sub CommandButton1_Click(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("O38:O42"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = " " Then
Call Mail_small_Text_Outlook(Target)
Set Target.Value = "X"
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xRgTo As Range
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next

Set xRgTo = Application.Range("M" & Target.Row)
If xRgTo Is Nothing Then Exit Sub

ThisWorkbook.Worksheets("List Form").Unprotect ("9999")
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

xMailBody = "Your review/approval is needed on a Material Master form." & vbNewLine & vbNewLine & _
"Click the hyperlink or copy/paste the file name in a browser to open form." & vbNewLine & vbNewLine & _
Range("BW1") & vbNewLine & _
" "
On Error Resume Next
With xOutMail
.To = xRgTo
' .CC = ""
' .BCC = ""
.Subject = "Material Master / BOM Review & Approval"
.Body = xMailBody
' .Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
ActiveSheet.Protect ("9999"), DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True
' ThisWorkbook.Worksheets("List Form").Protect ("9999")
End Sub
This comment was minimized by the moderator on the site
Hi

Thank you so much for posting this VBA Code and instructions. Your code is most definitely continuing to help us rookies! Thank you 🥰! When I found it I felt like I'd won in the Excel VBA game. However, I am stuck on something so I'm hoping you can help. If not, please kindly point me in the right direction. I'm new to VBA, self-taught as much as possible, and only have very basic understanding of most of the commands.

Your code has helped me answer the question of "how to I send to an email address without having to manually enter the address in the code". But, now I'm struggling with "how to route through a list of addresses based on a cell condition". Meaning, I have a protected form with 28 data elements, 21 lines of data, and 5 approvers must approve the data before any action can be taken (data added to SAP). Signature lines have been added to the form for electronic approvals (was initially trying to use the Signature line email addresses but failed to find help on that issue).

Since I was not able to find how to use the Signature line email addresses, I created an email table with email addresses in the range M37:M42 and a Sent indicator in the range O37:O42.

Workflow: once the Originator of the form has completed entering the data, he/she clicks the "Send to Next Approver" button. The code for the button is to loop through checking that M & target row is not blank, check that the Sent indicator in O & target row is blank, then generate an email to the approver and set Sent indicator for that row to "X". The Originator (M37) will be the last to receive a final email showing all approvals. At the end of routing, all Sent indicator fields should have an "X" and the form is digitally signed and protected.

I'm getting a compile error : "Procedure declaration does not match description of event or procedure having the same name". I think it is something very simple look right at me, but I cannot see to find it. Thank you in advance.

Here's my code:

Dim xRg As Range
Private Sub CommandButton1_Click(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("O38:O42"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = " " Then
Call Mail_small_Text_Outlook(Target)
Set Target.Value = "X"
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xRgTo As Range
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next

Set xRgTo = Application.Range("M" & Target.Row)
If xRgTo Is Nothing Then Exit Sub

ThisWorkbook.Worksheets("List Form").Unprotect ("9999")
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

xMailBody = "Your review/approval is needed on a Material Master form." & vbNewLine & vbNewLine & _
"Click the hyperlink or copy/paste the file name in a browser to open form." & vbNewLine & vbNewLine & _
Range("BW1") & vbNewLine & _
" "
On Error Resume Next
With xOutMail
.To = xRgTo
' .CC = ""
' .BCC = ""
.Subject = "Material Master / BOM Review & Approval"
.Body = xMailBody
' .Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
ActiveSheet.Protect ("9999"), DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True
' ThisWorkbook.Worksheets("List Form").Protect ("9999")
End Sub
This comment was minimized by the moderator on the site
Wie schreibe ich den Code um, wenn je nachdem welche Zelle ausgefüllt wird eine Mail an eine bestimmte Person gehen soll.

Beispielsweise wird Zelle A1 ausgefüllt, dann geht automatisch ein Mail Fenster auf mit Mail Adresse von Person A.
Wenn Zelle B1 ausgefüllt wird, dann geht automatisch ein Mail Fenster auf mit Mail Adresse von Person B
... etc.

Ich habe es mit der Funktion Case versucht, jedoch leider nicht geschafft.
Besten Dank für die Rückmeldung.
This comment was minimized by the moderator on the site
Dear

code run when insect value in D7 but can't run when D7 = result ( X+Y)
How to ?
Thanks your
This comment was minimized by the moderator on the site
Hi I am looking for a code that when a cell changes YELLOW, I want an email to send automatically to that person and when the cell changes RED another e-mail to be sent to them.

The cells contain expiry dates of mandatory training and are:
- GREEN when in date,
- YELLOW when they have 29 days before they are out of date AND
- RED when they are a day over the date that their training expires.

The e-mails are to prompt them to book onto a course when they are YELLOW (almost out of date) and to input there new expiry date when they get an email when it turns RED.

Hope that all makes sense.

Formula for it to turn green: GREATER THAN =NOW()+30
Yellow: BETWEEN =NOW() =NOW()+29
Red: LESS THAN =NOW()-1

Thank you in advance, Alyssa
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations