Using Excel's OnTime method in Outlook

[Ruddles]

I'd like to monitor file activity in a folder on my hard disk from Outlook to enable me to generate emails depending on that activity. I couldn't see anything in Outlook which corresponds to Excel's OnTime method, so I tried to 'borrow' it from Outlook like this:-

Sub UseExcelOnTime()
 
  Dim objExcel As Excel.Application
  Set objExcel = CreateObject("Excel.Application")
  
  objExcel.Visible = True
  objExcel.Wait Now() + TimeValue("00:00:05")
  objExcel.OnTime Now() + TimeValue("00:00:05"), "TestSub"
 
End Sub

Sadly this doesn't work. The Wait works okay but the OnTime complains that there's no macro called TestSub in the workbook when I single-step through the code and crashes when I let the code execute at full speed.

Any ideas for scheduling events to run in Outlook? As the mailbox may not receive mail for hours at a time I can't use the NewMail event.

 

[GlennUK]

When you say "monitor file activity in a folder on my hard disk from Outlook" ... what are you doing exactly?

 

[Ruddles]

I said 'my hard disk' but it's actually a subdirectory on a server.

There's a process on another machine dropping files into that folder. I need to detect when a file appears, read some data from it and create an email.

 

[GlennUK]

So wouldn't it be better to program this within Excel, using the OnTime method, and generate the email from there?

http://www.mrexcel.com/archive/VBA/4219.html

 

[Ruddles]

Excel's not running. The user has Outlook open all day and I thought that would be a neater solution.

My fall-back position would be to use Application_MAPILogonComplete() to fire up Excel and kick off a series of OnTime events to check the folder every couple of minutes, then use Application_Quit() to close Excel when the user shuts down Outlook.

Also, purely for my own curiosity, I've called Excel functions in Word and Access and used the dictionary from Word in Excel, it just seems that I ought to be able to use OnTime from Outlook.

 

[John_w]

There's a process on another machine dropping files into that folder. I need to detect when a file appears, read some data from it and create an email.

Have you considered WMI for monitoring the file creation?

http://blogs.technet.com/b/heyscrip...ent-types-of-events-with-just-one-script.aspx

The code shown is VBScript and should work unchanged in VBA. Note though that colMonitoredEvents.NextEvent() needs to be called in a continuous loop, so I'm not sure how you would handle that in Outlook VBA.

Alternatively, you could do the whole thing (file detection, read data, create email) in VBScript and run it as a separate scheduled task.

 

[Ruddles]

Thanks for the suggestion - I'll give it a go in the morning. Time to sit back and watch Coronation Street!

 

[Jaafar Tribak]

Why not run the monitoring code inside a loop in outlook instead of using Ontime ?

Do
'Code to monitor the folder here
Doevents
Loop

 

[Ruddles]

I've cobbled something together whereby when Outlook starts it opens an Excel workbook in the background. The Excel workbook uses OnTime to monitor the folder every few minutes and if it finds a file, it creates a new mail item if Outlook is still running. If Outlook has been closed, the Excel workbook closes itself.

The only issue is that the workbook has to remain active all day for the system to work... we'll just have to see how it goes.