Note: The other languages of the website are Google-translated. Back to English
Autentificare  \/ 
x
or
x
Înregistrare  \/ 
x

or

Cum să trimiteți automat e-mailuri pe baza valorii celulei din Excel?

Presupunând că doriți să trimiteți un e-mail prin Outlook unui anumit destinatar pe baza unei valori de celulă specificate în Excel. De exemplu, când valoarea celulei D7 într-o foaie de lucru este mai mare de 200, atunci se creează automat un e-mail. Acest articol introduce o metodă VBA pentru a rezolva rapid această problemă.

Trimiteți automat e-mail pe baza valorii celulei cu cod VBA


Trimiteți automat e-mail pe baza valorii celulei cu cod VBA

Vă rugăm să procedați după cum urmează pentru a trimite un e-mail pe baza valorii celulei din Excel.

1. În foaia de lucru trebuie să trimiteți e-mail pe baza valorii sale de celulă (aici spune celula D7), faceți clic dreapta pe fila foaie și selectați Afișați codul din meniul contextual. Vedeți captura de ecran:

2. În fereastra pop-up Microsoft Visual Basic pentru aplicații fereastra, vă rugăm să copiați și să lipiți codul VBA de mai jos în fereastra codului foii.

Cod VBA: trimiteți e-mail prin Outlook pe baza valorii celulei din 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

notițe:

1. În codul VBA, D7 și valoare> 200 sunt celula și valoarea celulei pe care veți trimite e-mail pe baza.

2. Vă rugăm să schimbați corpul e-mailului după cum aveți nevoie xMailBody linie în cod.

3. Înlocuiți adresa de e-mail cu adresa de e-mail a destinatarului în linie .To = "Adresă de e-mail".

4. Și specificați destinatarii Cc și Bcc după cum doriți .CC = „” și Cco = „” secțiuni.

5. În cele din urmă, schimbați subiectul e-mailului în linie .Subject = "trimite prin testul valorii celulei".

3. apasă pe Alt + Q tastele împreună pentru a închide Microsoft Visual Basic pentru aplicații fereastră.

De acum înainte, când valoarea pe care o introduceți în celula D7 este mai mare de 200, un mesaj de e-mail cu destinatarii și corpul specificați va fi creat automat în Outlook. Puteți face clic pe Trimite pentru a trimite acest e-mail. Vedeți captura de ecran:

notițe:

1. Codul VBA funcționează numai atunci când utilizați Outlook ca program de e-mail.

2. Dacă datele introduse în celula D7 sunt o valoare text, va apărea și fereastra de e-mail.


Trimiteți ușor e-mail prin Outlook pe baza câmpurilor listei de discuții create în Excel:

Trimite emailuri utilitatea Kutools pentru Excel ajută utilizatorii să trimită e-mailuri prin Outlook pe baza listei de corespondență create în Excel.
Descărcați și încercați acum! (Traseu gratuit de 30 de zile)


Legate de articole:


Cele mai bune instrumente de productivitate Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

  • reutilizarea: Introduceți rapid formule complexe, diagrame și orice ai folosit anterior; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
  • Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
  • Merge celule / rânduri / coloane fără a pierde date; Conținut de celule divizate; Combinați rânduri / coloane duplicate... Prevenirea celulelor duplicate; Comparați gamele...
  • Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
  • Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
  • Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
  • Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
  • Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
  • Peste 300 de funcții puternice. Suportă Office / Excel 2007-2019 și 365. Suportă toate limbile. Implementare ușoară în întreprindere sau organizație. Funcții complete de încercare gratuită de 30 de zile. Garanție de restituire a banilor de 60 de zile.
