Question:

Hi,

I am looking for an alart excel sheet that will be placed in our netowrk server, when any one saves the changes in excel sheet it automaticaly sends an email to group of emails “Excel file has been updated”

Solution:

I would suggest CDO rather than http://www.rondebruin.nl/cdo.htmhttp://www.rondebruin.nl/cdo.htm. It avoids the Outlook warning plus it does not rely on Outlook being present

This code goes in the ThisWorkbook module of the workbook, it runs automatically on save … if the workbook has macro enabled

Pls update this line with your SMTP server detials

.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) _
= “Fill in your SMTP server here”

Cheers
Dave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim iMsg As Object
Dim iConf As Object
Dim strbody As String

Set iMsg = CreateObject(“CDO.Message”)
Set iConf = CreateObject(“CDO.Configuration”)

iConf.Load -1    ‘ CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) _
= “Fill in your SMTP server here”
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
.Update
End With

With iMsg
Set .Configuration = iConf
.To = “youremailaddyhere”
.From = “Someone”
.Subject = “Excel file has been updated”
.TextBody = strbody
.Send
End With

End Sub