Solved: open shortcut at 7:15pm every day?

[TrippyTom]

I have to send a daily status report to my manager every day now. So I created a template with the pertinent info so I could just change a few things and send it off.

But is there a way I can setup a daily timer to open the template at 7:15pm every day so I don't forget?

[Ken Puls]

... is there a way I can setup a daily timer to open the template at 7:15pm every day so I don't forget?

Sure you can.

Basically you'll need to create the macro to open your template, convert it to VBScript, and then schedule it to run via the windows scheduler.

If you can create the macro in outlook to open up your template, (or yell if you need help with that too,) I'm sure that either myself or Matt Vidas can help you convert it to VBScript. Assigning it to the scheduler is extremely easy.

[Killian]

... or you can create a task in Outlook, recurring daily, that has a reminder set for 7:15pm

If the reminder's not enough, you could enable application events (with a class), and use the Reminder Event, test the item for something (like the subject) then open a mailitem from the template.

[Ken Puls]

Good point, Killian. You could set a reminder if you wanted to do it the easy way. LOL!

The advantage of going the VBScript route is that you can open Outlook if it's closed though, then open the template. Could gain you that extra bit of comfort.

[TrippyTom]

Well, the problem is reminders are not good enough for me. I find the box that appears to be not very intrusive so I just press ESC when I see it.


That's why I wanted to physically open my template so it's more of a blaring statement saying, "Hey dummy! Before you leave you have to send this."

Classes are way beyong me still and I haven't done any VBA in Outlook so I'm clueless how it should look. If you could show me an example or something, I could probably work it to my needs.

[Ken Puls]

Hi there,

Okay, I can try to help. Issues I have, though, are that I don't use Outlook at work, and don't really use a ton of the features at home. Honestly, I can't say that I've ever knowingly created an email based on a template. But if you tell me what steps you did to create your template/email from it, I'll bet I can work out the code part. (Unless Killian beats me to it, which is a fair bet. )

[TrippyTom]

Actually, creating a template is simple. It's like creating a template in Word. Templates can be based off of anything: email messages, notes, tasks, etc.

I created a mock-up email, put my boss as the recipient and chose File > Save As (Outlook Template). This saves an .oft file in my \Office\Templates folder. I then dragged that into my shortcuts bar to bring it up every time I want to send that email to my boss (daily).

I just need code to open this shortcut at 7:30pm every day.

[Ken Puls]

I *may* get some time tonight to play with this, but I hear that my inlaws have some work for me to do tonight... You could start with this though...

Create a new text file and paste the following in there. Save the file with a .vbs extension instead of a .txt extension.

Sub EmailWithOutlook() 
    Dim oApp
    Dim oMail
     
     'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application") 
    Set oMail = oApp.CreateItem(0) 
    With oMail 
        .To = "myboss@somedomain.com"
        .Subject = "Here's that report you wanted!"
        .Display 
    End With 

    'Release Outlook
    Set oMail = Nothing 
    Set oApp = Nothing 
End Sub

It's untested, but I think it should create you a new email. You can test by closing Outlook (or keep it open) and double clicking the VBS file.

To schedule it, (on XP)
-Go to Start|All Programs|Accesories|System Tools|Scheduled Tasks.
-Add Scheduled Task
-Next
-Browse for the vbs file and select it
-Name it, select Daily and click Next
-Change your start time and select if you want it on weekdays only, and click next
-Add your password twice, and click Next
-Click Done

I didn't work out the template part, as I don't have Oultook here, but if I get time tonight, I'll take a look at it.

[Killian]

Well, we could just use the reminder event to temporarily disable the Esc key but if we're going to all that trouble, I suppose we should work out how to use the template.

First, let's dispense with the nervousness surrounding classes by making two points:
1) A class is just a template
2) You already use them (a UserForm is a class - a relatively complex one too)

When you create a mail template (or Word dot, etc) you start with a blank item, add some customization (content, behaviour, etc) and save it as a template.
You use a template by creating an instance of the (mail) object from the template (leaving the original) and it inherits all the template customization.

A class is just the same.
You add a class module and create some customization code that defines it's behaviour.
You use a class by creating an instance of it. This instance, assigned to an object reference (variable), inherits the behaviour defined in the class module.