fila kte 201905

Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse în fiecare zi!
fundul officetab
Say something here...
symbols left.
You are guest
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.
    hanizah223@gmail.com · 3 years ago
    how to stop code from running ie don't prompt the email when condition is not met?

    even when D7 < 200, I still get prompted the email.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      The code is updated in the post with the problem solved. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Savy · 3 years ago
    How can you add Multiple Range to "Set xRg = Range("D7")". I want to edit it and add Range("D7:F7"). However i am getting an error of Run Time Error 13, Type Mismatch and it is taking me to If xRg = Target And Target.Value > 2 Then.


    How can i solve this proble?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      Please try below VBA code to solve the problem.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      If (Not Intersect(Target, Range("D7:F7")) Is Nothing) 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 = "Your recipient's 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.
        Nitol · 2 years ago
        It is not working for me as the value in D7 is a result of a formual. What if cell D7 contains a formula, e.g. D7 =2*120? It still meets the condition but nothing is happening. Please help
      • To post as a guest, your comment is unpublished.
        Savy · 3 years ago
        worked perfectly fine.. Thank you..:):)
  • To post as a guest, your comment is unpublished.
    Doug · 3 years ago
    How can I edit the code to send an email based on a date in the cell. For example, I need a document reviewed every 15 months and I want to kick out an email at 12 months to an email address saying the document needs to be reviewed. I've got it now to auto-send an email by changing .Display to .Send and it works great as written, but what do I need to change to use a date function instead of a whole number??
  • To post as a guest, your comment is unpublished.
    New2Excel · 3 years ago
    Hello what code would I use if I am trying to send an email to a manager that has a list of the fruit that has a quantity > 200 once per month (based on your example) or expires soon( based on dates)
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day
      May be the method in this article "How to send email if due date has been met in Excel?" can help you.
      Please follow this link: 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.
    vj.mayank@gmail.com · 3 years ago
    I am having trouble sending mail through outlook. I receive the error saying "A program is trying to send an email on your behalf. If it is unexpected, please deny and verify your anti-virus software is up to date"
    Please help as I am not able to automate it.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Sorry mayank,
      The code works well in my case. It seems that something about "send on behalf" function is configured in your Outlook. Pease check for it.
  • To post as a guest, your comment is unpublished.
    Dhruv · 3 years ago
    I have a list of email addresses already in an excel file, how can I modify the code to automatically choose the email address of the person if his cell D7 is >200?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      The following VBA code can help you solve the problem. Please place the VBA script into your worksheet module. When value in the specified cell meet the condition, a Kutools for Excel dialog box will pop up, please select the cells which contain the recipients' email addresses and then click the OK button. Then emails with specified recipients are opening. Please send them as you need.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      Set xRg = Range("D7")
      If xRg = 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
      Dim xRgMsg As Range
      Dim xCell As Range
      Set xRgMsg = Application.InputBox("Please select the address cells:", "Kutools for Excel", , , , , , 8)
      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      "This is line 1" & vbNewLine & _
      "This is line 2"
      On Error Resume Next
      For Each xCell In xRgMsg
      Set xOutApp = CreateObject("Outlook.Application")
      Set xOutMail = xOutApp.CreateItem(0)
      With xOutMail
      .To = xCell.Value
      .CC = ""
      .BCC = ""
      .Subject = "send by cell value test"
      .Body = xMailBody
      .Display 'or use .Send
      End With
      xOutApp = Nothing
      xOutMail = Nothing
      Next
      On Error GoTo 0
      End Sub
  • To post as a guest, your comment is unpublished.
    fdh1201 · 3 years ago
    How could I change this code for sending student grades to parents. Where if column A is the grade and Column B is the parent email. I want to populate an email for each student with an F as a grade.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Frank,
      The below VBA code can help you solve the problem. Thank you.

      Sub Mail_small_Text_Outlook()
      Dim xRg As Range
      Dim I As Long
      Dim xRows As Long
      Dim xVal As String
      Dim xOutApp As Object
      Dim xOutMail As Object
      Dim xMailBody As String
      On Error Resume Next
      Set xRg = Application.InputBox("Please select grade column and the email column (two columns)", "Kutools for Excel", Selection.Address, , , , , 8)
      If xRg Is Nothing Then Exit Sub
      xRows = xRg.Rows.Count
      Set xRg = xRg(2)
      For I = 1 To xRows
      xVal = xRg.Offset(I, -1).Text
      If xVal = "F" Then
      Set xOutApp = CreateObject("Outlook.Application")
      Set xOutMail = xOutApp.CreateItem(0)
      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      "This is your child's grade " & xRg.Offset(I, -1).Text
      With xOutMail
      .to = xRg.Offset(I, 0).Text
      .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 If
      Next
      End Sub
  • To post as a guest, your comment is unpublished.
    Jose Manuel · 3 years ago
    Hello, how would you modify this code to check wether a group of cells have the string "No match" and send an email if it has.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Jose,
      Please try below VBA code. When running the code, a dialog box pops up, please select the range you will check for string, and click the OK button. if the string does not exist, you will get a prompt dialog box. If the string exists in the range, an email with specified recipient, subject and body will display.

      Sub SendEmail()
      Dim I As Long
      Dim J As Long
      Dim xRg As Range
      Dim xArr
      Dim xOutApp As Object
      Dim xOutMail As Object
      Dim xMailBody As String
      Dim xFlag As Boolean
      On Error Resume Next
      Set xRg = Application.InputBox("Please select range", "Kutools for Excel", Selection.Address, , , , , 8)
      If xRg Is Nothing Then Exit Sub
      xArr = xRg.Value
      xFlag = False
      For I = 1 To UBound(xArr)
      For J = 1 To UBound(xArr, 2)
      If xArr(I, J) = "No Match" Then
      xFlag = True
      End If
      Next
      Next
      If xFlag Then
      Set xOutApp = CreateObject("Outlook.Application")
      Set xOutMail = xOutApp.CreateItem(0)
      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      "This is line 1" & vbNewLine & _
      "This is line 2"
      With xOutMail
      .To = "Email address"
      .CC = ""
      .BCC = ""
      .Subject = "Match"
      .Body = xMailBody
      .Display 'or use .Send
      End With
      Else
      MsgBox "Found No matched value", vbInformation, "KuTools for Excel"
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    basil · 3 years ago
    Hi I put the same script but it is not working please help me in the 1st part

    Dim xRg As Range

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Set xRg = Range("D7")
    If xRg = Target And Target.Value = 200 Then
    Call Mail_small_Text_Outlook
    End If

    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear basil,
      Is there any warning when running the code?
  • To post as a guest, your comment is unpublished.
    Brahma · 3 years ago
    will it be sent automatically mail, without any manual interruption
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Brahma,
      If you want to directly send the email without displaying, please replace the line ".Display" with ".Send" in the above VBA code.
  • To post as a guest, your comment is unpublished.
    Shawn Henry · 3 years ago
    Hello

    I am having trouble because Email recipient has to be added again and again one by one. Please guide if list of email recipients can be added to this function so the the function will select the email address from the list of email addresses provided or list upload and the function sends the email, already composed to the desired recipient.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Henry,
      The following VBA code can help you solve the problem. Please place the VBA script into your worksheet module. When value in the specified cell meet the condition, a Kutools for Excel dialog box will pop up, please select the cells which contain the recipients' email addresses and then click the OK button. Then emails with specified recipients are opening. Please send them as you need.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      Set xRg = Range("D7")
      If xRg = 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
      Dim xRgMsg As Range
      Dim xCell As Range
      Set xRgMsg = Application.InputBox("Please select the address cells:", "Kutools for Excel", , , , , , 8)
      xMailBody = "Hi there" & vbNewLine & vbNewLine & _
      "This is line 1" & vbNewLine & _
      "This is line 2"
      On Error Resume Next
      For Each xCell In xRgMsg
      Set xOutApp = CreateObject("Outlook.Application")
      Set xOutMail = xOutApp.CreateItem(0)
      With xOutMail
      .To = xCell.Value
      .CC = ""
      .BCC = ""
      .Subject = "send by cell value test"
      .Body = xMailBody
      .Display 'or use .Send
      End With
      xOutApp = Nothing
      xOutMail = Nothing
      Next
      On Error GoTo 0
      End Sub
  • To post as a guest, your comment is unpublished.
    Jordan · 3 years ago
    I am having trouble getting this code to prompt if the value in the cell is changed indirectly. For example, if I have Sum equation changing this value automatically. When the equation runs and the value goes above the set value to prompt the email, it does not do so, unless I physically change the number myself. Is there a way to make the email prompt even if changed indirectly?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Jordan,
      The following VBA code can help you solve the problem. Please don't forget to replace the "Email Address" with the recipient's email address in the code. Thank you.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim xRgPre As Range
      On Error Resume Next
      If Target.Cells.Count > 1 Then Exit Sub
      Set xRg = Range("D7")
      Set xRgPre = xRg.Precedents
      If xRg.Value > 200 Then
      If Target.Address = xRg.Address Then
      Call Mail_small_Text_Outlook
      ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
      Call Mail_small_Text_Outlook
      End If
      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.
        Jim · 2 years ago
        I used this code with the only change being I have applied it to an entire column [Set xRg = Range("D4:D13")]. Now the event triggers whenever a calculation is made regardless of whether the valve in Column D is below the target value. Any idea's why that is?


        Dim Xrg As Range
        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim xRgPre As Range
        On Error Resume Next
        If Target.Cells.Count > 1 Then Exit Sub
        Set Xrg = Range("D4:D13")
        Set xRgPre = Xrg.Precedents
        If Xrg.Value < 1200 Then
        If Target.Address = Xrg.Address Then
        Call Mail_small_Text_Outlook
        ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
        Call Mail_small_Text_Outlook
        End If
        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" & vbNewLine & _
        "Test vba" _
        & vbNewLine & _
        "Line 2."
        On Error Resume Next
        With xOutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Auto Email Test"
        .Body = xMailBody
        .Display
        End With
        On Error GoTo 0
        Set xOutMail = Nothing
        Set xOutApp = Nothing

        End Sub


        Thanks.
      • To post as a guest, your comment is unpublished.
        Herrera5238 · 3 years ago
        I've modified suggested code to try to make it work for my application.
        Changed xRg = Range("C2:C40") and If xRg.Value = -1.

        The issue that I'm having is anytime there is a change to any cell and as long as one of the cells in my range is = -1 it will call Mail_small_Text_Outlook.
        I'm trying to only call if any cell in my range is changed indirectly to -1.
        I was also wondering if and how it would be possible to have it meet two criteria.
        Like check range A and range B and if they meet criteria call function.

        Thanks in advance for the help. I'm new to all this but reading through this thread has me about 90% there.


        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim xRgPre As Range
        On Error Resume Next
        If Target.Cells.Count > 1 Then Exit Sub
        Set xRg = Range("C2:C40")
        Set xRgPre = xRg.Precedents
        If xRg.Value = -1 Then
        If Target.Address = xRg.Address Then
        Call Mail_small_Text_Outlook
        ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
        Call Mail_small_Text_Outlook
        End If
        End If
        End Sub
  • To post as a guest, your comment is unpublished.
    Debbie · 3 years ago
    How should the code be modified, to apply to an entire range of cells?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Debbie,
      Please try below VBA code to solve the problem.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      If (Not Intersect(Target, Range("A1:D4")) Is Nothing) 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 = "Your recipient's 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