emailing from Excel

[Jock]

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

 

[Ron de Bruin]

Hi Jock

See
http://www.rondebruin.nl/mail/prevent.htm

 

[Jock]

Thanks Ron.
I'm currently stuck with 2003 so I'll have to put up with it until 2007
arrives next month!

Cheers,
Jock