Microsoft Office Forums - View Single Post - receive cvs file then automatically send out to members

OTPM · 08-02-2012, 06:41 AM

Hi
Try this code. It has been supplied by Ron Debruin 's Web site http://www.rondebruin.nl/.

Right click on the name tab of the worksheet that changes and select View Code.
Change the left hand drop box to Worksheet.
Paste the code below and save the workbook and text.
You will need to change the email addresses in the code below to those that you need.
This will generate an email as soon as anything changes on the worksheet and covers up to 1 million rows. This can be changed if you want a smaller area to be checked.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:XFD100000")) Is Nothing Then
  Dim Msg As String
  Dim OutApp As Outlook.Application
  Dim OutMail As Outlook.MailItem
  Msg = "Worksheet has changed. Here is the updated Worksheet."
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(olMailItem)
  With OutMail
    .To = "emailaddr1; emailaddr2; emailaddr3"
    .CC = ""
    .BCC = ""
    .Subject = "Worksheet Updates"
    .Body = Msg
    .Send
  End With
  Set OutMail = Nothing
  Set OutApp = Nothing
End If
End Sub

Good luck.

Tony