Email Notification from excel when changes are made

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

greggio

Thread Starter
Joined
Oct 14, 2003
Messages
8
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?
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
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

Thread Starter
Joined
Oct 14, 2003
Messages
8
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!
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
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.
 
Joined
Aug 30, 2003
Messages
2,702
>> 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
 
Joined
May 4, 2002
Messages
743
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 :confused:
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
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

Thread Starter
Joined
Oct 14, 2003
Messages
8
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!
 
Joined
May 4, 2002
Messages
743
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.....




:D







All mails should be cc: S. White
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
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.

:)
 

greggio

Thread Starter
Joined
Oct 14, 2003
Messages
8
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top