Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

email alert notification

0

How do I get notified by an email when my excel file in one drive shared folder gets updated by client

Answer
Discuss

Discussion

Hi Soumya and welcome to the Forum.

Are you and your client using Outlook and do they agree that you get notified? Is your OneDrive file used by a single client or several from the same domain?
John_Ru (rep: 6102) Oct 20, '22 at 5:52 pm
Yes we are. yes they are. single client
Soumya Oct 21, '22 at 3:30 am
Thanks. Will try to answer this afternoon (GMT)
John_Ru (rep: 6102) Oct 21, '22 at 3:39 am
Soumya. I'm a bit surprised to get no response to my Answer from you. Please let me have your commnets and/or mark my Answer as Selected (if it works for you)
John_Ru (rep: 6102) Oct 25, '22 at 7:12 am
Add to Discussion

Answers

0

Soumya 

Provided your user uses Outlook as  the email client, you can do it when the user saves the file (using the VBA workbook event AfterSave).

In the attached file, this simple code is under ThisWorkbook:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

' get the module code to send an email
Call Email_From_Excel_Basic

End Sub

(You can replace the ProjectTimeline sheet with your sheets)

The bold bit calls another macro (under Module1, based on a tutorial from Don), as follows. You may need to change the bits in bold (especially the email addresses) in VB Project Explorer: 

Sub Email_From_Excel_Basic()
' TeachExcel.com

Dim emailApplication As Object
Dim emailItem As Object
Dim MyClient As String, MyRecipients As String

' name your client and your email address(es)
MyClient = "Joe Somename"
MyRecipients = "email@test.com; anotheremail@test.com"

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)


' Now we build the email.

emailItem.to = MyRecipients

emailItem.Subject = "New file saved by client"

emailItem.Body = "At " & Now & ", " & MyClient & " saved this Excel file:" & vbCr & ThisWorkbook.FullName

' Send the Email
' Use this OR .Display, but not both together.
emailItem.Send

' Display the Email so the user can change it as desired before sending it.
' Use this OR .Send, but not both together.
'emailItem.Display

Set emailItem = Nothing
Set emailApplication = Nothing

' tell the user an email was sent
MsgBox "An email (advising of file save) was sent to " & vbCr & MyRecipients

End Sub

This code will also send you an email when you change that file but I guess you can live with that). When you save the file, look in your Sent folder under Outlook and you should see the email.

I've used a Message Box to tell the client (or you) that an email was sent. 

You can also do this in OneDrive itself without VBA apparently, following the Microsoft guidance here :Get notified when members of your team update your shared file

Hope this fixes your problem. If so, please remember to mark this Answer as Selected.

Discuss

Discussion

Did you try that? 
John_Ru (rep: 6102) Oct 25, '22 at 2:04 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login