Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
emailing from Excel
H all,
I found the following code in this forum but I need to adapt it a bit to suit our needs. When cell value is changed it will send an email to the email address in "A1". Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("c8:e13")) Is Nothing Then With Target If .Value < "" Then Dim objOL As New Outlook.Application Dim objMail As MailItem Set objOL = New Outlook.Application Set objMail = objOL.CreateItem(olMailItem) msg = "XXXX1" & Chr(13) & Chr(13) msg = msg & "Payment of £XXXX2 recieved on XXXX3 for the month of XXX4" & Chr(13) msg = msg & "Sandra" addee = Range("a1").Value CC = Range("b1").Value With objMail .To = addee .CC = CC .Subject = "Payment received" .Body = msg .Display .Send End With Set objMail = Nothing Set objOL = Nothing MsgBox "File has been e-mailed to " & addee End Sub XXX1 will be the cell on Sheet 1 active row, column "B" XXX2 will be the value in the cell which has just changed XXX3 will be today date XXX4 will be the month which is derived from a constant date in row "C" in the column above the changed cell Also, how do I turn off the Outlook warning about an email being sent. Does any of this make sense??? Thanks Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
emailing from Excel
Hi Jock
See http://www.rondebruin.nl/mail/prevent.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jock" wrote in message ... H all, I found the following code in this forum but I need to adapt it a bit to suit our needs. When cell value is changed it will send an email to the email address in "A1". Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("c8:e13")) Is Nothing Then With Target If .Value < "" Then Dim objOL As New Outlook.Application Dim objMail As MailItem Set objOL = New Outlook.Application Set objMail = objOL.CreateItem(olMailItem) msg = "XXXX1" & Chr(13) & Chr(13) msg = msg & "Payment of £XXXX2 recieved on XXXX3 for the month of XXX4" & Chr(13) msg = msg & "Sandra" addee = Range("a1").Value CC = Range("b1").Value With objMail .To = addee .CC = CC .Subject = "Payment received" .Body = msg .Display .Send End With Set objMail = Nothing Set objOL = Nothing MsgBox "File has been e-mailed to " & addee End Sub XXX1 will be the cell on Sheet 1 active row, column "B" XXX2 will be the value in the cell which has just changed XXX3 will be today date XXX4 will be the month which is derived from a constant date in row "C" in the column above the changed cell Also, how do I turn off the Outlook warning about an email being sent. Does any of this make sense??? Thanks Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
emailing from Excel
Thanks Ron.
I'm currently stuck with 2003 so I'll have to put up with it until 2007 arrives next month! Cheers, Jock -- Traa Dy Liooar Jock "Ron de Bruin" wrote: Hi Jock See http://www.rondebruin.nl/mail/prevent.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jock" wrote in message ... H all, I found the following code in this forum but I need to adapt it a bit to suit our needs. When cell value is changed it will send an email to the email address in "A1". Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("c8:e13")) Is Nothing Then With Target If .Value < "" Then Dim objOL As New Outlook.Application Dim objMail As MailItem Set objOL = New Outlook.Application Set objMail = objOL.CreateItem(olMailItem) msg = "XXXX1" & Chr(13) & Chr(13) msg = msg & "Payment of £XXXX2 recieved on XXXX3 for the month of XXX4" & Chr(13) msg = msg & "Sandra" addee = Range("a1").Value CC = Range("b1").Value With objMail .To = addee .CC = CC .Subject = "Payment received" .Body = msg .Display .Send End With Set objMail = Nothing Set objOL = Nothing MsgBox "File has been e-mailed to " & addee End Sub XXX1 will be the cell on Sheet 1 active row, column "B" XXX2 will be the value in the cell which has just changed XXX3 will be today date XXX4 will be the month which is derived from a constant date in row "C" in the column above the changed cell Also, how do I turn off the Outlook warning about an email being sent. Does any of this make sense??? Thanks Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
emailing from Excel | Excel Discussion (Misc queries) | |||
Emailing from Excel | Excel Programming | |||
Emailing in Excel VBA | Excel Programming | |||
emailing from excel | Excel Programming | |||
Emailing from Excel | Excel Programming |