Hvordan sendes automatisk e-mail baseret på celleværdi i Excel?

Antag, at du vil sende en e-mail via Outlook til en bestemt modtager baseret på en bestemt celleværdi i Excel. For eksempel, når værdien af celle D7 i et regneark er større end 200, oprettes der automatisk en e-mail. Denne artikel introducerer en VBA-metode, som du hurtigt kan løse dette problem.

Send automatisk e-mail baseret på celleværdi med VBA-kode

Send automatisk e-mail baseret på celleværdi med VBA-kode

Gør som følger for at sende en e-mail baseret på celleværdi i Excel.

1. I regnearket skal du sende e-mail baseret på dens celleværdi (her står cellen D7), højreklik på arkfanen og vælg Vis kode fra genvejsmenuen. Se skærmbillede:

2. I popping up Microsoft Visual Basic til applikationer vindue, skal du kopiere og indsætte nedenstående VBA-kode i arkkodevinduet.

VBA-kode: Send e-mail via Outlook baseret på celleværdi i 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

Noter:

1. I VBA-koden D7 og værdi> 200 er cellen og celleværdien, du vil sende e-mail baseret på.

2. Skift venligst e-mail-kroppen, som du har brug for xMailBody linje i koden.

3. Udskift e-mail-adressen med modtagerens e-mail-adresse i køen .To = "E-mail-adresse".

4. Og specificer Cc- og Bcc-modtagere, som du har brug for i .CC = “” og Bcc = “” sektioner.

5. Skift endelig emnets e-mail-emne på linje .Subject = "send med celleværditest".

3. Tryk på andre + Q tasterne sammen for at lukke Microsoft Visual Basic til applikationer vindue.

Fra nu af, når den værdi, du indtaster i celle D7, er større end 200, oprettes der automatisk en e-mail med specificerede modtagere og brødtekster i Outlook. Du kan klikke på Send knappen for at sende denne e-mail. Se skærmbillede:

Noter:

1. VBA-koden fungerer kun, når du bruger Outlook som dit e-mail-program.

2. Hvis de indtastede data i celle D7 er en tekstværdi, vises mailvinduet også.

Send emails nytte af Kutools til Excel hjælper brugere med at sende e-mail via Outlook baseret på oprettet mailingliste i Excel.
Download og prøv det nu! (30 dages gratis sti)

Relaterede artikler:

De bedste Office-produktivitetsværktøjer

Kutools til Excel løser de fleste af dine problemer og øger din produktivitet med 80%

Genbruge: Indsæt hurtigt komplekse formler, diagrammer og alt, hvad du har brugt før; Krypter celler med adgangskode Opret postliste og send e-mails ...
Super Formula Bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
Flet celler / rækker / kolonner uden at miste data; Split celler indhold; Kombiner duplikerede rækker / kolonner... Forhindre duplikerede celler; Sammenlign områder...
Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
Mere end 300 kraftfulde funktioner. Understøtter Office / Excel 2007-2019 og 365. Understøtter alle sprog. Nem implementering i din virksomhed eller organisation. Fuld funktioner 30-dages gratis prøveperiode. 60-dages pengene tilbage garanti.

Læs mere ... Gratis download... Køb...

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
Øger din produktivitet med 50% og reducerer hundredvis af museklik for dig hver dag!

Læs mere ... Gratis download... Køb...

201 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.
DylanLunaEx · 1 days ago
Hi I am trying to use this VBA Code to use as an automated email resolution so I can notify my colleague that an invoice is resolved from a Dispute. So on the first part of the code I was able to make use of it

'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("D1:D99"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = "Resolve" Then
Call Mail_small_Text_Outlook
End If
End Sub

However my dilemma comes in when I am at the code below

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 here"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Send 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

