Solved I'm trying to send an email from Excel.

May 1, 2013 at 10:59:48
Specs: Windows 7
If someone with access to a excel 10 spreadsheet makes a change in it is it possible to have an email sent to my outlook email address?

See More: Im trying to send an email from Excel.

Report •

#1
May 1, 2013 at 11:51:11
✔ Best Answer
Yes, you can achieve this by using the 'BeforeSave' function. Open the VBA window, expand 'Microsoft Excel Objects' if it's not already, then double-click on 'ThisWorkbook.'

Copy and paste the following code into the window: (Note: you will need to change the email addresses and the servername at the minimum!)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Dim MailObject As Object
 Dim Cconfig As Object
 Dim SMTP_Config As Variant
 Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Body As String
 Email_Subject = "User Has Saved Changes to Your WorkBook"
 Email_Send_From = "emailaddress@gmail.com"
 Email_Send_To = "emailaddress@gmail.com"
 Email_Body = "Someone has made changes to your workbook and saved them."
 Set MailObject = CreateObject("CDO.Message")
 On Error GoTo debugs
 Set Cconfig = CreateObject("CDO.Configuration")
 Cconfig.Load -1
 Set SMTP_Config = Cconfig.Fields
 With SMTP_Config
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "PUTYOURSERVERNAMEHERE!"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 .Update
 End With
 With MailObject
 Set .Configuration = Cconfig
 End With
 MailObject.Subject = Email_Subject
 MailObject.From = Email_Send_From
 MailObject.To = Email_Send_To
 MailObject.TextBody = Email_Body
 MailObject.send
debugs:
 If Err.Description <> "" Then MsgBox Err.Description
End Sub

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#2
May 1, 2013 at 13:54:23
Note: They can just disable macros and prevent you from getting an email.

How To Ask Questions The Smart Way


Report •

#3
May 1, 2013 at 14:25:06
Razor2.3, good point. There is no way around this because it would become a security issue for all users.

MichaelDavis, just keep in mind what Razor2.3 has said. The only way to do what you want to do is to use VBA/Macro and there is no way to prevent a user from disabling macros.

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

Related Solutions

#4
May 1, 2013 at 14:50:42
Well, there are other ways to do this, just not from Excel.

File auditing can be turned on, and you could get some program scan the audit log to see if the file has been modified, and send an email if it has. The email would not happen until the program ran and noticed a change in the audit log.

Some program could register for file change notifications and send an email when it received a notification that the file changed. The email would be sent immediately, but Windows doesn't guarantee you'll be notified of file changes.

Neither of these options would detect someone copying the file and modifying the copy, but that's how it goes.

How To Ask Questions The Smart Way


Report •

#5
May 6, 2013 at 05:13:33
I'll give the suggestions a try and I appreciate your answers.

Report •

Ask Question