TexasBobcat
New Member
- Joined
- Aug 14, 2015
- Messages
- 3
Excel Gurus,
Below is my VBA to refresh a file upon opening, emailing out, then closes application.
The problem is when I go to my email and open the file I send, it runs the macro again.
How can I make this VBA more efficient as I do not want my end users to have the macro file?
_________________________________________________________________________
Sub Aut
pen()
'
' dailyrun Macro
'
Call Refresh
Call emailfiles
Call disablemacros
Call save
End Sub
______________________________________________________________________
Sub disablemacros()
Application.EnableEvents = False
'code to format and save
Application.EnableEvents = True
End Sub
______________________________________________________________________
Sub save()
ActiveWorkbook.Close True
End Sub
________________________________________________________________________
Sub emailfiles()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "Fake Email <fakeemail123@yahoo.com>"
.CC = "Fake Email <fakeemail123@yahoo.com"
.BCC = ""
.Subject = "YTD President's Club Pacing Report"
.Body = "directly from macro"
.Attachments.Add Application.ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
End Sub
______________________________________________________________________________
Sub Refresh()
'
' Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub
Below is my VBA to refresh a file upon opening, emailing out, then closes application.
The problem is when I go to my email and open the file I send, it runs the macro again.
How can I make this VBA more efficient as I do not want my end users to have the macro file?
_________________________________________________________________________
Sub Aut
'
' dailyrun Macro
'
Call Refresh
Call emailfiles
Call disablemacros
Call save
End Sub
______________________________________________________________________
Sub disablemacros()
Application.EnableEvents = False
'code to format and save
Application.EnableEvents = True
End Sub
______________________________________________________________________
Sub save()
ActiveWorkbook.Close True
End Sub
________________________________________________________________________
Sub emailfiles()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "Fake Email <fakeemail123@yahoo.com>"
.CC = "Fake Email <fakeemail123@yahoo.com"
.BCC = ""
.Subject = "YTD President's Club Pacing Report"
.Body = "directly from macro"
.Attachments.Add Application.ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
End Sub
______________________________________________________________________________
Sub Refresh()
'
' Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub