Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to automatically send email based on cell value in Excel?

Supposing you want to send an email through Outlook to a certain recipient based on a specified cell value in Excel. For example, when the value of cell D7 in a worksheet is greater than 200, then an email is created automatically. This article introduces a VBA method for you to quickly solve this issue.

Automatically send email based on cell value with VBA code

Automatically send email based on cell value with VBA code

Please do as follows to send an email based on cell value in Excel.

1. In the worksheet you need to send email based on its cell value (here says the cell D7), right-click the sheet tab, and select View Code from the context menu. See screenshot:

2. In the popping up Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the sheet code window.

VBA code: Send email through Outlook based on cell value in Excel

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("D7"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value > 200 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2"
    On Error Resume Next
    With xOutMail
        .To = "Email Address"
        .CC = ""
        .BCC = ""
        .Subject = "send by cell value test"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Notes:

1). In the VBA code, D7 and value > 200 are the cell and cell value you will send email based on.
2). Please change the email body as you need in xMailBody line in the code.
3). Replace the Email Address with the recipient email address in line .To = "Email Address".
4). And specify the Cc and Bcc recipients as you need in .CC = “” and Bcc = “” sections.
5). Finally change the email subject in line .Subject = "send by cell value test".

3. Press the Alt + Q keys together to close the Microsoft Visual Basic for Applications window.

From now on, when the value you entering in cell D7 is greater than 200, an email with specified recipients and body will be created automatically in Outlook. You can click the Send button to send this email. See screenshot:

Notes:

1. The VBA code is only working when you use Outlook as your email program.

2. If the entered data in cell D7 is a text value, the email window will be popped out as well.

Easily send email through Outlook based on fields of created mailing list in Excel:

The Send Emails utility of Kutools for Excel helps users sending email through Outlook based on created mailing list in Excel.
Download and try it now! ( 30-day free trail)

Related articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (290)
No ratings yet. Be the first to rate!
Debbie
Debbie
This comment was minimized by the moderator on the site
How should the code be modified, to apply to an entire range of cells?
Reply
crystal
crystal     Debbie
This comment was minimized by the moderator on the site
Dear Debbie,
Please try below VBA code to solve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If (Not Intersect(Target, Range("A1:D4")) Is Nothing) And (Target.Value > 200) Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Your recipient's email address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Reply
Report
Jordan
Jordan
This comment was minimized by the moderator on the site
I am having trouble getting this code to prompt if the value in the cell is changed indirectly. For example, if I have Sum equation changing this value automatically. When the equation runs and the value goes above the set value to prompt the email, it does not do so, unless I physically change the number myself. Is there a way to make the email prompt even if changed indirectly?
Reply
crystal
crystal     Jordan
This comment was minimized by the moderator on the site
Dear Jordan,
The following VBA code can help you solve the problem. Please don't forget to replace the "Email Address" with the recipient's email address in the code. Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgPre As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("D7")
Set xRgPre = xRg.Precedents
If xRg.Value > 200 Then
If Target.Address = xRg.Address Then
Call Mail_small_Text_Outlook
ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Email Address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Reply
Report
Daniel
Daniel     crystal
This comment was minimized by the moderator on the site
I've modified suggested code to try to make it work for my application.
Changed xRg = Range("C2:C40") and If xRg.Value = -1.

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

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


Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgPre As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("C2:C40")
Set xRgPre = xRg.Precedents
If xRg.Value = -1 Then
If Target.Address = xRg.Address Then
Call Mail_small_Text_Outlook
ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
Reply
Report
Jim
Jim     crystal
This comment was minimized by the moderator on the site
I used this code with the only change being I have applied it to an entire column [Set xRg = Range("D4:D13")]. Now the event triggers whenever a calculation is made regardless of whether the valve in Column D is below the target value. Any idea's why that is?


Dim Xrg As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgPre As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set Xrg = Range("D4:D13")
Set xRgPre = Xrg.Precedents
If Xrg.Value < 1200 Then
If Target.Address = Xrg.Address Then
Call Mail_small_Text_Outlook
ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
Call Mail_small_Text_Outlook
End If
End If
End Sub

Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi" & vbNewLine & _
"Test vba" _
& vbNewLine & _
"Line 2."
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Auto Email Test"
.Body = xMailBody
.Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

End Sub