So what I wanted it to happen is that I want it to send an email with an email body that includes the invoice number from a specific cell so let say for example I marked "D7 as resolved I want the email body to include the invoice number in Cell C7 and so on and so forth.
Thank you in advance.
- Reply
- To post as a guest, your comment is unpublished.
  DylanLunaEx · 1 days ago
  
  I want this code to say
  
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  "This is line 1" & vbNewLine & _
  "This is line 2"
  
  Hi Everyone,
  
  This is confirm that Invoice <Then Invoice Number from a specific Cell> was marked as Resolved.
  Please let us know if you need further assistance.
  
  Then I also want it to include my outlook saved signature.
  
  Reply
To post as a guest, your comment is unpublished.
AdamKolaszynski · 7 days ago
Hi,
This is fantastic.

Is there a way for each cell to provide a deifferent email address.

E.g If cell B7 triggers an email to info@example.com, can cell B8 trigger an email to start@tech.com?

Cheers!
- Reply
To post as a guest, your comment is unpublished.
Adam · 7 days ago
Hi,
This is fantastic.

Is there a way for each cell to provide a deifferent email address.

E.g If cell B7 triggers an email to info@example.com, can cell B8 trigger an email to start@tech.com?

Cheers!
- Reply
To post as a guest, your comment is unpublished.
rorangina2011 · 29 days ago
Hello ,
I am new here , your code is doing me a great , but I am having a small problem , the cell value in D7 for me is changing automatically , and it is either blank or has value 1 , when it is value 1 it triggers the email , that works fine , the problem is that it keeps sending emails when the D7=1 , is there a way to make it send only 1 email instead of keep sending ?
Thanks
- Reply
To post as a guest, your comment is unpublished.
Kavitha · 1 months ago
how to send due date email automatically
- Reply
To post as a guest, your comment is unpublished.
Ashish M Sharma · 1 months ago
hi My name Is ashish, If someone changes any data in the axle sheet, the system should automatically shoot the mail to the authorized person, how would that happen?
- Reply
To post as a guest, your comment is unpublished.
Sunkit Shah · 2 months ago
Hi, could anyone help I'm trying to send emails to a different address when a name is entered into a particular cell do I need to adjust this code or have a new piece of code for each address i.e if cell = AA then send to AA@hotmail.com or if cell = BB then send to BB@hotmail.com etc.
- Reply
To post as a guest, your comment is unpublished.
JGentry · 3 months ago
I was wanting to include the "target text" or cell value in the email body. Is there a way to do that? For Example, If my target value to initiate the email is "test", how can I add it to the email body to say "test, this is a automated message"?
- Reply
To post as a guest, your comment is unpublished.
jthom2885 · 4 months ago
Hi, I've been able to create an email with content, but now I'm trying to extract data from a specified cell related to the initial query. How do I get that data to show in the email Body with the below code? I've tried linking with a vLookUp function to extract that data. So basically, how do I get in the main example above, "This is line 1" to show the Data being pulled for xData below

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/12
Dim xRgSel As Range
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String
Dim xData As Object
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xRg = Range("J:J")
Set xRgSel = Intersect(Target, xRg)
Set xData = Application.WorksheetFunction.VLookup(xRgSel, Range("Data_Table"), 6, False)
ActiveWorkbook.Save
If Not xRgSel Is Nothing Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
xMailBody = xData & "Cell(s) " & xRgSel.Address(False, False) & _
" in the worksheet '" & Me.Name & "' were modified on " & _
Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
" by " & Environ$("username") & " in https://textmgmt.sharepoint.com/:x:/r/sites/EmailManagedServiceCollaboration/Documents/(%20Test)%20Shared%20TM%20Tracking%20Document%20-%20Local%20Email%20Campaign.xlsm?d=w7ad2f1404d574b9abb529ec248453f42&csf=1&web=1&e=zg9REc ."

