Autmatic run macro, closing workbook.

Chris! · May 31st, 2013, 02:12 PM

Dear all.

I would like a macro to run automatically if the workbook closes.

The macro takes care of 2 things:
- Saving the data;
- Send an automated E-mail 'End Of Day' report.

Here is what I have so far:

Code:

Sub Run_On_Close()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Save & close workbook and e-mail report?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Run close sequence?" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
        ' context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
    MyString = "Yes" Sub SendIndividual().
    ThisWorkbook.Close
       
Else ' User chose No.
    MyString = "No" ' Perform some action.
End If
End Sub

Public Sub SendIndividual()
    'Clear out any old data on Report
    Sheets("Report").Select
    Range("A1").CurrentRegion.ClearContents
    ' Sort data by region
    Sheets("Data").Select
    Range("A1").CurrentRegion.Select
    Selection.Sort Key1:=Range("A2"), Header:=xlYes
    ' Process each record on Distribution
    Sheets("Distribution").Select
    FinalRow = Range("A15000").End(xlUp).Row
    For i = 2 To FinalRow
        Sheets("Distribution").Select
        RegionToGet = Range("A" & i).Value
        Recipient = Range("B" & i).Value
        ' Clear out any old data on Report
        Sheets("Report").Select
        Range("A1").CurrentRegion.ClearContents
        ' Get records from Data
        Sheets("Data").Select
        Range("A1").CurrentRegion.Select
        ' Turn on AutoFilter, if it is not on
        If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
        ' Filter the data to just this region
        Selection.AutoFilter Field:=1, Criteria1:=RegionToGet
        ' Select only the visible cells and copy to Report
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy Destination:=Sheets("Report").Range("A1")
        ' Turn off the Autofilter
        Selection.AutoFilter
        ' Copy the Report sheet to a new book and e-mail
        Sheets("Report").Copy
        Application.Dialogs(xlDialogSendMail).Show _
            arg1:=Recipient, _
            arg2:="Report for " & RegionToGet
        ThisWorkbook.Close SaveChanges:=True
    Next i
End Sub

Shasur · June 1st, 2013, 04:13 AM

Hi

Can you please try Workbook_BeforeClose event. It should help you

Cheers
Shasur