Note: The other languages of the website are Google-translated. Back to English
Belépek  \/ 
x
or
x
Regisztráció  \/ 
x

or

Hogyan küldhetünk automatikusan e-mailt az Excel cellája alapján?

Tegyük fel, hogy e-mailt szeretne küldeni az Outlook segítségével egy bizonyos címzettnek az Excel megadott cellája alapján. Például, ha a munkalap D7 cellájának értéke nagyobb, mint 200, akkor automatikusan létrejön egy e-mail. Ez a cikk egy VBA-módszert mutat be a probléma gyors megoldására.

Automatikus e-mail küldése a cella értéke alapján, VBA kóddal


Automatikus e-mail küldése a cella értéke alapján, VBA kóddal

Kérjük, tegye a következőket, ha e-mailt szeretne küldeni az Excel cellasejtje alapján.

1. A munkalapon e-mailt kell küldeni a cellaértéke alapján (itt a D7 cella áll), kattintson a jobb gombbal a lap fülre, majd Kód megtekintése a helyi menüből. Lásd a képernyőképet:

2. A felbukkanóban Microsoft Visual Basic for Applications ablakba, kérjük, másolja és illessze be az alábbi VBA kódot a lapkód ablakba.

VBA kód: E-mail küldése az Outlook segítségével az Excel cellája alapján

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

Megjegyzések:

1. A VBA kódban D7 érték> 200 azok a cellák és cellák értékei, amelyek alapján e-mailt küldeni fog.

2. Kérjük, módosítsa az e-mail törzsét, amire szüksége van xMailBody sor a kódban.

3. Cserélje ki az e-mail címet a címzett e-mail címére a sorban .To = "E-mail cím".

4. És adja meg a Másolat és Titkos másolat címzetteket, amire szüksége van .CC = “” Titkos másolat = „” szakaszok.

5. Végül sorban módosítsa az e-mail tárgyát .Subject = "küldés cellateszt-teszt segítségével".

3. megnyomni a más + Q billentyűk együttes bezárásához Microsoft Visual Basic for Applications ablak.

Mostantól, amikor a D7 cellába beírt érték meghaladja a 200 értéket, a rendszer automatikusan létrehoz egy e-mailt a megadott címzettekkel és törzssel az Outlookban. Kattintson a gombra Küldés gombra az e-mail elküldéséhez. Lásd a képernyőképet:

Megjegyzések:

1. A VBA kód csak akkor működik, ha az Outlook programot használja e-mail programként.

2. Ha a D7 cellában megadott adatok szöveges értékek, akkor az e-mail ablak is kiugrik.


Könnyen küldhet e-mailt az Outlook segítségével az Excelben létrehozott levelezőlista mezői alapján:

A Emaileket küld hasznossága Kutools for Excel segíti a felhasználókat az Outlook segítségével az Excel létrehozott levelezőlista alapján.
Töltse le és próbálja ki most! (30 napos ingyenes nyomvonal)


Kapcsolódó cikkek:


A legjobb irodai termelékenységi eszközök

A Kutools for Excel megoldja a legtöbb problémát, és 80% -kal növeli a termelékenységet

  • újrafelhasználás: Gyorsan helyezze be összetett képletek, diagramok és bármi, amit korábban használt; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése az adatok elvesztése nélkül; Osztott cellák tartalma; Kombinálja a duplikált sorokat / oszlopokat... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Több mint 300 hatékony funkció. Támogatja az Office / Excel 2007-2019 és 365. Támogatja az összes nyelvet. Könnyen telepíthető a vállalkozásba vagy szervezetbe. 30 napos ingyenes próbaverzió. 60 napos pénzvisszafizetési garancia.
kte tab 201905

