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

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:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Thais · 5 days ago
    Hi !


    I have managed to get the coding up and running. However I would like to have the "from" fix from one of my accounts. Several people will be creating this autoresponse so I need to have a fixed "from"


    Thank you in advance!
  • To post as a guest, your comment is unpublished.
    Nicolas Molina · 8 days ago
    Thank you for the code ! I'm having a small issue where the code will only select the first cell that meets my criteria
    ( I want to flag any tasks that are late so were due before the current date, however the code is only taking the first value and not all the values in my excle"

    the following is my code, i would be forever grateful if you could help!

    Public Sub Late_Task_Email()
    'Updated by Extendoffice 2018/11/22
    Dim xRgDate As Range
    Dim xRgSend As Range
    Dim xRgText As Range
    Dim xRgDone As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xLastRow As Long
    Dim vbCrLf As String
    Dim xMailBody As String
    Dim xRgDateVal As String
    Dim xRgSendVal As String
    Dim xMailSubject As String
    Dim xStrRang As String

    Dim i As Long

    On Error Resume Next

    Set xRgDate = Range("F12:F500")
    Set xRgDate = Range(xStrRang)


    Set xRgSend = Range("B12:B500")
    Set xRgSend = Range(xStrRang)


    Set xRgText = Range("A12:A500")
    Set xRgText = Range(xStrRang)



    xLastRow = xRgDate.Rows.Count

    Set xRgDate = xRgDate(1)
    Set xRgSend = xRgSend(1)
    Set xRgText = xRgText(1)
    Set xOutApp = CreateObject("Outlook.Application")
    For i = 1 To xLastRow
    xRgDateVal = ""
    xRgDateVal = xRgDate.Offset(i - 1).Value
    If xRgDateVal <> "" Then
    If CDate(xRgDateVal) - Date < 0 Then
    xRgSendVal = xRgSend.Offset(i - 1).Value
    xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal
    vbCrLf = "

    "
    xMailBody = ""
    xMailBody = xMailBody & "Dear DOE,"
    xMailBody = xMailBody & "This task is OVERDUE!"
    xMailBody = xMailBody & ""
    Set xMailItem = xOutApp.CreateItem(0)
    With xMailItem
    .Subject = xMailSubject
    .To = xRgSendVal
    .HTMLBody = xMailBody
    .Display
    '.Send
    End With
    Set xMailItem = Nothing
    End If
    End If
    Next
    Set xOutApp = Nothing
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 5 days ago
      Hi Nicolas Molina,
      Maybe the code in this tutorial can help you solve the problem:
      How To Send Email If Due Date Has Been Met In Excel?
      https://www.extendoffice.com/documents/excel/4664-excel-send-email-if-due-date-has-been-met.html

  • To post as a guest, your comment is unpublished.
    Kyle · 8 days ago
    Hi Crystal, thank you for sharing this awesome code! I changed the code to function with text so when a cell has anything typed in, it prompts the email function. My question is, how can I code the email to auto-populate the row and column header info for a specific cell along with text? Here are the ranges of data with text:

    Subject "(A3:A50) - (Q1:AC1) - Pending"
    Mail Body "(P1:AB1) complete."
    "Please prepare the (Q1:AC1)"

    '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("P3:AB50"), Target)
    If xRg Is Nothing Then Exit Sub
    If Target.Value > 0 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 = "Hello," & vbNewLine & vbNewLine & _
    "Create Bid Comparison step complete." & vbNewLine & _
    "Please prepare the Recommendation of Award." & vbNewLine & vbNewLine & _
    "Thank you"
    On Error Resume Next
    With xOutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ("Project 20-20")
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
    • To post as a guest, your comment is unpublished.
      Kyle · 8 days ago
      To better clarify, I am hoping to auto-populate the column and row headers into the email, along with text.

      Subject "(A3:A50) - (Q1:AC1) - Pending"
      Mail Body "(P1:AB1) complete."
      "Please prepare the (Q1:AC1)"

      (i.e. C12, the headers for the subject line would be A12 and C1. C12 for the 1st line in the "Mail Body" and D12 for the 2nd.)

      This way, any cell I enter data into will prompt the email and pull the headers in pertaining to that cell.

      Thank you!
  • To post as a guest, your comment is unpublished.
    Mladen · 11 days ago
    Hello
    I need a help. I use VB code for automatically to send email based on cell value in Excel. The thing is that this code works when I enter the value and press enter. But in my case the cell is filling automatically with formula, and when the value is reached it doesn't open the email (the code do not works in this case). Ex.
  • To post as a guest, your comment is unpublished.
    shondap · 14 days ago
    Hey Crystal,

    I'd like to set automatic emails based on drop options of Update Request, Complete or More Info in Cell I. Update Request will send an email to three different email addresses and Complete or More Info will send an email to one email address. Also, how do to I write each Range to equal data that corresponds to the row with the drop down option.

    My data below only triggers off Update Request for a specific range (b3). Help!

    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("I:I"), Target)
    If xRg Is Nothing Then Exit Sub
    If Target.Value = "Update Request" 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 = "All," & vbNewLine & vbNewLine & _
    "Updates have been entered into the maintenance log:" & vbNewLine & _
    Range("b3") & vbNewLine & _
    Range("c3") & vbNewLine & _
    Range("d3") & vbNewLine & vbNewLine & _
    "Thanks," & vbNewLine & _
    "Training Team"
    With xOutMail
    .To = "ShondaX@yahoo.com"
    .CC = ""
    .BCC = ""
    .Subject = "Log Update Requests"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub


    Thanks, Shonda
  • To post as a guest, your comment is unpublished.
    fari · 21 days ago
    hi there
    i have a table in excel file
    i need to email it row by row
    can you help me?
  • To post as a guest, your comment is unpublished.
    Tyson nold · 1 months ago
    Is there a way to send emails to individual recipients that can be pulled from another cell in the same row?
    • To post as a guest, your comment is unpublished.
      crystal · 26 days ago
      Hi Tyson nold,
      Supposing the recipient's email address is in F7, please apply the below code.

      Dim xRg As Range
      'Update by Extendoffice 2020/7/17
      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 = Range("F7")
      .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
      • To post as a guest, your comment is unpublished.
        Mike · 19 days ago
        I'm not Tyson, but found it helpful (along with the whole tutorial). Thank you!
  • To post as a guest, your comment is unpublished.
    Gary · 2 months ago
    Hi, I am using the above code to send auto-generated emails when a cell from range G4:G999, how could I add the value(text) from the adjacent cell in range A4:A999 to the xMailBody on the generated email?
    Many thanks,
    G
  • To post as a guest, your comment is unpublished.
    Brittany · 2 months ago
    I want my email to send when any of the cells in row H are changed to a specific value, which is one of 3 drop down options (Ready for Inventory) set for that row. How do I modify this coding to that instead of a manually entered value? Thanks!!
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Brittany,
      Supposing there are drop down lists in column H, and you want to trigger an email when selecting "Done" from the drop-down, please try the below VBA to get it down.

      Dim xRg As Range
      'Update by Extendoffice 2020/6/12
      Private Sub Worksheet_Change(ByVal Target As Range)
      On Error Resume Next
      If Target.Cells.Count > 1 Then Exit Sub
      Set xRg = Intersect(Range("H:H"), Target)
      If xRg Is Nothing Then Exit Sub
      If Target.Value = "Done" 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
      • To post as a guest, your comment is unpublished.
        Mladen · 11 days ago
        HelloI need a help. I use VB code for automatically to send email based on cell value in Excel. The thing is that this code works when I enter the value and press enter. But in my case the cell is filling automatically with formula, and when the value is reached it doesn't open the email (the code do not works in this case).
        • To post as a guest, your comment is unpublished.
          crystal · 5 days ago
          Hi Mladen,
          Try the below code.

          Dim xRg As Range
          'Update by Extendoffice 2012/08/07
          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

          Private Sub Worksheet_Calculate()
          Dim xI As Integer
          Dim xRg As Range
          Set xRg = Range("D7")
          On Error GoTo Err01
          xI = Int(xRg.Value)
          If xI > 200 Then
          Call Mail_small_Text_Outlook
          End If
          Err01:
          End Sub
      • To post as a guest, your comment is unpublished.
        Shonda · 20 days ago
        What if there are two options in the drop down list Update and Complete? How would the code look? What if each had its own email that would to be sent based on the action?
  • To post as a guest, your comment is unpublished.
    dave · 2 months ago
    Hi
    I am trying to do this type of automatic email button a true or false question true than the form does nothing but false then the form to automatically email out
    Can you help ??
  • To post as a guest, your comment is unpublished.
    darkgyft · 4 months ago
    Cystal,

    This is definitely a time saver. I want to know what I would have to add to the vba to include my signature line on the outgoing email. On my usual emails, I have a signature line with company logo and contact info. I want to include that so the email looks just like it would if I had sent myself manually from Outlook. Many thanks!

    Mike
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi darkgyft.
      Try the below VBA code. Hope I can help. thank you.

      Dim xRg As Range
      'Update by Extendoffice 2020/05/22
      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)
      On Error Resume Next
      With xOutMail
      .Display 'or use .Send
      .To = "Email Address"
      .CC = ""
      .BCC = ""
      .Subject = "send by cell value test"
      .HTMLBody = "This is a test email sending in Excel" & "
      " & .HTMLBody
      End With
      On Error GoTo 0
      Set xOutMail = Nothing
      Set xOutApp = Nothing
      End Sub

  • To post as a guest, your comment is unpublished.
    thumsri@gmail.com · 4 months ago
    Hi - I tried to use the code, it works fine when cell value D7 is changed manually. if D7 value changed using any formula i.e vlookup or IF .code doesnt trigger. Code trigger only when any cell on the sheet is change keeping D7 value above 200. is there any wayout for the problem . regards srini
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi,
      The below VBA code can help you solve the problem. Please have a try. Thank you.

      Dim xRg As Range
      'Update by Extendoffice 20120/5/22
      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

      Private Sub Worksheet_Calculate()
      Dim xI As Integer
      Dim xRg As Range
      Set xRg = Range("D7")
      On Error GoTo Err01
      xI = Int(xRg.Value)
      If xI > 200 Then
      Call Mail_small_Text_Outlook
      End If
      Err01:
      End Sub

  • To post as a guest, your comment is unpublished.
    Stephanie · 5 months ago
    Hi All,
    I'm trying to connect all these actions by clicking a button instead of running it each time. Does anybody know how to do this?
  • To post as a guest, your comment is unpublished.
    Bob · 5 months ago
    Hi! How can I send the e-mails without accepting them at outlook. And they sand automatically without knowing.
  • To post as a guest, your comment is unpublished.
    lynsey · 7 months ago
    Hi all - I am trying to use this code but when I exit the developer and test the cell- it is over the specifications, but nothing happens. I don't receive a pop up to send the email? But when I run the code in developer it comes up - its just when I'm within the spreadsheet and change the cell, nothing happens. I have copied and edited the code as per instructions.
  • To post as a guest, your comment is unpublished.
    karthick · 7 months ago
    Dear all kindly help me …… my requirement as follow..!!

    I will be having a workbook in which there will be set of data

    then i'll be manually entering a date as a value in a cell
    when the actual date matches with my manual entered date
    excel has to automatically trigger a mail in OUTLOOK with so and so data along with a body of letter to recipient and also cc

    thanks a lot in advance
  • To post as a guest, your comment is unpublished.
    raguirre76@gmail.com · 8 months ago
    How can I refer to a cell on the worksheet to include in the email automatically?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Robert,
      Supposing you want to refer to cell A7 on the worksheet, please apply the below code.

      Dim xRg As Range
      'Update by Extendoffice 2019/12/13
      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 Rng = Selection.SpecialCells(xlCellTypeVisible)
      Set xOutApp = CreateObject("Outlook.Application")
      Set xOutMail = xOutApp.CreateItem(0)
      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      Range("A7") & vbNewLine & _
      "Best Regards"

      On Error Resume Next
      With xOutMail
      .To = ""
      .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
  • To post as a guest, your comment is unpublished.
    tarti.s689@gmail.com · 8 months ago
    Crystal you're the best.

    I want to download the data to be checked with a SQL query, what do I have to do to make the code below work then, at the moment it only works manually.
    Thank you


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    If (Target.Count > 1) Then Exit Sub
    Set xRg = Intersect(Target, Range("Z:Z"))
    If xRg Is Nothing Then Exit Sub
    If UCase(Target.Value) = "HOT" Then
    Call Mail_small_Text_Outlook(Target)
    End If
    End Sub
    Sub Mail_small_Text_Outlook(ByVal xCell As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello Team" & vbNewLine & vbNewLine & _
    "Lot " & Range("D" & xCell.Row) & "'s Priority has changed to HOT, please prioritize this lot."
    On Error Resume Next
    With xOutMail
    .To = "Email Address1; Email Address2; Email Address3; Email Address4; Email Address5"
    .CC = ""
    .BCC = ""
    .Subject = "Lot number is " & Range("D" & xCell.Row)
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Nano22 · 8 months ago
    Crystal you're the best.
    what do i have to add to make the code work with formulas ?
    Thank you

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    If (Target.Count > 1) Then Exit Sub
    Set xRg = Intersect(Target, Range("B:B"))
    If xRg Is Nothing Then Exit Sub
    If UCase(Target.Value) = "Yes" Then
    Call Mail_small_Text_Outlook(Target)
    End If
    End Sub
    Sub Mail_small_Text_Outlook(ByVal xCell As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello Team" & vbNewLine & vbNewLine & _
    "Lot " & Range("A" & xCell.Row) & "'s Priority has changed to Yes, please prioritize this lot."
    On Error Resume Next
    With xOutMail
    .To = "Email Address1; Email Address2; Email Address3; Email Address4; Email Address5"
    .CC = ""
    .BCC = ""
    .Subject = "Lot number is " & Range("A" & xCell.Row)
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    prasana05@gmail.com · 10 months ago
    I need help with Excel to send an automatic email using outlook when a cell value is changed in the file.

    Question . Sheet name "Attrition Report" Column L in the file has the list of RAG status, So, when one of the manager will choose the options (Green, Red, Amber) for possible attrition cases. When the cell value changes to Red or Amber ; excel should automatically send an email ONLY when it changes to RED or Amber. The body of the email should show employee name which is in Cell range "B" and the RAG status which is in Range "L".
    I put the code but i guess it's incorrect.

    Any help on this will be much appreciated.

    Thanks and Regards

    Emanuel Prasanna Kumar

    The code i currently have. is.

    _______________

    Dim xRg As Range
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    For Each xRg In Range("L2:L100")
    If CInt(xRg.Value) = "Red" And "Amber" Then
    Call Mail_small_Text_Outlook
    End If
    Next
    End Sub
    Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim xIntR As Integer
    xIntR = xRg.Row
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "Emp Name. :" & Range("B" & xIntR).Value & vbNewLine & _
    "RAG Status :" & Range("L" & xIntR).Value & vbNewLine & _
    "Factor :" & Range("K" & xIntR).Value & vbNewLine
    On Error Resume Next
    With xOutMail
    .To = "Emanuel.Kumar@infovision.com"
    .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
  • To post as a guest, your comment is unpublished.
    rajeshwariagale04@gmail.com · 11 months ago
    How can I display the Sheet name and list of cells which has value more than 200 in the email content?
  • To post as a guest, your comment is unpublished.
    ClaireC · 11 months ago
    Hello,
    How do I get excel to send an email automatically when the worksheet is opened based on a cell value?
  • To post as a guest, your comment is unpublished.
    Claire · 11 months ago
    Hello,

    How can i get excel to send an email automatically when the workbook is opened based on a cell value, instead of when the cell value is changed?
  • To post as a guest, your comment is unpublished.
    joseph · 11 months ago
    I want to add different email address per row, but when I change one row, the entire worksheet changes. How can I limit the changes only to one row each per one email account?
  • To post as a guest, your comment is unpublished.
    mshreyascse@gmail.com · 11 months ago
    Hi All,
    Can someone help me to figure out the below
    I have an Excel sheet with loads of worksheets in it.
    Data is entered in 3 worksheets on daily basis and information from these 3 sheets is sent out to mailing list on every Sunday (data accumulated from Last Sunday to Saturday in those 3 work sheets).
    This XL sheet is stored in share point.
  • To post as a guest, your comment is unpublished.
    Katie · 11 months ago
    Hi Crystal,
    Thank you for this code. It is extremely helpful. I am trying to add a few things to it and was wondering if you or someone following this post could help.

    How can you add Cell Text from the same Range (row) to the Mail Body?
    How can you get the code to send an email for every Cell in the Range that is over an amount?

    Thank you so much!
  • To post as a guest, your comment is unpublished.
    RobArchibald · 11 months ago
    Hi Crystal,
    Thank you for your extremely useful posts! I have the VBA code working perfectly for our purposes, except for one problem - when I open the workbook or make a change, each email is created twice. What can be done to ensure each email is only created once?

    Many thanks, Rob

    My current code:

    Dim xRg As Range
    'Update by Extendoffice 2019/8/2
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Intersect(Range("A1"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = 1 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 = "Dear " & Range("C3") & "," & vbNewLine & vbNewLine & _
    "I am writing to inform you that the Form D filing for " & Range("C2") & " is due on " & Range("C4") & ". All securities offerings that rely on the exemptions set forth under Regulation D are required to file a Form D every year for as long as the offering is open." & vbNewLine & vbNewLine & _
    "It is advised that " & Range("C2") & " should maintain its Form D filing if it continues to offer securities in reliance on Regulation D. Maintaining an up-to-date Form D is important in ensuring compliance with federal and state securities regulations." & vbNewLine & vbNewLine & _
    "Please let us know at your earliest convenience if you wish to arrange the renewal of the Form D." & vbNewLine & vbNewLine & _
    "Thank you," & vbNewLine
    On Error Resume Next
    With xOutMail
    .To = Range("C5")
    .CC = "t.kim@geracillp.com"
    .BCC = ""
    .Subject = "Form D filing notice for " & Range("C2")
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub

    Private Sub Worksheet_Calculate()
    Dim xI As Integer
    Dim xRg As Range
    Set xRg = Range("A1")
    On Error GoTo Err01
    xI = Int(xRg.Value)
    If xI = 1 Then
    Call Mail_small_Text_Outlook
    End If
    Err01:
    End Sub
    • To post as a guest, your comment is unpublished.
      RobArchibald · 11 months ago
      Actually, more to the point - how do I make it so that only one email is created when I open the workbook, but not just when I make a change to it? Currently changing any cell results in a new email being created.

      Please help with this last hurdle, I'm so close!
  • To post as a guest, your comment is unpublished.
    Viji · 1 years ago
    How to insert the images in email body
  • To post as a guest, your comment is unpublished.
    Edward · 1 years ago
    I Keep Getting this:

    Run-time error '429':

    ActiveX component can't create object

    I use apple and don't have outlook... is there a way to make this work without outlook.. and

    instead Mail Version 11.5 (3445.9.1) apple
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Edward,
      The code can't work without Outlook. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    Ganesh · 1 years ago
    Thank you for posting valuable and important VBA code. I am new in VBA coding and trying to modify your VBA with editing Mail Body;

    ...
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2"
    On Error Resume Next
    ...

    How to add Cell Text Ex. text from A7, B7 & C7 related to D7 into Mail Body instead of other text. Could someone please guide me. Thank you in advance.
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Ganesh,
      Please apply the below code. Hope I can help.

      Dim xRg As Range
      'Update by Extendoffice 2019/12/13
      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 & _
      Range("A7") & vbNewLine & _
      Range("B7") & vbNewLine & _
      Range("C7") & vbNewLine & _
      "Best Regards"

      On Error Resume Next
      With xOutMail
      .To = ""
      .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
      • To post as a guest, your comment is unpublished.
        Guru · 1 months ago
        I have the exact situation but only difference is ...I have got around 100 rows to check. So it has to check each row on column D if any cell on column D meets the criteria it has to add info from column A B and C for respective D cell and after checking all 100 rows it has to send one email.
  • To post as a guest, your comment is unpublished.
    ricardo27 · 1 years ago
    I have another question, how do you write in VBA so instead of "If IsNumeric(Target.Value) And Target.Value > 200 Then" (detecting a value greater than 200 it detects any text input..... for example in cell D7 you write any words (not number but words) it calls out the email ! please respond
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Please replace the line "If IsNumeric(Target.Value) And Target.Value > 200 Then" with "If Target.Value = "test" Then"
      "test" is the specific word you will cal out the email based on, please change it to your own word.
  • To post as a guest, your comment is unpublished.
    ricardo27 · 1 years ago
    Hi

    I got a question, on excel I have a column with several emails written (they are formulated), for example A36 is example1@hotmail.com, A37 is example2@hotmail.com, A38 is example3@hotmail.com, I want that the part of the programming VBA To: Range (A36:A38) so they send the email to each of those wroten emails, the thing is that I put it like this To: Range (A36:A38) but it does not work, am I doing it wrong?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi ricardo suarez,
      The below VBA code can solve your problem. Please have a try and thank you for your comment.

      Dim xRg As Range
      'Update by Extendoffice 2019/8/14
      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
      For Each cell In Range("A36:A38")
      If cell.Value Like "?*@?*.?*" Then
      strto = strto & cell.Value & ";"
      End If
      Next cell
      If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
      Application.ScreenUpdating = False
      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 = strto
      .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
  • To post as a guest, your comment is unpublished.
    amy786 · 1 years ago
    Hi


    My vba code works fine, even the email part to outlook. I'm quite chuffed as I am a novice at vba. I created an automated process to create an invoice and email in pdf.


    However the next step is I want to include a status on the excel spreadsheet, which is my database to say "complete" or "sent". I'm thinking maybe an IF formula? I don't want to use a message box. Basically I just want to keep record that for each creditor the email has been sent.


    How would I do that in VBA


    Thanks for your help:)
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Anisa,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Roxiann · 1 years ago
    Hi. I am trying to use your code and I get the email but I want the email address I have in cell E159 to pop up as the email address. I also want to Display a cell value in the middle of my email. I have tried several ways and none seem to work. I cannot use the kudotools app. I only have excel and the vba.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Roxiann,
      Supposing you want to send email to address in cell E159, and display value of cell A1 in your email body, you can try the below VBA code. Thanks for your comment.

      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 & _
      Range("A1") & vbNewLine & _
      "This is line 2"
      On Error Resume Next
      With xOutMail
      .To = Range("E159")
      .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
  • To post as a guest, your comment is unpublished.
    Otoniel · 1 years ago
    Hi Crystal, thanks for the last answer, i got a quick question, how can i add the cell value automatically to the mailbody
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Or just include this line Range("D7") & vbNewLine & _ in the xMailBody line.

      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      "This is line 1" & vbNewLine & _
      Range("D7") & vbNewLine & _
      "This is line 2"
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Otoniel,
      Supposing the cell D7 is in the worksheet named "Sheet2", please change the xMailBody line in above code to
      xMailBody = "The value of D7 is" & ThisWorkbook.Sheets("Sheet2").Cells(7, 4).Value

      Don't forget to change the sheet name and the cell number as you need (here Cells(7,4) represents cell D7 which locationg in row 7 column 4).
  • To post as a guest, your comment is unpublished.
    Paras · 1 years ago
    This is great but just the last step is not completing my request. Could you please update so it automatically sends out the email, without clicking send.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Paras,
      In the code, please replace the .Display line with .Send. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    dalton · 1 years ago
    I am having a similar issue as has been brought up, the VBA works with manually values. I have 2 workbooks, I hope to pull data from workbook 2 into workbook1. Workbook 1 then subtracts 2 numbers, one of which is pulled from workbook2 (via using formula of =value in cell in workbook 2). Then if the difference is less than a given number, I hope to send an email. When I manually enter the number the VBA works great. When I use the = value of cell in workbook 2, VBA does not run. I feel so close but still out of arms length. Any help is greatly appreciated. Thanks much.
  • To post as a guest, your comment is unpublished.
    Otoniel · 1 years ago
    Hello is it possible, that instead of popping up the email.

    Can it be configured to automatically send the mail, without the need to send it manually?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Otoniel,
      In the code, please replace the .Display line with .Send. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Toph42 · 1 years ago
    Is there was a way to prompt the email by cells that are populated by a formula? This seems to only work when populating the cells manually. Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      The below VBA code will do you a favor.

      Dim xRg As Range
      'Update by Extendoffice 2019/8/2
      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

      Private Sub Worksheet_Calculate()
      Dim xI As Integer
      Dim xRg As Range
      Set xRg = Range("D7")
      On Error GoTo Err01
      xI = Int(xRg.Value)
      If xI > 200 Then
      Call Mail_small_Text_Outlook
      End If
      Err01:
      End Sub
  • To post as a guest, your comment is unpublished.
    Kayden · 1 years ago
    I've set this VBA to range instead of a single cell. However, I'd like to specify which cell value has triggered this email to be sent. Let say, Value on D3 is greater than 200, that triggered this email message from range (D1:E20). How can I embed this code into VBA, so I can add this to email subject? any help would be greatly appreciated!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Kayden,
      If you want to specify a range of cells rather than single cell, the below VBA code can help. But I don't really understand your last question about "so I can add this to email subject". Thanks for your comment.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      If Intersect(Target, Range("D1:E20")) = Target 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 = ""
      .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
      • To post as a guest, your comment is unpublished.
        KAYDEN · 1 years ago
        Hello Crystal,

        Thank you very much for your response! I'd like to elaborate an issue I have. I have a range of cells as "target". When email is generated, on Subject line, I'd like to add a cell that triggered this email. For instance, Qty of Mango fell below target value of 200, then I'd like to add Please order "Mango" on the subject line. Since the target is a range, I'd want some type of formula that can refer to cell B2 (Mango - name of the fruit column). I hope you could help me out on this! Thanks once again!
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          HI KAYDEN,
          Do you mean the subject line is dynamically changed based on the target value of the Qty column? When Qty of Mango fell below target value of 200, send email with subject line "Please order Mango (refers to cell B2)"; When Qty of another fruit fell below target value of 200, send email with subject line of the corresponding fruit name?
          • To post as a guest, your comment is unpublished.
            Rodrigo · 1 years ago
            I think what he's also trying to say (beside the dynamics in the subject of the email) is that by setting a range of cells that can trigger the email, you wont know what that change was and where.

            With a single cell instead of a range you'll know exactly where to look once you get the email or you can even add the value that triggered the email in the email body, as I read above by specifying the single cell you're trying to track.

            But when you specify a range, how can you add in the body of the email the value and cell or column that started the whole action? How do I know what and where the change happened?
          • To post as a guest, your comment is unpublished.
            KAYDEN · 1 years ago
            Dear Crystal,

            Yes, you are correct. I'd like to have a dynamic subject line to reflect the name of fruit! Sorry I was ambiguous on my asking, but you understood perfectly! Do you think there's a code that I can use to make this work? Thank you again for your help!
  • To post as a guest, your comment is unpublished.
    Dmytro · 1 years ago
    Hi! How should I copy/paste the code to get it to work? (after copying/pasting with line number and line text it shows up "invalid outside procedure"; copying/pasting only text lines without line numbers it does nothing after changing the cell value).
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Dmytro,
      Sorry for the inconvenience. You just need to copy the text lines without the line numbers. The code only works when the criterion met (In this case, when value in cell D7 is changed to more than 200, the code will work).
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Justin · 1 years ago
    so when you say "Automatically Send Email Based On Cell Value" you really mean make outlook pop up so you can manually press the send button? or am I missing something?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Justin,
      Replace the .Display with .Send in the code will automtically send the email as soon as the criterion met.
      Popping up the email can help readers to test the code when following the steps.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Sophie · 1 years ago
    Hi, I have used this code and I need it to send an email when its 0 but its sending emails even whe its above 0. Can you help?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Sophie,
      Please change the > 200 to =0 to make it work.Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    griffd12 · 1 years ago
    My excel workbook consist of 30+ worksheets. Each sheet is a duplicate that has same structure. I have columns that are set as validation (drop down list) and a field that is a VLOOKUP formula.

    Validation = C:5
    VLOOKUP formula is = D:5

    When I change the lookup value in C:5 it changes the value in D:5 (Note that C:5 is text not a number) Because D:5 is configured as a VLOOKUP the VB script dos not work even though cell D:5 shows the value of 2. If I delete the formula in D:5 and simple enter a number 2 then it works. How can I get this to work with the VLOOKUP cell or even by just changing the validation cell. Example if C:5 = "New" then send email. Or if C:5 changes from New to "Escalate" send an email and last if C:5 changes to "Closed" send a email??

    Also one last item is when the email is created how can the email in the subject line or in the body contain the name of the worksheet that was changed? right now i just get a email but i don't know which worksheet out of the workbook was changed.


    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("D5"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = 2 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
  • To post as a guest, your comment is unpublished.
    Derek · 1 years ago
    Hello. Using your VB Script works when the value I enter is done manually. My cell is a VLOOKUP formula. The VB script to send email based on cell D5 but D5 is a formula not a manual entry number. How can I get this to work with a VLOOKUP cell?

    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("D5"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = 1 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
  • To post as a guest, your comment is unpublished.
    Derek · 1 years ago
    Hello. First let me thank you for your information on this topic. it's been very helpful. I have several sheets in a single excel workbook. On Each sheet there will be 3 VB click boxes that will send an email. What I am wondering is, how can I include the sheet name in the email/VB code? Below is my code that i am using for the 3 click boxes (note I just change some of email body and subject.

    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 = "Hello" & vbNewLine & vbNewLine & _
    "Please note that a new software case has been added." & vbNewLine & _
    "Please review and follow-up with customer service rep for status updates"
    On Error Resume Next
    With xOutMail
    .To = "email address"
    .CC = ""
    .BCC = ""
    .Subject = "A New Software Case Has Been Added"
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub
  • To post as a guest, your comment is unpublished.
    Janelle · 1 years ago
    Hi, I am trying to write a VBA for email from excel. Basically if an individual's name is selected in the corresponding cell excel will trigger an email to that person. I have created a reference list of names and emails that are being used for data validation.

    I also want to return particular values in the email body that are relevant to the row number the name is entered to.

    e.g R9C21 (U9) = Joe Bloggs. Email joe.bloggs@bloggs.com >>>>>>>>>>> This value must be sourced from the data validation list (one of multiple name and email address combinations.

    As a result. Excel opens an email and the email body will be populated with the following cells

    R9C12 & "Random Text" & R9C10

    The email process should be triggered based on any name value entered in C[21] and the email body must contain cell values from the corresponding row.

    This is what I have. This does not allow for multiple name options, rows or allow me to enter cell references in the email body:

    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("R9C21"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value = "Name" 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 & _
    "Test"
    On Error Resume Next
    With xOutMail
    .To = "Email"
    .CC = "Email"
    .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
  • To post as a guest, your comment is unpublished.
    asint · 1 years ago
    how if there are several cells, each cell has a different value
  • To post as a guest, your comment is unpublished.
    MrBain · 1 years ago
    Hello,

    Would you kindly help me with the below?

    I am trying to use the below code, however, whenever I use the Range("A" & xCell.Row) to add a cell value in the Subject or Body of the email it blanks out the whole thing, if I only introduce "Text" it works fine.

    Column A has cells that get their value from another workbook through a simple formula (=IF('[TestWorkbook.xlsm]Sheet1'!C2="1",'[TestWorkbook.xlsm]Sheet1'!A2,"")). Would this cause the issue, seeing how cells in A:A get their value through a formula rather than manually imputed values?

    Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    If (Target.Count > 1) Then Exit Sub
    Set xRg = Intersect(Target, Range("L:L"))
    If xRg Is Nothing Then Exit Sub
    If UCase(Target.Value) = "P" Then
    Call Mail_small_Text_Outlook(Target)
    End If
    End Sub
    Sub Mail_small_Text_Outlook(ByVal xCell As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello Team" & vbNewLine & vbNewLine & _
    "Case " & Range("A" & xCell.Row) & " has been read and acknowledged"
    On Error Resume Next
    With xOutMail
    .To = "Email Address1; Email Address2; Email Address3; Email Address4; Email Address5"
    .CC = ""
    .BCC = ""
    .Subject = "Case " & Range("A" & xCell.Row)
    .Body = xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End Sub


    Thank you!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Sorry can't help you with that.
      Welcome to post any question in our forum: https://www.extendoffice.com/forum.html to get more Excel support from out Excel professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    glennwensley42@gmail.com · 1 years ago
    This is great for a single cell, but I would suggest a more useful function would be to have multiple instances where cells (within a range of cells on the same column) reach the same specific value that generates an automated email. However, Supposing you have a column with a range B2:B10, and want to generate an auto-email when a specific value (say the number 4) occurs anywhere within the column? Thus, if the value of 4 is achieved in Cell B3 and B7. Your code will generate an auto-email at B3 and B7, when what we really want is to generate an auto-email at B3, and then later, when B7 reaches the same value of 4, another email is automatically sent, but not one for B3. The instance for B3 is in the past and an email has been automatically sent for that instance. We don't want another email to be auto-generated for it when the value of 4 is reached further down the ranged column.

    What line of code would achieve this?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      For applying the code to a range of cells in a column, please change the specified cell D7 to the range of cells such as B2:B10.