| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending Emails
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending Emails
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending Emails
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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! . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending Emails
See
http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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! . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending Emails
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 "Ron de Bruin" wrote: See http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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! . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending Emails
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("D2:D8000").Cells.SpecialCells(xlCellTypeCo nstants) 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 "juanpablo" wrote: 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 "Ron de Bruin" wrote: See http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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! . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending Emails
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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("D2:D8000").Cells.SpecialCells(xlCellTypeCo nstants) 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 "juanpablo" wrote: 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 "Ron de Bruin" wrote: See http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "juanpablo" wrote in message ... 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! . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trouble Sending emails | Excel Programming | |||
| Sending emails from worksheet | Excel Discussion (Misc queries) | |||
| Help Sending Emails From Excel | Excel Discussion (Misc queries) | |||
| Sending emails from Excel | Excel Programming | |||
Linear Mode