Az Office fül a füles felületet hozza az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és minden nap több száz kattintással csökkenti az egér kattintását!
officetab alja
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.
    fifi · 2 years ago
    hello, can u help me
    1. send this email with attach the worksheet
    2. set timer automatically send this email weekly (like every sunday)


    thanks
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @jason Dear Jason,
    The following VBA code can help you solve the problem. Thank you.

    Dim xRg As Range
    'Update by Extendoffice 2018/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 " & vbNewLine & "value is " & xRg.Value
    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.
    crystal · 2 years ago
    @kevin b Hello,
    Please try below VBA code, when the specified cell meets the condition, a dialog will pop up, please select the cell contains the email address you will sent email to. Hope it can help. Thank you.

    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.
    crystal · 2 years ago
    @basha Hello,
    We have posted an article "How to send email if due date has been met in Excel?"
    You can see if there has answers in this article. Please follow this link to open the article: https://www.extendoffice.com/documents/excel/4664-excel-send-email-if-due-date-has-been-met.html
    Thank you.
  • To post as a guest, your comment is unpublished.
    jason · 3 years ago
    hello, sir: may i ask how to display the value in D7 in the out bouncing email automatically ?
    thanks
    jason
  • To post as a guest, your comment is unpublished.
    kevin b · 3 years ago
    Hello- If I wanted to send to an email from a list instead of putting actual email addy in the code, is that possible? thanks
  • To post as a guest, your comment is unpublished.
    basha · 3 years ago
    hi crystal
    thanks for your codes, if possible kindly send the codes for below given details

    if we have 8 to 9 coloums using different type of expires like passport expiry date, driving licence expiry date, vehicle registration expiry date, gate pass expiry date and more etc., and mail alert must send to only 5 given persons.

    like our date sheet is with more than 300 employees, expired and expiry date with in 15 days in red colour and email alert should sent.

    kindly do the needful

    thanks in advance
  • To post as a guest, your comment is unpublished.
    Jermaine · 3 years ago
    @crystal Crystal,

    This replaces the following code:

    Sub email()

    Dim xRg As Range

    Dim xRgEach As Range

    Dim xEmail_Subject, xEmail_Send_Form,;etc.
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @alely Hi Noemi,
    Please try this VBA scrip.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    Dim I, J, K As Long
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    If Target.Address = Range("D7").Address Then
    With Application.WorksheetFunction
    If IsNumeric(Target.Value) And Target.Value > 200 Then
    Set xRg = Application.InputBox("Please select the cell range you will display in the mail body:", "KuTools for Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    For I = 1 To xRg.Rows.Count
    For J = 1 To xRg.Rows(I).Columns.Count
    For K = 1 To xRg.Rows(I).Columns(J).Count
    xMailBody = xMailBody & " " & xRg.Rows(I).Columns(J).Cells(K).Text
    Next
    Next
    xMailBody = xMailBody & vbNewLine
    Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = "Hi there " & vbNewLine & xMailBody
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End If
    End With
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @Jan Dear Jan,
    The following script can help you solve the problem. Thank you for your comment.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    If Target.Address = Range("D7:F7").Address Then
    With Application.WorksheetFunction
    If .CountIf(Target, "") > 0 Or .CountIf(Target, 0) > 0 Then
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    With xOutMail
    .To = "Email Address"
    .CC = ""
    .BCC = ""
    .Subject = "send by cell value test"
    .Body = "Hi there "
    .Display 'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    End If
    End With
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @MC Dear Ann,
    Please try the below VBA code. Thank you for your comment.

    Sub Mail_small_Text_Outlook()
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim xRows As Long
    Dim xCols 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("Select the range contains the cell value you will send emails based on:", "Kutools for Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xRows = xRg.Rows.Count
    xCols = xRg.Columns.Count
    For I = 1 To xRows
    Set xCell = xRg(I, xCols)
    If xCell.Value = "Yes" Then
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
    "This is your information: " & vbNewLine & xCell.Offset(0, -1).Text & vbNewLine & xCell.Offset(0, -2).Text
    With xOutMail
    .To = xCell.Offset(0, -4).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.
    alely · 3 years ago
    So I used your edit to include a cell ranges but (if we are using the worksheet example) I was wondering how to add the type of fruit, the Date, and the quantity into the HTML email from the worksheet if they fit the criteria to have an email generated. So it would say

    "Hi there,"

    Fruit name from cell "Needs to be put on back order because as of order date: " order date from cell "we have this amount:" quantity from cell.
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @Jesse Dear Jesse,
    The following VBA code can help you solve the problem. Thank you for your comment.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If (Not Intersect(Target, Range("L:L")) Is Nothing) And (Target.Value = "completed") 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.
    crystal · 3 years ago
    @Moorhen Dear martyn,
    Sorry can't help with this.
    You can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our technical staff.
    Thank you for your comment.

    Best Regards,
    Crystal
  • To post as a guest, your comment is unpublished.
    Jan · 3 years ago
    Hi,
    I would like Outlook to pop out only when the data I have pasted into the Range ("D7:F7") has at least 1 zero or a blank.
    I have removed the 'If Target.Cells.Count > 1 Then Exit Sub' line and now Outlook always launches when i paste any group of values into cells D7:F7.

    Help.
  • To post as a guest, your comment is unpublished.
    Jesse · 3 years ago
    Hi,


    What if I wanted to send the email based off of the word "completed" being added to column L?
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @hanizah223@gmail.com 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.
    Moorhen · 3 years ago
    Hi there,


    I currently having a little trouble this the coding (new to this - may have bitten off more than I can chew)


    I currently have a spreadsheet with the following that I need help to automate and send email for fault that are at our properties for our business


    I currently need a code that will do use the following data:


    1) An address and the issue ( 2 "general" cells that have been merged via ((In cell D1)) " = =CONCAT(B1," "C1,) "
    The address in B1 will allways be same (more or less)
    Whilst C1 will always be changing dependant on the fault at the property.


    2) An email to be sent by the same email adress, ( can I use $E$1 or I have to use E1 - E1 . for example) or can I just Input " TheEmailAdress@.co.uk" in the line of code


    3) The email body to be populated in the similar way to point 1) ...... ((In cell F1)) " =CONCAT(G1," ",H1)
    These will be changing constantly as they represent the company (G1) and what they are doing , fixing, quoting ect (H1)

    4) The trigger to send the email off, I would be the number 7 , the sheet gets updated daily (7 days in a week)
    as such I need the trigger to send the email on day 7, but no constantly like on day 8, 9 , 10+ ect. and not before such as 1-6, this would be in A4 : A 100+ (as we are constantly expanding


    4) I've used small snippets from other users who mentioned about using a list for the trigger to send the email, but not sure was 100 % it was correct, but i'd need it to scan though all Collum A.... A4: A100
    and if there are 47 cells that contain only " 7 " then 47 Emails will be sent


    Thank you ever so much for reading and I hope you can help :)
  • To post as a guest, your comment is unpublished.
    Savy · 3 years ago
    @crystal worked perfectly fine.. Thank you..:):)
  • To post as a guest, your comment is unpublished.
    MC · 3 years ago
    Hi

    Thank you so much for posting this VBA Code and instructions. When I found it I felt like I had won the lotto. However I am stuck on something so I'm hoping you can help (I'm new to VBA, only have very basic understanding).

    I've copied the code and changed the cell and cell value to pick from a range if a criteria is met. I have tried and tested and it works and I received an email to outlook based on the criteria.

    1) However, I cannot seem to figure out how to get the VBA code to run automatically when I open up the excel worksheet, rather than having to click on the VBA application and select run. Could you advise if there is an additional prompt to type into the VBA code above that will do this or does it have to be done separately.

    2) Also is there a way to get the VBA code to send a mail to a person if the due date is yes for a certain item as shown in example below.
    email hidden column
    Name

    Procedure
    Procedure no.1 due date yes
    Procedure no. 2 due date no

    I would have numerous people in the spreadsheet (going across horizontally in a row) and 'Yes' could be highlighted for various overdue procedures (listed vertically in column A. Is there a way to create a VBA code that runs for something like this - if 'Yes' for 'Person 1', then email 'person 1' with 'procedure no #' (or numbers) and due date(s). Being able to list in the email all the procedures and their subsequent due dates.

    I wouldn't mind if I had to set a separate VBA code for each person as long as it sent a mail of all the documents overdue for that person and the due dates.

    Hoping you can help
  • 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
    @Savy 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.
    crystal · 3 years ago
    @New2Excel 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.
    crystal · 3 years ago
    @vj.mayank@gmail.com 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.
    crystal · 3 years ago
    @Dhruv 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.
    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.
    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.
    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.
    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
    @fdh1201 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.
    crystal · 3 years ago
    @Jose Manuel 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.
    crystal · 3 years ago
    @basil Dear basil,
    Is there any warning when running the code?
  • 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.
    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.
    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
    @Shawn Henry 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.
    crystal · 3 years ago
    @Brahma 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.
    Herrera5238 · 3 years ago
    @crystal 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.
    Brahma · 3 years ago
    will it be sent automatically mail, without any manual interruption
  • 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
    @Jordan 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.
    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
    @Debbie 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
  • 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?