With xMailItem
.To = "jason.thompson@textmanagement.co.uk"
.Subject = "Worksheet modified in Brand Approve Column "
.Body = xMailBody
.Send
End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
- Reply
To post as a guest, your comment is unpublished.
DavidD · 6 months ago
Hi, I am stuck with a piece of Code. I have combined a few comments below but can't get it to work
I have added the xtarget part of the code in the Mail_small_text_Outlook to select a specific cell to be included as subject
As a result, nothing works anymore. Could someone help please

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("AC9"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = 1 Then
ActiveSheet.Calculate
Call Mail_small_Text_Outlook
End If
End Sub

Sub Mail_small_Text_Outlook(xTarget As Range)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim xR
xR = xTarget.Row
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = ""
On Error Resume Next
With xOutMail
.To = "email"
.CC = ""
.BCC = ""
.Subject = Cells(xR, 1).Value
.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("AC9")
On Error GoTo Err01
xI = Int(xRg.Value)
If xI = 1 Then
Call Mail_small_Text_Outlook
End If
Err01:
End Sub
- Reply
To post as a guest, your comment is unpublished.
Yolandivdberg@gmail.com · 7 months ago
Good day,
Thank you for teaching me something new, I am trying to get excel to send out an email to different sales reps.
So if row X is an error code of "999" then it needs to send an email to the rep I placed the email addresses in row K as it differs for every line, but if row X is "0" it should not send an email.
is this possible?
- Reply
To post as a guest, your comment is unpublished.
Alicia · 8 months ago
hello
is there a way to have the value be percentage instead of numeric?
- Reply
To post as a guest, your comment is unpublished.
Brandon · 8 months ago
Thank you for the fantastic VBA coding, i am learning quite a bit. As I am a little "green" with this skill, i am currently trying to have the macro choose between doing nothing, or sending one of two different email to send based on a cell value. If the cell has noting in it then "do nothing", if the cell has a "1" or a "2" in it choose either "Mail_small_Text_Outlook()" or "Mail_small_Text_Outlook_2()". I can not figure this out and i am reaching out to see if there are some brighter minds that could help me with this.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("N5"), Target)
If xRg Is Nothing Then Exit Sub

If IsNumeric(Target.Value) And Target.Value = 1 Then
Call Mail_small_Text_Outlook
Exit Sub
ElseIf IsNumeric(Target.Value) And Target.Value = 2 Then
Call Mail_small_Text_Outlook_2
Exit Sub

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 = "" & vbNewLine & vbNewLine & _

On Error Resume Next
With xOutMail
' .SentOnBehalfOfName = ""
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Sub Mail_small_Text_Outlook_2()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "" & vbNewLine & vbNewLine & _

On Error Resume Next
With xOutMail
' .SentOnBehalfOfName = ""
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.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("N5")
On Error GoTo Err01
xI = Int(xRg.Value)
If xI = 1 Then
Call Mail_small_Text_Outlook
End If
Err01:
End Sub
- Reply
To post as a guest, your comment is unpublished.
reeti jaswal · 9 months ago
hello
i want to send automatic emails when due date come to the concern departments.
i have to apply this to 3 or 4 columns
can you please send me code
- Reply
To post as a guest, your comment is unpublished.
Ckey1990 · 10 months ago
Hello,

If for example a cell in column D is filled in to meet the criteria to auto generate an email. Can this be adapted to pull the information from the row in which has been filled in.

ie D7 is filled in so email the contents of A7, B7, C7

ie D25 is filled in so email the contents of A25, B25, C25

Thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 10 months ago
  
  Hi Ckey1990,
  The below code can do you a favor, please have a try. Thank you.
  
  Dim xRg As Range 'Update by Extendoffice 2020/8/28 Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Cells.Count > 1 Then Exit Sub Set xRg = Intersect(Range("D:D"), Target) If xRg Is Nothing Then Exit Sub If Target.Value = "Done" Then Call Mail_small_Text_Outlook(Target) End If End Sub Sub Mail_small_Text_Outlook(xTarget As Range) Dim xOutApp As Object Dim xOutMail As Object Dim xMailBody As String Dim xR xR = xTarget.Row Set xOutApp = CreateObject("Outlook.Application") Set xOutMail = xOutApp.CreateItem(0) xMailBody = "Hi there" & vbNewLine & vbNewLine & _ Cells(xR, 1).Value & vbNewLine & _ Cells(xR, 2).Value & vbNewLine & _ Cells(xR, 3).Value & vbNewLine 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.
