Sending Emails

[juanpablo]

I have the following Excel with data in columns:

State-Name-Customer-Email-PO-NV-Product01-Qty01-Product02-Qty02-Product03-Qty03-DeliveryDate

And I would like to create a code that everytime the user enters the last
info on Delivery Date, it sends and automated email to the Email stated on
that row,
So the email would use the information stored on the rows:

Subject: (State) (PO) (Customer)
Message:
Dear (Name):
Your Purchase Order (PO) associated to our Nota de Venta(NV) with the
following products:
(Product01) (Qty01)
(Product02) (Qty02)
(Product03) (Qty03)

is under production and it will be ready for delivery on (DeliveryDate).

Best regards. Bla bla

Thank You!

 

[Ron de Bruin]

Start here
http://www.rondebruin.nl/mail/change.htm

Download the example workbook

Or see the examples here
http://www.rondebruin.nl/mail/folder3/message.htm

 

[juanpablo]

Thanks,

According to your instruction:
"You must copy this macro in a standard module and not in the worksheet
module"

What is the difference?


JP

 

[Ron de Bruin]

See
http://www.rondebruin.nl/code.htm

 

[juanpablo]

Thanks.
If: Instead of the range being numeric value, is a date, how should it be:

If Not Application.Intersect(Range("Q2:T8000"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 0 Then

 

[juanpablo]

Thanks, I did the following code with your instructions, it works ok but how
do I modified the email so that only one email is sent to each recipient?
Right now with this code, it keeps adding one by one to the same email.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("Q2:T8000"), Target) Is Nothing Then
If IsDate(Target.Value) And Target.Value > 0 Then
Call Mail_small_Text_Outlook
End If
End If
End Sub

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim cell As Range
Dim strto As String
On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Hoja1") _
.Range("D28000").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"Cell A1 is changed" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Ron de Bruin]

Have you see the example for a formula in the second tab in the workbook ?
You can change this to the change event if you want

This example will only send the info from the row