Jak automaticky odesílat e-maily na základě hodnoty buňky v aplikaci Excel?

Předpokládejme, že chcete odeslat e-mail prostřednictvím aplikace Outlook určitému příjemci na základě zadané hodnoty buňky v aplikaci Excel. Například když je hodnota buňky D7 v listu větší než 200, automaticky se vytvoří e-mail. Tento článek představuje metodu VBA pro rychlé vyřešení tohoto problému.

Automaticky odesílat e-maily na základě hodnoty buňky s kódem VBA

Automaticky odesílat e-maily na základě hodnoty buňky s kódem VBA

Chcete-li odeslat e-mail na základě hodnoty buňky v aplikaci Excel, postupujte takto.

1. V listu musíte odeslat e-mail na základě jeho hodnoty buňky (zde se uvádí buňka D7), klepněte pravým tlačítkem na kartu listu a vyberte Zobrazit kód z kontextové nabídky. Viz snímek obrazovky:

2. Ve vyskakovacím okně Microsoft Visual Basic pro aplikace zkopírujte a vložte níže uvedený kód VBA do okna kódu listu.

Kód VBA: Odesílejte e-maily prostřednictvím Outlooku na základě hodnoty buňky v aplikaci 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

Poznámky:

1. V kódu VBA, D7 a hodnota> 200 jsou hodnota buňky a buňky, na které budete posílat e-maily.

2. Změňte prosím tělo e-mailu tak, jak potřebujete xMailBody řádek v kódu.

3. Vyměňte e-mailovou adresu za řádek s e-mailovou adresou příjemce .To = "E-mailová adresa".

4. A zadejte příjemce Cc a Bcc, jak potřebujete .CC = “” a Skrytá kopie = “” sekce.

5. Nakonec změňte předmět e-mailu v řádku .Subject = "odeslat testem hodnoty buňky".

3. zmáčkni Další + Q klávesy společně zavřete Microsoft Visual Basic pro aplikace okno.

Od této chvíle, když je hodnota zadaná v buňce D7 větší než 200, bude v aplikaci Outlook automaticky vytvořen e-mail se zadanými příjemci a tělem. Můžete kliknout na Poslat tlačítko pro odeslání tohoto e-mailu. Viz screenshot:

Poznámky:

1. Kód VBA funguje, pouze když používáte Outlook jako svůj e-mailový program.

2. Pokud jsou zadaná data v buňce D7 textovou hodnotou, otevře se také e-mailové okno.

Společnost Odeslat e-maily užitečnost Kutools pro Excel pomáhá uživatelům odesílat e-maily prostřednictvím Outlooku na základě vytvořeného seznamu adres v aplikaci Excel.
Stáhněte si a vyzkoušejte hned! (30denní trasa zdarma)

Související články:

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2019 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Kompletní funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.

Čtěte více... Stažení zdarma... Nákup...

Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
Zvyšuje vaši produktivitu o 50% a omezuje stovky kliknutí myší každý den!

Čtěte více... Stažení zdarma... Nákup...

205 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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.
ricardo27 · 2 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?
- Reply
- 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
  
  Reply
To post as a guest, your comment is unpublished.
amy786 · 2 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:)
- Reply
- 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.
  
  Reply
To post as a guest, your comment is unpublished.
Roxiann · 2 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.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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
  
  Reply
To post as a guest, your comment is unpublished.
Otoniel · 2 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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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"
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  otorodriguez26@gmail.com · 2 years ago
  
  Thank you very much ! Crystal
  
  Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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).
  
  Reply
To post as a guest, your comment is unpublished.
Paras · 2 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.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Paras,
  In the code, please replace the .Display line with .Send. Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
dalton · 2 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.
- Reply
To post as a guest, your comment is unpublished.
Otoniel · 2 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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Otoniel,
  In the code, please replace the .Display line with .Send. Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Toph42 · 2 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!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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
  
  Reply