Tyson Nold · 10 months ago
I posted before and got a great answer...but moving forward i need more info. I have coulmn "C" listed with 25 different emails. I have column "D" "E" & "F" used for date completions (ex. August 17 = 081720). I need the email body to be different for each of the DEF cells but only go to ONE email in that coresponding row once entered

D3..(EMAIL A) to c3 address, E3(EMAIL B) to c3 address, F3 (EMAIL C) to c3 address

D4..(EMAIL A) to c4 address, E4(EMAIL B) to c4 address, F4 (EMAIL C) to c4 address...

Does this make sense?
- Reply
To post as a guest, your comment is unpublished.
Saybier@gmail.com · 10 months ago
Thank you for the code, this code works when I enter the value and press enter. But in my case the cell is filling automatically with formula, and when the value is reached it doesn't open the email (the code do not works in this case). I would also like the email body to populate data from the other cells for that line item how would I get it to pull that cell data in reference to the cell that created the email?

Any help would be fantastic!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 10 months ago
  
  Hi Mladen,
  I can only help to solve the formula problem for you. Please have a try. Thank you!
  
  Dim xRg As Range
  'Update by Extendoffice 2020/08/21
  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.
Thais · 10 months ago
Hi !

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

Thank you in advance!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 10 months ago
  
  Hi Thais,
  The email will be sent with the default account in your Outlook. If you need a fixed "from", please get into the account settings dialog in your Outlook, and then specify the account as the default one.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Thais · 10 months ago
  
  Hi Crystal!
  
  Thank you for the tip :-)
  
  The issue is that the code for sending the email is on a shared excel. People who mighty choose to send the automated email from this shared excel, are most often not the ones who might be able to answer if someone replies to the automated email. Therefore I would like the automated email to always be from a fixed email, in that way we avoid peopel who send the automated email to get questions. Do you know if this can be fixed? I mean, in the code you can choose the to: and subject:, shouldnt we be able to design the :from?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 10 months ago
  
  Hi Thais,
  I got your point. It just like sending an email on behalf of someone.
  Please add the below line to the code, and don't forget to specify the email address. (After running the code, you will see the From field has a fixed email address)
  .SentOnBehalfOfName = "Email Address"
  The whole VBA code is as follows.
  Dim xRg As Range 'Update by Extendoffice 20120/8/28 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 .SentOnBehalfOfName = "Email Address" .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.
