Solved: Emailing file from excel

[tkaplan]

I have a file which I want that every time there is a change made to it, before i close the file it should be emailed.

This file is housed on a shared drive and someone with no access to the drive needs to know about any changes.

here's what i tried to do, i'm not sure if it'll work:


Option Explicit
'i only want to email the file if i make changes and save the changes
Dim ChangesMade As Boolean
Dim ChangesSaved As Boolean

'checking if made changes
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ChangesMade = True
End Sub

'here i'm checking when changes are saved
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ChangesMade = True Then
ChangesSaved = True
End If
End Sub

'if changes are made and saved, before the workbook is closed, email the file
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'i need help here:
'want to email file as attachement to email address "tkaplan@try.com"
'with subject line "changes to tracker made"

End Sub


any help in putting the code into that last bit and telling me if this would actually work i would really appreciate it.

thank you
tkaplan

[mvidas]

Hi tkaplan,
Try the following: 'if changes are made and saved, before the workbook is closed, email the file
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'using your current method (why do you have ChangesSaved?):
If ChangesMade Then
ThisWorkbook.SendMail "tkaplan@try.com", "Changes to tracker made"
End If

'However, you could remove the _sheetchange and _beforesave events, and the
' boolean variables, and just have the following in _beforeclose
If Not ThisWorkbook.Saved Then
ThisWorkbook.SendMail "tkaplan@try.com", "Changes to tracker made"
End If
End SubMatt

[tkaplan]

the reason i have ChangesSaved is because if changes were made but the user did not save the changes, i do not want the file emailed. if the user saved the file but did not make changes, i dont want it emailed either. i only want the file emailed if changes were made and changes were saved.

i'll try what you have there and post back.
thank you.

[mvidas]

You'll want to change it to "If ChangesMade and ChangesSaved then", and remove my "If Not ThisWorkbook.Saved Then" block, since it would be of no use to you.

[tkaplan]

Ok, here's where I'm struggling:

If Not ThisWorkbook.Saved Then
this tells me that this will only run if the workbook is not saved? am i missing something?

also, i realized i had the SheetChange event. i really need just a "change" event- if any changes are made to the sheet. what event would i use for this?

[mvidas]

_SheetChange is a fine one to have, because it will detect a change on any sheet you have.

I was using ThisWorkbook.Saved which just tells you if a workbook has changed since the last save (if thisworkbook.saved=false, you get the "This workbook has not been saved" when you close the file, uses same flag).

Since I misunderstood your need (email when closing file if 1) changes have been made AND 2) file saved). The following should work fine:Option Explicit
'i only want to email the file if i make changes and save the changes
Dim ChangesMade As Boolean
Dim ChangesSaved As Boolean

'checking if made changes
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ChangesMade = True
End Sub

'here i'm checking when changes are saved
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ChangesSaved = True
End Sub

'if changes are made and saved, before the workbook is closed, email the file
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ChangesMade And ChangesSaved Then
ThisWorkbook.SendMail "tkaplan@try.com", "Changes to tracker made"
End If
End SubMatt

[tkaplan]

do you know if there is any way to do this directly from outlook.
is there a way that from outlook i can set that an email is sent every four hours or at certain times of day to a specific recipient, with a specific subject with a specific attachment?

[tkaplan]

this will work for now.
If anyone knows of a way to do it from outlook though i'd appreciate that.

Thank you for all of your help:)

[mvidas]

Hello,

Sorry, I didnt see your last comment yesterday. There are ways to send an email from outlook at scheduled times, but it wouldn't send it if the file changed/etc.

I can give you a method that will send it at scheduled times, but it wouldn't be from outlook (this way you can continue to have it mailed even when outlook is closed, as long as the computer is on), is this ok for you?