To post as a guest, your comment is unpublished.
Kayden · 2 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!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  KAYDEN · 2 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!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 2 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?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Corben Baxter · 9 months ago
  
  Hi Crystal,
  
  When targeting a range of cells... how do you get the automated email to call out "identify" the particular cell that generated the email. I am working on a usage based forklift PM spreadsheet that changes column E to read PM NEEDED after 300hrs. We currently have 27 forklifts (cells E3;E49), your code has worked for me in triggering the email but it would be ideal for the email to identify which cell was the email trigger.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 9 months ago
  
  Hi,
  Supposing cell D7 is the email trigger, you just need to include this line Range("D7") & vbNewLine & _ in the xMailBody line of the code as follows:
  
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  "This is line 1" & vbNewLine & _
  Range("D7") & vbNewLine & _
  "This is line 2"
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Corben · 9 months ago
  
  Thank you for your response. This helped out.
  
  Reply
  
  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?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  KAYDEN · 2 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!
  
  Reply
To post as a guest, your comment is unpublished.
Dmytro · 2 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).
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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.
  
  Reply
To post as a guest, your comment is unpublished.
Justin · 2 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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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.
  
  Reply
To post as a guest, your comment is unpublished.
Sophie · 2 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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Sophie,
  Please change the > 200 to =0 to make it work.Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
griffd12 · 2 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
- Reply
To post as a guest, your comment is unpublished.
Derek · 2 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
- Reply
To post as a guest, your comment is unpublished.
Derek · 2 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
- Reply
To post as a guest, your comment is unpublished.
Janelle · 2 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
- Reply
- To post as a guest, your comment is unpublished.
  Deepika · 10 months ago
  
  Hi crystal,
  
  I have below requirement , My excel sheet has below columns Query,reported by,Answer,Answered by.So whenever new row has been added/Query or Answer column updated email should be triggered to many people automatically after saving that excelsheet.In that mail body- modified rows column values should display as Query was reported by that user and Query was answered by that column value. please help me to achieve this.
  
  Reply
To post as a guest, your comment is unpublished.
asint · 2 years ago
how if there are several cells, each cell has a different value
- Reply
To post as a guest, your comment is unpublished.
MrBain · 2 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!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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.
  
  Reply
To post as a guest, your comment is unpublished.
glennwensley42@gmail.com · 2 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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 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.
  
  Reply
To post as a guest, your comment is unpublished.
Stu · 2 years ago
Hi, is it possible to get the email to send based on the result of and IF function in the spreadsheet? (=IF(D7>E1,TRUE,FALSE). At the moment, I can't see how to get it working that way.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Sorry can't help you with that. Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Joe · 2 years ago
Hi I want to show in the email what is the content of D7
- Reply
To post as a guest, your comment is unpublished.
cisraels · 2 years ago
The VBA Script provided works for NUMERIC values entered:

If IsNumeric(Target.Value) And Target.Value > 200 Then

Can this be changed to allow TEXT??
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  The below VBA code can help you. Thank you 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 Target.Value = "test" 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
  
  Reply
To post as a guest, your comment is unpublished.
motd614@gmail.com · 2 years ago
Hello-I have tried so hard to get the code above to work for me, and I cannot. I went through all of the replies and tried to combine code, but I have been unsuccessful. I need to send an email reminder for uncompleted task. Column A has the task, Column B has the status. I need to send emails for every status that is TRUE. In the message I want it to include the uncompleted task. The messages go to different emails that are located in column C, BUT I only want to send one email per month. Someone please HELP.
- Reply
To post as a guest, your comment is unpublished.
April · 2 years ago
Hi,

What would the code look like if I am trying to send a reminder to myself that an expiry date is approaching (a month before official date). I have conditional formatting on the column (G2:G32) to highlight when the due date is a month away. How do I incorporate that into a VBA code to send to my e-mail as a reminder?
- Reply
To post as a guest, your comment is unpublished.
mathieumelissa · 2 years ago
Hi, I am very new to VBA and when I copy and paste this I have managed to get it to work with my expiry dates but it only works for 1 cell. Is there a way for this to work with all the cells in a column.

thanks
Melissa
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Melissa,
  Please change "D7" to "D:D" in the above VBA code if you want to work with all the cells in column D. Thank you for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
BSS · 2 years ago
Hi
I am fairly new to formatting on spreadsheets and am currently working on a spreadsheet that has multiple expiry dates over several columns and rows (I have already put in place conditional formatting to highlight in red when date has expired) However I was wondering if is possible to create either a report or alert of when the dates are due to expire?
Any thoughts are gratefully received
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Please try the method in this article. Hope it can help.
  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
  
  Reply
To post as a guest, your comment is unpublished.
Ian · 2 years ago
Hello!

I'm new to VBA, so this may be a dumb question. I'm trying to use this code to send automatic email alerts when inventory gets below a numerical value that is specific to each cell. I've been able to get it to work for one cell, and now I'm trying to repeat the process for other cells in my sheet. (i.e. When D7 get's below 2 it send out an email. But when D8 gets below 4 I'd like it to send out a different email.)However, copying and pasting the code and changing the specified cell does not work. What can I do to solve this problem? Thanks!
- Reply
- To post as a guest, your comment is unpublished.
  Moriah · 18 days ago
  
  I am also wondering about this.
  
  Reply
- To post as a guest, your comment is unpublished.
  crbrown17 · 19 days ago
  
  Did you ever work out how to do this? This is what I'm struggling with at the moment too
  
  Reply
To post as a guest, your comment is unpublished.
Kim · 2 years ago
I have a code to autogenerate an email if an change is made to cells e11:a33. If I make several changes, I'd only like it to autogenerate the email once. How can I do this? Thanks,

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to cells E11:E33
If Not Intersect(Target, Range("E11:E33")) Is Nothing 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 = "LAC team," & vbNewLine & vbNewLine & _
"This LAC Event Management Macro for _ has a status update. Please review. Thanks."
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Event Planning Update"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Kim,
  Sorry I am not sure I got your question. Would be nice if you could provide screenshot of what you are trying to do.
  Or you can post any question about Excel to our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional or other Excel fans.
  
  Reply
To post as a guest, your comment is unpublished.
hiba · 2 years ago
I have a list of email addresses in excel that i need to send emails to The subject and body are in cells besides the email address and i have the script below but i need to send 100 emails per 1 hour how i can please your support

Sub SendEm()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
With Mail_Object.CreateItem(o)
.Subject = Range("B" & i).Value
.To = Range("A" & i).Value

.Body = Range("C" & i).Value
.attachments.Add (Sheets("Sheet1").Range("H" & i).Text)
.attachments.Add (Sheets("Sheet1").Range("I" & i).Text)
.attachments.Add (Sheets("Sheet1").Range("J" & i).Text)
.attachments.Add (Sheets("Sheet1").Range("K" & i).Text)
.Send

'.display 'disable display and enable send to send automatically
End With
Next i
MsgBox "E-mail successfully sent", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub
- Reply
To post as a guest, your comment is unpublished.
hiba · 2 years ago
I have a list of email addresses in excel that i need to send emails to The subject and body are in cells besides the email address and i have the script below but i need to send 100 emails per 1 hour how i can please your support

Sub SendEm()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
With Mail_Object.CreateItem(o)
.Subject = Range("B" & i).Value
.To = Range("A" & i).Value

.Body = Range("C" & i).Value
.attachments.Add (Sheets("Sheet1").Range("H" & i).Text)
.attachments.Add (Sheets("Sheet1").Range("I" & i).Text)
.attachments.Add (Sheets("Sheet1").Range("J" & i).Text)
.attachments.Add (Sheets("Sheet1").Range("K" & i).Text)
.Send

'.display 'disable display and enable send to send automatically
End With
Next i
MsgBox "E-mail successfully sent", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Sorry can’t help you with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  
  Reply
To post as a guest, your comment is unpublished.
Akbar · 2 years ago
I have a requirement to send over 30 email. I have two sheet wherein in sheet1 the data is there and I create 30 individual worksheet with name.

Below are conditions:-

1. Each workbook will be send as a separate email.
2. Each email will have different recipient for To, which is there in H7 and CC
3. Each email will have same subject based on workbook/worksheet name.
4. Each email will have same body based on worksheet Name & month.

Now, a table can be made in excel which will list all points from 1-4 in different columns as a master data table. I can achieve this part.

Can there be a macro which can read this table and create separate emails by adding attachments from a path?
- Reply
To post as a guest, your comment is unpublished.
Louis · 2 years ago
Hi

The email is working. The problem i have is that i would like to send the mail ones it is saved and the value is < 200
if value is > 200 then no email should go out.
i have 10 values to change and every time i change a value the calculated value sends a mail
so i would like to make it ( <200 AND save ) then email should be generated.

Thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Louis,
  If you want to make it (<200 AND save), here you need to apply three VBA codes in your workbook.
  
  1. Please insert a Module and rename it as "UpdataVBA_2", then copy below VBA code 1 into the Module window.
  
  VBA code 1.
  Public mBolSendMail As Boolean
  
  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
  
  2 Copy the below VBA code 2 into the ThisWorkbook code window
  
  VBA code 2:
  Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  If UpdataVBA_2.mBolSendMail Then
  Call Mail_small_Text_Outlook
  End If
  mBolSendMail = False
  End Sub
  
  Private Sub Workbook_Open()
  UpdataVBA.mBolSendMail = False
  End Sub
  
  3. Now you need to copy the following code into the worksheet code window your specific cell locates in.
  VBA code 3:
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim xPRg As Range
  Dim xRRg As Range
  On Error Resume Next
  Set xRRg = Range("D7")
  If Target.Cells.count > 1 Then Exit Sub
  Set xRg = Intersect(xRRg, Target)
  If xRg Is Nothing Then
  Set xPRg = xRRg.Precedents
  Set xRg = Intersect(xPRg, Target)
  If xRg Is Nothing Then Exit Sub
  End If
  If IsNumeric(xRRg) And xRRg.Value < 200 Then
  UpdataVBA_2.mBolSendMail = True
  'Call Mail_small_Text_Outlook
  End If
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Jarvis Head · 2 years ago
I have a worksheet where 60 different values could change, prompting 5 different emails. How do I write the code to look at each individual change and then send the corresponding email? Thanks for the assistance
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Sorry can’t help you with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  
  Reply
To post as a guest, your comment is unpublished.
S.Balasubramanian · 2 years ago
Vendor ID Order no Part qty pending
abc@gmail.com 6542684 apple 78 45
abc@gmail.com 8786854 orange 45 0
def@gmail.com 651654 Letter 1245 148
def@gmail.com 898882 cover 547 65

from the above excel sheet data

i have to send mail to abc@gmail.com and def@gmail.com with their respective line

condition is "pending" value should be greater than 0

Guide me how to do it
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  Please try the below VBA code, hope it can help. Thanks for your comment.
  
  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("E2:E5")
  If CInt(xRg.Value) > 0 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 & _
  "ID Order no. :" & Range("B" & xIntR).Value & vbNewLine & _
  "Part :" & Range("C" & xIntR).Value & vbNewLine & _
  "Qty :" & Range("D" & xIntR).Value & vbNewLine
  On Error Resume Next
  With xOutMail
  .To = Range("A" & xIntR).Value
  .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
  
  Reply
To post as a guest, your comment is unpublished.
Kennedy · 3 years ago
I have the code running correctly for when a cell within a range of cells drops below a 1.The problem is that the cells I am referencing use a formula. And when a value that is part of the formula is changed and the cell referenced in the code drops below 1, an email is not sent. The email will only be prompted when the cell itself is changed. How do I code for this?

Thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hi Kennedy,
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  xami4p@gmail.com · 2 years ago
  
  Hi Crystal,
  Thank you for the code. I was trying to use your code to send an email notification. The challenge is however, I have values in column F7 to F300 (some of the cells are blank in column F) which are formulas calculating the remaining days to certain deadlines. So, everyday the values in column F will decrease by 1. I would like to get a notification email where there are only 7 days left to the deadlines. Also, in the xMailBody, I would like to have texts referencing from the excel spreadsheet (like the project name which is going to be due and in how many days). Can this reminder email work even when excel is not running?
  I would appreciate very much your time if you kindly help me to solve the issue.
  Regards,
  Rahman
  
  Reply
To post as a guest, your comment is unpublished.
wu · 3 years ago
Hi.

I am wondering whether it is possible to tailor the first line of each email. For example, in my excel sheet I have a column with title such as Mr., a column with last name such as Black.
I want to send out emails automatically with the first line: Dear Mr. Black
for the others the first line will be accordingly dear ms. White....etc..

Will that be possible to change the wording in the body of the email. That is my issue here.

Thanks a lot
- Reply
To post as a guest, your comment is unpublished.
· 3 years ago
I have a requirement to send over 50 email. Below are conditions:-

1. Each workbook will be send as a separate email.
2. Each email will have different recipient for To, CC and BCC.
3. Each email will have different subject based on workbook/worksheet name.
4. Each email will have different body based on date & subject.

Now, a table can be made in excel which will list all points from 1-4 in different columns as a master data table. I can achieve this part.

Can there be a macro which can read this table and create separate emails by adding attachments from a path?

This will be a great help and save tremendous amount of time.
- Reply
To post as a guest, your comment is unpublished.
justin.p.patterson@gmail.com · 3 years ago
I have a worksheet that has about 20 columns of data, but I only have two that I want to focus on here. Column D is lot number and Column Z is priority. I would like excel to send and email when Column Z has a priority of “HOT” and send an email including the associated lot number in Column D. It will either be HOT or blank so nothing needs to be done unless it is HOT. I would like the Email to go to about 5 people and say something along the lines of “Hello Team, Lot “xxxx’s” Priority has changed to HOT, please prioritize this lot.

Is it possible to have it look up the lot number associated to the priority and place it in the text like that. If not maybe have the subject of the email be the associated lot number and then have the text just be as it was without the lot number.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hi Patterson,
  The below VBA code can help you solve the problem. Please have a try. Thank you for your comment.
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
Vignesh · 3 years ago
I am trying to mail the content which is present in A1:G5 columns in excel ,but its not working. Can you help me with that?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hi Vignesh,
  The following VBA code can help you solve the problem. Thank you for your comment.
  
  Dim xRg As Range
  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 I, J As Long
  Dim xRg As Range
  Dim xStr As String
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  Set xRg = Range("A1:G5")
  For I = 1 To xRg.Rows.Count
  For J = 1 To xRg.Columns.Count
  xStr = xStr & xRg.Rows(I).Cells(J) & " "
  Next
  xStr = xStr & vbNewLine
  Next
  xMailBody = "Hi there" & vbNewLine & vbNewLine & xStr
  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
  
  Reply
To post as a guest, your comment is unpublished.
Vetrak · 3 years ago
I would like to ask if it is possible to send one email when closing the application - each change means a large number of emails.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If (Not Intersect(Target, Range("B2:B9")) Is Nothing) And (Target.Value < 3) 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 = "Ahoj," & vbNewLine & vbNewLine & _
"xxx." & vbNewLine & _
""
On Error Resume Next
With xOutMail
.To = "xxx"
.CC = ""
.BCC = ""
.Subject = "xxx"
.Body = xMailBody
.Send 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Thank you
- Reply
To post as a guest, your comment is unpublished.
Pavel · 3 years ago
Hello
I would like to ask if it is possible to send one email when closing the application - each change means a large number of emails.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If (Not Intersect(Target, Range("B2:B9")) Is Nothing) And (Target.Value < 3) 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 = "Ahoj," & vbNewLine & vbNewLine & _
"XXX." & vbNewLine & _
""
On Error Resume Next
With xOutMail
.To = "1234"
.CC = ""
.BCC = ""
.Subject = "XXX"
.Body = xMailBody
.Send 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hello Pavel,
  Would you explain it in more details? I don't really get your point, sorry about that.
  
  Reply
To post as a guest, your comment is unpublished.
fifi · 3 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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Hello,
  There are two articles:
  1. How to send email with multiple attachments attached in Excel?
  https://www.extendoffice.com/documents/excel/4719-excel-macro-send-multiple-attachment.html
  
  2. How to send an email at specific time of a day in Excel?
  https://www.extendoffice.com/documents/excel/5028-excel-send-email-at-specific-time.html
  
  You can gather the VBA codes in these two articles to solve your problem. Hope I can help.
  
  Reply
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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.
  
  Reply
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.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
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.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
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 :)
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Jacob · 3 years ago
  
  Where exactly do we insert this code?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 3 years ago
  
  Good day,
  You need to place the code into the worksheet's code window.
  Open the Microsoft Visual Basic for Applications window, double click the sheet name in the left pane to open the code editor.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Jermaine · 3 years ago
  
  Crystal,
  
  This replaces the following code:
  
  Sub email()
  
  Dim xRg As Range
  
  Dim xRgEach As Range
  
  Dim xEmail_Subject, xEmail_Send_Form,;etc.
  
  Reply

Přihlásit se ke svému účtu

Password Reset