Thanks.
Reply
Shawn Henry
Shawn Henry
This comment was minimized by the moderator on the site
Hello

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

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("D7")
If xRg = Target And Target.Value > 200 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim xRgMsg As Range
Dim xCell As Range
Set xRgMsg = Application.InputBox("Please select the address cells：", "Kutools for Excel", , , , , , 8)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
For Each xCell In xRgMsg
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
With xOutMail
.To = xCell.Value
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
xOutApp = Nothing
xOutMail = Nothing
Next
On Error GoTo 0
End Sub
Reply
Report
Brahma
Brahma
This comment was minimized by the moderator on the site
will it be sent automatically mail, without any manual interruption
Reply
crystal
crystal     Brahma
This comment was minimized by the moderator on the site
Dear Brahma,
If you want to directly send the email without displaying, please replace the line ".Display" with ".Send" in the above VBA code.
Reply
Report
basil
basil
This comment was minimized by the moderator on the site
Hi I put the same script but it is not working please help me in the 1st part

Dim xRg As Range

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

End Sub
Reply
crystal
crystal     basil
This comment was minimized by the moderator on the site
Dear basil,
Is there any warning when running the code?
Reply
Report
Jose Manuel
Jose Manuel
This comment was minimized by the moderator on the site
Hello, how would you modify this code to check wether a group of cells have the string "No match" and send an email if it has.
Reply
crystal
crystal     Jose Manuel
This comment was minimized by the moderator on the site
Dear Jose,
Please try below VBA code. When running the code, a dialog box pops up, please select the range you will check for string, and click the OK button. if the string does not exist, you will get a prompt dialog box. If the string exists in the range, an email with specified recipient, subject and body will display.

Sub SendEmail()
Dim I As Long
Dim J As Long
Dim xRg As Range
Dim xArr
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim xFlag As Boolean
On Error Resume Next
Set xRg = Application.InputBox("Please select range", "Kutools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xArr = xRg.Value
xFlag = False
For I = 1 To UBound(xArr)
For J = 1 To UBound(xArr, 2)
If xArr(I, J) = "No Match" Then
xFlag = True
End If
Next
Next
If xFlag Then
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
With xOutMail
.To = "Email address"
.CC = ""
.BCC = ""
.Subject = "Match"
.Body = xMailBody
.Display 'or use .Send
End With
Else
MsgBox "Found No matched value", vbInformation, "KuTools for Excel"
End If
End Sub
Reply
Report
Frank
Frank
This comment was minimized by the moderator on the site
How could I change this code for sending student grades to parents. Where if column A is the grade and Column B is the parent email. I want to populate an email for each student with an F as a grade.
Reply
Report
crystal
crystal     Frank
This comment was minimized by the moderator on the site
Dear Frank,
The below VBA code can help you solve the problem. Thank you.

Sub Mail_small_Text_Outlook()
Dim xRg As Range
Dim I As Long
Dim xRows As Long
Dim xVal As String
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xRg = Application.InputBox("Please select grade column and the email column (two columns)", "Kutools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xRows = xRg.Rows.Count
Set xRg = xRg(2)
For I = 1 To xRows
xVal = xRg.Offset(I, -1).Text
If xVal = "F" Then
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is your child's grade " & xRg.Offset(I, -1).Text
With xOutMail
.to = xRg.Offset(I, 0).Text
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End If
Next
End Sub
Reply
Report
Dhruv
Dhruv
This comment was minimized by the moderator on the site
I have a list of email addresses already in an excel file, how can I modify the code to automatically choose the email address of the person if his cell D7 is >200?
Reply
crystal
crystal     Dhruv
This comment was minimized by the moderator on the site
Good Day,
The following VBA code can help you solve the problem. Please place the VBA script into your worksheet module. When value in the specified cell meet the condition, a Kutools for Excel dialog box will pop up, please select the cells which contain the recipients' email addresses and then click the OK button. Then emails with specified recipients are opening. Please send them as you need.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("D7")
If xRg = Target And Target.Value > 200 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim xRgMsg As Range
Dim xCell As Range
Set xRgMsg = Application.InputBox("Please select the address cells：", "Kutools for Excel", , , , , , 8)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
For Each xCell In xRgMsg
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
With xOutMail
.To = xCell.Value
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
xOutApp = Nothing
xOutMail = Nothing
Next
On Error GoTo 0
End Sub
Reply
Report
mayank vijay
mayank vijay
This comment was minimized by the moderator on the site
I am having trouble sending mail through outlook. I receive the error saying "A program is trying to send an email on your behalf. If it is unexpected, please deny and verify your anti-virus software is up to date"
Please help as I am not able to automate it.
Reply
Report
crystal
crystal     mayank vijay
This comment was minimized by the moderator on the site
Sorry mayank,
The code works well in my case. It seems that something about "send on behalf" function is configured in your Outlook. Pease check for it.
Reply
Report
New2Excel
New2Excel
This comment was minimized by the moderator on the site
Hello what code would I use if I am trying to send an email to a manager that has a list of the fruit that has a quantity > 200 once per month (based on your example) or expires soon( based on dates)
Reply
crystal
crystal     New2Excel
This comment was minimized by the moderator on the site
Good Day
May be the method in this article "How to send email if due date has been met in Excel?" can help you.
Please follow this link: https://www.extendoffice.com/documents/excel/4664-excel-send-email-if-due-date-has-been-met.html
Reply
Report
Doug
Doug
This comment was minimized by the moderator on the site
How can I edit the code to send an email based on a date in the cell. For example, I need a document reviewed every 15 months and I want to kick out an email at 12 months to an email address saying the document needs to be reviewed. I've got it now to auto-send an email by changing .Display to .Send and it works great as written, but what do I need to change to use a date function instead of a whole number??
Reply
Savio Jois
Savio Jois
This comment was minimized by the moderator on the site
How can you add Multiple Range to "Set xRg = Range("D7")". I want to edit it and add Range("D7:F7"). However i am getting an error of Run Time Error 13, Type Mismatch and it is taking me to If xRg = Target And Target.Value > 2 Then.


How can i solve this proble?
Reply
Report
crystal
crystal     Savio Jois
This comment was minimized by the moderator on the site
Good Day,
Please try below VBA code to solve the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If (Not Intersect(Target, Range("D7:F7")) Is Nothing) And (Target.Value > 200) Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Your recipient's email address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Reply
Report
Savio Jois
Savio Jois     crystal
This comment was minimized by the moderator on the site
worked perfectly fine.. Thank you..:):)
Reply
Report
Nitol
Nitol     crystal
This comment was minimized by the moderator on the site
It is not working for me as the value in D7 is a result of a formual. What if cell D7 contains a formula, e.g. D7 =2*120? It still meets the condition but nothing is happening. Please help
Reply
Hanizah Ismail
Hanizah Ismail
This comment was minimized by the moderator on the site
how to stop code from running ie don't prompt the email when condition is not met?

even when D7 < 200, I still get prompted the email.
Reply
Report
crystal
crystal     Hanizah Ismail
This comment was minimized by the moderator on the site
Good Day,
The code is updated in the post with the problem solved. Thank you for your comment.
Reply
Report
Ann
Ann
This comment was minimized by the moderator on the site
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
Report
crystal
crystal     Ann
This comment was minimized by the moderator on the site
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
Report
Jermaine
Jermaine     crystal
This comment was minimized by the moderator on the site
Crystal,

This replaces the following code:

Sub email()

Dim xRg As Range

Dim xRgEach As Range

Dim xEmail_Subject, xEmail_Send_Form,;etc.
Reply
Jacob
Jacob     crystal
This comment was minimized by the moderator on the site
Where exactly do we insert this code?
Reply
crystal
crystal     Jacob
This comment was minimized by the moderator on the site
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
Report
martyn
martyn
This comment was minimized by the moderator on the site
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
Report
crystal
crystal     martyn
This comment was minimized by the moderator on the site
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
Report
Jesse
Jesse
This comment was minimized by the moderator on the site
Hi,


What if I wanted to send the email based off of the word "completed" being added to column L?
Reply
crystal
crystal     Jesse
This comment was minimized by the moderator on the site
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
Report
Jan
Jan
This comment was minimized by the moderator on the site
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
crystal
crystal     Jan
This comment was minimized by the moderator on the site
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
Report
Noemi
Noemi
This comment was minimized by the moderator on the site
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
Report
crystal
crystal     Noemi
This comment was minimized by the moderator on the site
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
Report
basha
basha
This comment was minimized by the moderator on the site
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
crystal
crystal     basha
This comment was minimized by the moderator on the site
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
Report
kevin b
kevin b
This comment was minimized by the moderator on the site
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
crystal
crystal     kevin b
This comment was minimized by the moderator on the site
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
Report
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations