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

or

Come inviare automaticamente e-mail in base al valore della cella in Excel?

Supponendo di voler inviare un'e-mail tramite Outlook a un determinato destinatario in base a un valore di cella specificato in Excel. Ad esempio, quando il valore della cella D7 in un foglio di lavoro è maggiore di 200, viene creata automaticamente un'e-mail. Questo articolo introduce un metodo VBA per risolvere rapidamente questo problema.

Invia automaticamente e-mail in base al valore della cella con codice VBA


Invia automaticamente e-mail in base al valore della cella con codice VBA

Si prega di fare quanto segue per inviare un'e-mail in base al valore della cella in Excel.

1. Nel foglio di lavoro è necessario inviare e-mail in base al valore della cella (qui dice la cella D7), fare clic con il pulsante destro del mouse sulla scheda del foglio e selezionare Visualizza codice dal menu contestuale. Vedi screenshot:

2. Nel spuntare Microsoft Visual Basic, Applications Edition finestra, copia e incolla il codice VBA sottostante nella finestra del codice del foglio.

Codice VBA: invia e-mail tramite Outlook in base al valore della cella 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

Note:

1. Nel codice VBA, D7 e valore> 200 sono la cella e il valore della cella su cui invierai l'email.

2. Si prega di modificare il corpo dell'email come necessario xMailBody riga nel codice.

3. Sostituire l'indirizzo e-mail con l'indirizzo e-mail del destinatario in linea .To = "Indirizzo e-mail".

4. E specifica i destinatari Cc e Ccn di cui hai bisogno .CC = "" e Ccn = "" .

5. Infine cambia l'oggetto dell'email in linea .Subject = "invia tramite test valore cella".

3. premi il altro + Q i tasti insieme per chiudere il file Microsoft Visual Basic, Applications Edition finestra.

D'ora in poi, quando il valore immesso nella cella D7 è maggiore di 200, verrà creata automaticamente un'e-mail con i destinatari e il corpo specificati in Outlook. Puoi fare clic su Invia pulsante per inviare questa email. Vedi screenshot:

Note:

1. Il codice VBA funziona solo quando si utilizza Outlook come programma di posta elettronica.

2. Se i dati inseriti nella cella D7 sono un valore di testo, verrà visualizzata anche la finestra di posta elettronica.


Invia facilmente e-mail tramite Outlook in base ai campi della mailing list creata in Excel:

Il progetto Inviare emails utilità di Kutools for Excel aiuta gli utenti a inviare e-mail tramite Outlook in base alla mailing list creata in Excel.
Scaricalo e provalo subito! (30 giorni di percorso gratuito)


Articoli correlati:


I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

  • Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Più di 300 potenti funzionalità. Supporta Office / Excel 2007-2019 e 365. Supporta tutte le lingue. Facile distribuzione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
scheda kte 201905

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce centinaia di clic del mouse ogni giorno!
fondo 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