Example:
Add a class. Rename it "cTest"
An empty class has two self-explanatory events - Initialize and Terminate. Add some code so we can see the behaviour[VBA]Private Sub Class_Initialize()
MsgBox "Class_Initialize code"
End Sub

Private Sub Class_Terminate()
MsgBox "Class_Terminate code"
End Sub[/VBA]
In a standard module, you will need an object reference to assign the class instance to. Declaring it at module level means its lifetime will be the same as the project and we can access it from all routines in that module (to control it).
Then you just need a routine that assigns a new instance of the class to the variable and one to destroy it and we're done[VBA]Dim myClass As cTest

Sub CreateTestClass()
Set myClass = New cTest
End Sub

Sub DestroyTestClass()
Set myClass = Nothing
End Sub[/VBA]And thats a working example of a class.

Now to the business at hand...
It's time to introduce the "WithEvents" keyword (Don't panic). Used in a variable declaration, it specifies that the variable is an object variable used to respond to events triggered by an ActiveX object.
So using it in a class with an object of type, Application, will give us some application events to play with.

Add a new class module. Name it "cAppEventClass"
In the empty class, we need some code to enable app events when an instance is created and use the reminder event to do something useful[VBA]Dim WithEvents m_app As Application

Private Sub Class_Initialize()
Set m_app = Application
End Sub

Private Sub m_app_Reminder(ByVal Item As Object)
If Item.Subject = "Daily Status Report" Then
Item.Dismiss
'open template
End If
End Sub[/VBA]As with the example, you now need a standard module for your object variable and a routine to create the instance of the class[VBA]Dim myApp As cAppEventClass

Sub InitAppEventHandler()
Set myApp = New cAppEventClass
End Sub[/VBA]You can use the ApplicationStartup event in the ThisOutlookSession module to call InitAppEventHandler so now when you launch the app, the class instance is created and sits and waits for Reminder events to happen.

OK, so I missed out the open from template code. No idea. I'll have a look tomorrow (Unless Ken beats me to it )

[TrippyTom]

Killian,

Are you a teacher perchance? I was excited because your explanation was perfect for me (I can relate to templates). I understood everything up until the part where you say "don't panic".

Then I started reading slower and going over it multiple times hoping it would sink in, but this stuff is like calculus: you can explain it perfectly but I'll never be in the right frame of mind to understand.

I'm almost done at work, so when I get home I will continue to soak up this info and maybe it will just hit me like a brick and I will laugh at myself for not understanding it the first time.

On the bright side: my reminder task is working. It just went off (at 7:15pm) and I pressed ESC like I usually do.

[Ken Puls]

Okay... let's try this:

[vba]Private Sub m_app_Reminder(ByVal Item As Object)
Dim MyItem As Outlook.MailItem
Dim myTemplatePath As String

'Set the path and name of your template here:
myTemplatePath = "C:\somefolder\sometemplate.oft"

If Item.Subject = "Daily Status Report" Then
Item.Dismiss
'open template
Set MyItem = myOlApp.CreateItemFromTemplate(myTemplatePath)
MyItem.Display
End If
End Sub[/vba]

I haven't tested it extensively, though, so it may still error. Make sure you change the path to your template.

Also, just in case anyone else comes across this, this is a link to a page on how to set up the templates. You have to disable Word as your email editor to do it.

[Ken Puls]

And this is the code to do it through VBS. Paste the entire bit in a text file, update the template path, save as a vbs file and schedule it to go. This will open Outlook if it's not running, so that you never forget to send it.

EmailWithOutlook

Sub EmailWithOutlook() 
    Dim oApp
    Dim oMail
    Dim myTemplatePath

    'Set the path and name of your template here:
    myTemplatePath = "C:\somefolder\sometemplate.oft"

    'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application") 
    Set oMail = oApp.CreateItemFromTemplate(myTemplatePath)
    oMail.Display

    'Release Outlook
    Set oMail = Nothing 
    Set oApp = Nothing 
End Sub

There you go. Two ways to skin the same cat.

[TrippyTom]

Ken, I would rather go with the first example since I'm not sure my I.T. department would like it if they saw a scheduled process in my login account at work. They might get paranoid and ask me if I'm trying to sabotage the network.

So, I put your example code in a standard module in Outlook and it didn't seem to work when my notification popped up. Did I put it in the wrong place?


Thanks,
Tommy

p.s. -- I'm trying this at home on Office2003 (maybe it will work when I try it at work on Office2000)

[Ken Puls]

Hi Tommy,

The routine I gave above replaces the "Private Sub m_app_Reminder(ByValAs Object)" code in the cAppEventClass, as provided by Killian above. All part of the class module.



Now, as for the VBScript... you're using code. VBA or VBScript is the same risks IMHO, since you're the one creating and deploying it. I wouldn't run any old VBScript you came on, but then I wouldn't run any old macro either.

[TrippyTom]

Either I'm doing something wrong or it's a security issue.
I will try it when I get home and see if it's any different.

I even went to the Scheduled Tasks window under Start > Programs > Accessories > System Tools, tried to add a new scheduled task and nothing happened.

Ok, I tried this at home and it's not firing as well. I named my task "Daily Status Report", so the routine should work when it pops up, right?

[Ken Puls]

Tommy,

Did you modify Killian's class module code, save, close and reopen Outlook? As soon as you modify the class, it will destroy the instance, so it needs to be recreated.

I'm heading out the door right now, but will play with it when I get home tonight.

The vbs route should work with the scheduler, but the VBA code won't.

[Ken Puls]

Hmmm...

Killian, I've got something weird here. I can make things work up to the Item.Dismiss part. That keeps coming back with an error about being an invalid property or method. Strangely, when you view the locals for the routine (stepping through), Item is not defined. What's even weirder though, is that is does trigger the If clause successfully.

Tommy,

This is the full routine for the Class module approach that Killian started you on. In your cAppEventClass class module, replace all the code with this:
[vba]Option Explicit
Dim WithEvents m_app As Application

'Set the path and name of your template here!
Const myTemplatePath As String = _
"C:\Documents and Settings\yourusername\Application Data\Microsoft\Templates\"
Const myTemplateName As String = "templatename.oft"

Private Sub Class_Initialize()
Set m_app = Application
End Sub

Private Sub m_app_Reminder(ByVal Item As Object)
Dim MyItem As Outlook.MailItem
Dim myTemplatePath As String

If Item.Subject = "Daily Status Report" Then
'open template
Set MyItem = Application.CreateItemFromTemplate(myTemplatePath & myTemplateName)
MyItem.Display
'Item.Dismiss
End If
End Sub[/vba]
Modify the parts in red, above, with your correct info. Next, in the ThisOutlookSession module, place the following:

[vba]Option Explicit

Dim myApp As cAppEventClass

Private Sub Application_Startup()
Set myApp = New cAppEventClass
End Sub[/vba]
Close Outlook and save the project when prompted to do so. Reopen outlook.

It should work for you now, BUT, the reminder will not be dismissed.

With regards to the code, I did modify the script a bit to make it work (that myolApp part shouldn't have been there, and also made it a bit more readable by assigning some constants. Finally, I commented the Dismiss part that wasn't working. We'll wait on Killian for that to hear his thoughts on that part...

[TrippyTom]

Well, now I know one of my problems. I put it in a new Module instead of ThisOutlookSession. :P

But it's not working because Windows keeps setting the directory my template is in to READ ONLY. Even after I uncheck it in properties, I go back and it's set ReadOnly again. STRANGE! :P

In fact, the ReadOnly property goes all the way back to my \Documents and Settings folder.
How do I fix that? I guess I'll just move my template to a non-readOnly directory.

EDIT: I tried this at work and made the changes but I'm getting the same thing I did at home. My template is not ReadOnly at work. (see error msg below)

[TrippyTom]

Strange,

The problem was the variables.
I changed the line to:
Set MyItem = Application.CreateItemFromTemplate("C:\downloads\OutlookStuff\DailyStatusRe port.oft")

... and it works!
Thanks for your help guys! Why wouldn't those variables work in place of the actual path though? I'm just curious.

p.s. I actually DO NOT want it to dismiss the notification, because if it does, it unchecks it so it won't notify me the next day.

[Ken Puls]

Hmm...

You had the constants set up as follows?
[vba]
Const myTemplatePath As String = "C:\downloads\OutlookStuff\"
Const myTemplateName As String = "DailyStatusReport.oft" [/vba]

I'd assumed that you had your template in the default path, so that could be it.

Glad on the rest though.