Nicolas Molina · 10 months ago
Thank you for the code ! I'm having a small issue where the code will only select the first cell that meets my criteria
( I want to flag any tasks that are late so were due before the current date, however the code is only taking the first value and not all the values in my excle"

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

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

Dim i As Long

On Error Resume Next

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

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

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

xLastRow = xRgDate.Rows.Count

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

"
xMailBody = ""
xMailBody = xMailBody & "Dear DOE,"
xMailBody = xMailBody & "This task is OVERDUE!"
xMailBody = xMailBody & ""
Set xMailItem = xOutApp.CreateItem(0)
With xMailItem
.Subject = xMailSubject
.To = xRgSendVal
.HTMLBody = xMailBody
.Display
'.Send
End With
Set xMailItem = Nothing
End If
End If
Next
Set xOutApp = Nothing
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 10 months ago
  
  Hi Nicolas Molina,
  Maybe the code in this tutorial can help you solve the problem:
  How To Send Email If Due Date Has Been Met In Excel?
  https://www.extendoffice.com/documents/excel/4664-excel-send-email-if-due-date-has-been-met.html
  
  Reply
To post as a guest, your comment is unpublished.
Kyle · 10 months ago
Hi Crystal, thank you for sharing this awesome code! I changed the code to function with text so when a cell has anything typed in, it prompts the email function. My question is, how can I code the email to auto-populate the row and column header info for a specific cell along with text? Here are the ranges of data with text:

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

'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("P3:AB50"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value > 0 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hello," & vbNewLine & vbNewLine & _
"Create Bid Comparison step complete." & vbNewLine & _
"Please prepare the Recommendation of Award." & vbNewLine & vbNewLine & _
"Thank you"
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ("Project 20-20")
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  Kyle · 10 months ago
  
  To better clarify, I am hoping to auto-populate the column and row headers into the email, along with text.
  
  Subject "(A3:A50) - (Q1:AC1) - Pending"
  Mail Body "(P1:AB1) complete."
  "Please prepare the (Q1:AC1)"
  
  (i.e. C12, the headers for the subject line would be A12 and C1. C12 for the 1st line in the "Mail Body" and D12 for the 2nd.)
  
  This way, any cell I enter data into will prompt the email and pull the headers in pertaining to that cell.
  
  Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Mladen · 10 months ago
Hello
I need a help. I use VB code for automatically to send email based on cell value in Excel. The thing is that this code works when I enter the value and press enter. But in my case the cell is filling automatically with formula, and when the value is reached it doesn't open the email (the code do not works in this case). Ex.
- Reply
To post as a guest, your comment is unpublished.
shondap · 11 months ago
Hey Crystal,

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

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

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("I:I"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = "Update Request" Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "All," & vbNewLine & vbNewLine & _
"Updates have been entered into the maintenance log:" & vbNewLine & _
Range("b3") & vbNewLine & _
Range("c3") & vbNewLine & _
Range("d3") & vbNewLine & vbNewLine & _
"Thanks," & vbNewLine & _
"Training Team"
With xOutMail
.To = "ShondaX@yahoo.com"
.CC = ""
.BCC = ""
.Subject = "Log Update Requests"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Thanks, Shonda
- Reply
To post as a guest, your comment is unpublished.
fari · 11 months ago
hi there
i have a table in excel file
i need to email it row by row
can you help me?
- Reply
To post as a guest, your comment is unpublished.
Tyson nold · 11 months ago
Is there a way to send emails to individual recipients that can be pulled from another cell in the same row?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 11 months ago
  
  Hi Tyson nold,
  Supposing the recipient's email address is in F7, please apply the below code.
  
  Dim xRg As Range
  'Update by Extendoffice 2020/7/17
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
  If xRg Is Nothing Then Exit Sub
  If IsNumeric(Target.Value) And Target.Value > 200 Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  "This is line 1" & vbNewLine & _
  "This is line 2"
  On Error Resume Next
  With xOutMail
  .To = Range("F7")
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .Body = xMailBody
  .Display 'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Saybier@gmail.com · 10 months ago
  
  That only pulled the information for that cell. If you are only sending one email for the D7 cell this works but how do you get it to change to another cell for the next value in column D? As in if the next value over 200 is D10 how do you get the email to auto pull the information from F10 instead of F7? That's the issue I'm running into right now.
  
  Thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Mike · 11 months ago
  
  I'm not Tyson, but found it helpful (along with the whole tutorial). Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Gary · 1 years ago
Hi, I am using the above code to send auto-generated emails when a cell from range G4:G999, how could I add the value(text) from the adjacent cell in range A4:A999 to the xMailBody on the generated email?
Many thanks,
G
- Reply
To post as a guest, your comment is unpublished.
Brittany · 1 years ago
I want my email to send when any of the cells in row H are changed to a specific value, which is one of 3 drop down options (Ready for Inventory) set for that row. How do I modify this coding to that instead of a manually entered value? Thanks!!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Brittany,
  Supposing there are drop down lists in column H, and you want to trigger an email when selecting "Done" from the drop-down, please try the below VBA to get it down.
  
  Dim xRg As Range
  'Update by Extendoffice 2020/6/12
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("H:H"), Target)
  If xRg Is Nothing Then Exit Sub
  If Target.Value = "Done" Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  "This is line 1" & vbNewLine & _
  "This is line 2"
  On Error Resume Next
  With xOutMail
  .To = "Email Address"
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .Body = xMailBody
  .Display 'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Mladen · 10 months ago
  
  HelloI need a help. I use VB code for automatically to send email based on cell value in Excel. The thing is that this code works when I enter the value and press enter. But in my case the cell is filling automatically with formula, and when the value is reached it doesn't open the email (the code do not works in this case).
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 10 months ago
  
  Hi Mladen,
  Try the below code.
  
  Dim xRg As Range
  'Update by Extendoffice 2012/08/07
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
  If xRg Is Nothing Then Exit Sub
  If IsNumeric(Target.Value) And Target.Value > 200 Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  "This is line 1" & vbNewLine & _
  "This is line 2"
  On Error Resume Next
  With xOutMail
  .To = "Email Address"
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .Body = xMailBody
  .Display 'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Private Sub Worksheet_Calculate()
  Dim xI As Integer
  Dim xRg As Range
  Set xRg = Range("D7")
  On Error GoTo Err01
  xI = Int(xRg.Value)
  If xI > 200 Then
  Call Mail_small_Text_Outlook
  End If
  Err01:
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Mladen · 10 months ago
  
  Thanks! It works.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Saybier@gmail.com · 10 months ago
  
  This is great but I need it to pull information from other cells on the same row that is triggering the email... Can anyone help?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Shonda · 11 months ago
  
  What if there are two options in the drop down list Update and Complete? How would the code look? What if each had its own email that would to be sent based on the action?
  
  Reply
To post as a guest, your comment is unpublished.
dave · 1 years ago
Hi
I am trying to do this type of automatic email button a true or false question true than the form does nothing but false then the form to automatically email out
Can you help ??
- Reply
To post as a guest, your comment is unpublished.
darkgyft · 1 years ago
Cystal,

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

Mike
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi darkgyft.
  Try the below VBA code. Hope I can help. thank you.
  
  Dim xRg As Range
  'Update by Extendoffice 2020/05/22
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
  If xRg Is Nothing Then Exit Sub
  If IsNumeric(Target.Value) And Target.Value > 200 Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  On Error Resume Next
  With xOutMail
  .Display 'or use .Send
  .To = "Email Address"
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .HTMLBody = "This is a test email sending in Excel" & "
  " & .HTMLBody
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
thumsri@gmail.com · 1 years ago
Hi - I tried to use the code, it works fine when cell value D7 is changed manually. if D7 value changed using any formula i.e vlookup or IF .code doesnt trigger. Code trigger only when any cell on the sheet is change keeping D7 value above 200. is there any wayout for the problem . regards srini
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi,
  The below VBA code can help you solve the problem. Please have a try. Thank you.
  
  Dim xRg As Range
  'Update by Extendoffice 20120/5/22
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
  If xRg Is Nothing Then Exit Sub
  If IsNumeric(Target.Value) And Target.Value > 200 Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  "This is line 1" & vbNewLine & _
  "This is line 2"
  On Error Resume Next
  With xOutMail
  .To = "Email Address"
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .Body = xMailBody
  .Display 'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Private Sub Worksheet_Calculate()
  Dim xI As Integer
  Dim xRg As Range
  Set xRg = Range("D7")
  On Error GoTo Err01
  xI = Int(xRg.Value)
  If xI > 200 Then
  Call Mail_small_Text_Outlook
  End If
  Err01:
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Stephanie · 1 years ago
Hi All,
I'm trying to connect all these actions by clicking a button instead of running it each time. Does anybody know how to do this?
- Reply
To post as a guest, your comment is unpublished.
Bob · 1 years ago
Hi! How can I send the e-mails without accepting them at outlook. And they sand automatically without knowing.
- Reply
To post as a guest, your comment is unpublished.
lynsey · 1 years ago
Hi all - I am trying to use this code but when I exit the developer and test the cell- it is over the specifications, but nothing happens. I don't receive a pop up to send the email? But when I run the code in developer it comes up - its just when I'm within the spreadsheet and change the cell, nothing happens. I have copied and edited the code as per instructions.
- Reply
To post as a guest, your comment is unpublished.
karthick · 1 years ago
Dear all kindly help me …… my requirement as follow..!!

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

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

thanks a lot in advance
- Reply
To post as a guest, your comment is unpublished.
raguirre76@gmail.com · 1 years ago
How can I refer to a cell on the worksheet to include in the email automatically?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Robert,
  Supposing you want to refer to cell A7 on the worksheet, please apply the below code.
  
  Dim xRg As Range
  'Update by Extendoffice 2019/12/13
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
  If xRg Is Nothing Then Exit Sub
  If IsNumeric(Target.Value) And Target.Value > 200 Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set Rng = Selection.SpecialCells(xlCellTypeVisible)
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  Range("A7") & vbNewLine & _
  "Best Regards"
  
  On Error Resume Next
  With xOutMail
  .To = ""
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .Body = xMailBody
  .Display 'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
tarti.s689@gmail.com · 1 years ago
Crystal you're the best.

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

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

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

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

Any help on this will be much appreciated.

Thanks and Regards

Emanuel Prasanna Kumar

The code i currently have. is.

_______________

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

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

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

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

Many thanks, Rob

My current code:

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

Private Sub Worksheet_Calculate()
Dim xI As Integer
Dim xRg As Range
Set xRg = Range("A1")
On Error GoTo Err01
xI = Int(xRg.Value)
If xI = 1 Then
Call Mail_small_Text_Outlook
End If
Err01:
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  RobArchibald · 1 years ago
  
  Actually, more to the point - how do I make it so that only one email is created when I open the workbook, but not just when I make a change to it? Currently changing any cell results in a new email being created.
  
  Please help with this last hurdle, I'm so close!
  
  Reply
To post as a guest, your comment is unpublished.
Viji · 1 years ago
How to insert the images in email body
- Reply
To post as a guest, your comment is unpublished.
Edward · 1 years ago
I Keep Getting this:

Run-time error '429':

ActiveX component can't create object

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

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

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

How to add Cell Text Ex. text from A7, B7 & C7 related to D7 into Mail Body instead of other text. Could someone please guide me. Thank you in advance.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Ganesh,
  Please apply the below code. Hope I can help.
  
  Dim xRg As Range
  'Update by Extendoffice 2019/12/13
  Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
  If xRg Is Nothing Then Exit Sub
  If IsNumeric(Target.Value) And Target.Value > 200 Then
  Call Mail_small_Text_Outlook
  End If
  End Sub
  Sub Mail_small_Text_Outlook()
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" & vbNewLine & vbNewLine & _
  Range("A7") & vbNewLine & _
  Range("B7") & vbNewLine & _
  Range("C7") & vbNewLine & _
  "Best Regards"
  
  On Error Resume Next
  With xOutMail
  .To = ""
  .CC = ""
  .BCC = ""
  .Subject = "send by cell value test"
  .Body = xMailBody
  .Display 'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Guru · 11 months ago
  
  I have the exact situation but only difference is ...I have got around 100 rows to check. So it has to check each row on column D if any cell on column D meets the criteria it has to add info from column A B and C for respective D cell and after checking all 100 rows it has to send one email.
  
  Reply
To post as a guest, your comment is unpublished.
ricardo27 · 1 years ago
I have another question, how do you write in VBA so instead of "If IsNumeric(Target.Value) And Target.Value > 200 Then" (detecting a value greater than 200 it detects any text input..... for example in cell D7 you write any words (not number but words) it calls out the email ! please respond
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi,
  Please replace the line "If IsNumeric(Target.Value) And Target.Value > 200 Then" with "If Target.Value = "test" Then"
  "test" is the specific word you will cal out the email based on, please change it to your own word.
  
  Reply
To post as a guest, your comment is unpublished.
ricardo27 · 1 years ago
Hi

I got a question, on excel I have a column with several emails written (they are formulated), for example A36 is example1@hotmail.com, A37 is example2@hotmail.com, A38 is example3@hotmail.com, I want that the part of the programming VBA To: Range (A36:A38) so they send the email to each of those wroten emails, the thing is that I put it like this To: Range (A36:A38) but it does not work, am I doing it wrong?
- 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 · 1 years ago
Hi

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

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

How would I do that in VBA

Thanks for your help:)
- 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

Log ind på din konto

Password Reset