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

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

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