Solved: Auto_Run macro that only auto_runs once

[jackdandcoke]

so I have this code


Sub eMailActiveWorkbook()

Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Subject = "Webathon Reporting"
.Body = "Here is the Report"
.To = "name@thing.com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Wb.FullName
.Send
End With

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing


Application.Quit

End Sub


That I want to use at the end of an Auto_Run macro that e-mails out a file. Unfortunately, if it e-mails out the file and this macro is the Auto_Open, it will simply open the file, update all the pivot tables then e-mail it to that person and quit again.

What I need is a modified part of this code that turns off the Auto_Open before it mails out the file. Does this make sense?

[Bob Phillips]

ARe you wanting this as a once only ever run macro, or one that runs every time that you open it, but not when others open it?

[jackdandcoke]

ARe you wanting this as a once only ever run macro, or one that runs every time that you open it, but not when others open it?

I want task manager to open this file 3 times on saturday (8am, noon, 5pm) and run the macro, but when anyone else opens the file, I don't want the macro to run.

[JKwan]

Set up an Environment variable say "runme" and set it to "YES" (of course, this is only to your profile)

now, in your code check the variable


Sub test()
If Environ("runme") = "YES" Then
Execute ....
end if
End Sub

[jackdandcoke]

I'm sure not sure how to do that. We log in with Novell, but I'm not sure how this would play out. Do you have some sample code about setting the environment that I could try and modify?

[JKwan]

I'm sure not sure how to do that. We log in with Novell, but I'm not sure how this would play out. Do you have some sample code about setting the environment that I could try and modify?

Right click on My Computer
Select Properties
Select the Advanced tab
Click the Environment Variables button (middle)
Top Section - User Variables for ..... Click New
Enter the info
Now, go back to your Excel code and run that little snippet that I posted and should execute.

By the way, you need to restart Excel in order to pickup that new Environment Variable.

[jackdandcoke]

It worked! Thank you!

[Bob Phillips]

<deleted> mis-post