# Email Notification from excel when changes are made



## greggio (Oct 14, 2003)

I am trying to find out if it is possible to send a notification to other users if one user edits and saves a particular document. The notification could be by email or windows messaging or whatever. 

Does anyone know if this is even possible?


----------



## Anne Troy (Feb 14, 1999)

Sure. But you only want it if the file is EDITED? What if someone opens the doc and doesn't edit it?

How well do you know VBA?


----------



## greggio (Oct 14, 2003)

Yes, only if it is edited. Many people will open the file, but only a few will be able to make changes, and they want to know when it is changed.

I don't know VBA much at all. On a side not though, it is something I wanted to get into to help with excel and access. Do you think it is something that can be learned through a few good books? Any suggestions?

Thanks!


----------



## Anne Troy (Feb 14, 1999)

Well, you don't have to know VBA to get a VBA solution, but that is what you'll need.

If you're interested in learning, it's easiest to start with Excel. Here's a couple sites you can start with:

http://www.cpearson.com/excel/topic.htm
http://j-walk.com/ss/excel/index.htm

I personally think the books are terrible. Something like this is pretty cool. I got a complimentary copy from the seller, and it's fairly easy:

http://www.add-ins.com/vbcollection.htm

For the VBA code, perhaps XLGuru can help you out here. Let's see if he shows up. Personally, I just can't think when the email event should fire. There's a workbook change event, but you wouldn't want to send an email everytime it was changed--only when it was changed then closed. And changing when closed isn't good, 'cause you'll get an email everytime someone opens it. We could do it "before save", but I'm not sure that'll do it.


----------



## XL Guru (Aug 30, 2003)

>> Let's see if he shows up.

<bg>, nice one,  ; but seriously, I also can't see when you'd fire it. It's beyond my coding capabilities for sure. If you poke the right words into a Google search you can get a lot of pointers, eg:

http://www.google.co.uk/groups?hl=e...anged&safe=images&ie=UTF-8&oe=UTF-8&lr=&hl=en

, tho' I've never managed to get this kind of thing working personally.

*If* only a few can change it and "they" want to know when it is changed, I honestly think it would save a lot of hassle to set up an email group.

Rgds,
Andy


----------



## CastleHeart (May 4, 2002)

What about.......

Code to prevent unlocking protection unless an "EDIT" button is first pressed.

Code that puts an Email into a particular cell when the "EDIT" BUTTON is pressed

Code that sends an Email to the address listed in that cell (if there is one) upon saving the file

Code that deletes the contents of that cell upon opening of the file.

?

or some morph of that logic.

-C


----------



## Anne Troy (Feb 14, 1999)

Well, I know it's just as easy to send the given email to a group as it is to one person.

Can the email go via Outlook? Or does it have to be sent regardless of default email program?

Need to know the version of Office. If Excel and Outlook version don't match, need to know version of each.

Will this "special" group be looking a lot WITHOUT changing? We could say "if the user name from the Windows login is Grumpy, Doc, or Sneezy, then email them when the file is closed".

What do you want the subject and body of the email to read? Do you need it to say, for instance: "Grumpy just edited the file."?


----------



## greggio (Oct 14, 2003)

Sending to a group is not a bad idea. 

Everyone is using Office XP.

Using the windows login name would work as well. As for the subject, "Grumpy just edited the file." is great.



Thanks!


----------



## CastleHeart (May 4, 2002)

There are only three in the group. 
Bashful is to shy to send Email. 
Happy just sits at his desk and smiles a lot. 
Sneezy isn't allowed to use the computer because he gets the keyboard sticky. 
And Dopey..... well he tries, but.....




 







All mails should be cc: S. White


----------



## Anne Troy (Feb 14, 1999)

You......are......such......a......dork.

I've given greggio (via email) instructions on where to take his criteria and get some free code. Hopefully, he'll get it and copy the code back here when he's done. It should only take him a day or two.


----------



## CastleHeart (May 4, 2002)

it's so nice to be wanted!


----------



## CastleHeart (May 4, 2002)

I forgot to mention Sleepy.

He doesn't need an email. He'll just read S. White's cc


----------



## Anne Troy (Feb 14, 1999)

My favorite TSG graphic from Mulder--used WITHOUT permission:


----------



## greggio (Oct 14, 2003)

I got my answer thanks to dreamboat. Here is the code that I got:

Private Declare Function getUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Function getLoggedUserName() As String
Dim strBufferString As String
Dim lngResult As Long
strBufferString = String(255, Chr(0))
lngResult = getUserName(strBufferString, 255)
getLoggedUserName = Replace((Mid(strBufferString, 1, 255)), Chr(0), "")
End Function

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim olapp As Object
Dim olmail As Object
Dim LoggedUserName As String
Dim LoggedUserEmail1 As String
Dim LoggedUserEmail2 As String
LoggedUserName = getLoggedUserName
'*****CHANGE EMAIL ADDRESS HERE - MyCompany.com part********
LoggedUserEmail1 = "greg.giordano" & "@eyetk.com"
LoggedUserEmail2 = "demetra.barlas" & "@eyetk.com"
LoggedUserEmail3 = "richard.sullivan" & "@eyetk.com"
'*****CHANGE EMAIL ADDRESS HERE********

Set olapp = CreateObject("Outlook.Application")
Set olmail = olapp.CreateItem(0)
With olmail
.Subject = LoggedUserName & " has edited " & ThisWorkbook.Name & "Test 100"
.To = LoggedUserEmail1
.CC = LoggedUserEmail2 & ";" & LoggedUserEmail3
.Send
End With
If olapp.ActiveExplorer Is Nothing Then
olapp.Quit
Set olapp = Nothing
End If
End Sub





I added a couple extra people in the "LoggedUserEmail" part. There was only one originally, but I I added the others and then added the ".CC" field. It seems you can keep adding people to that field. It took me a while to figure out that you had to use an "&" between just about everything, and that I had to put the ; in quotes.

All in all, it wasn't that bad. I think I am going to like this stuff.


----------



## Anne Troy (Feb 14, 1999)




----------

