How do I run a time consuming VBA function routinely without interfering with my work?

I have written a VBA function in MS Outlook that takes about 30 seconds to run. It needs to open and read data in a spreadsheet as well as loop through emails. I need the application to run every 30 minutes or so to see if there have been any new emails arrive or any updates to the spreadsheet. The trouble is that if I am using MS Outlook at the time it will interrupt and make me wait so I was wondering if there is a way to run it without interfering.

I was thinking that perhaps it may be possible to create an instance of Outlook and Excel using VB Script or perhaps running it from another MS product that I don't use very often like Publisher or Powerpoint, or perhaps I should create a virtual machine and leave an instance of Publisher running on it (though that will likely be very resource hungry). I don't think this is a new problem so I am hoping someone has a relatively straightforward suggestion.
Rob4077Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You cannot create another instance of Outlook - it is the only Office application not allowing to do so.
What I do for long running tasks (e.g. checking EE email notifications against the site for being solved already etc., see one of my articles if interested ;-) ), is to add DoEvenets in the loop, sometimes more than once. That allows Outlook to respond to user interaction, refresh views and the like, and does not slow down VBA (significantly).

A more sophisticated approach is to use Outlook like a database (via Jet drivers) from an external application. That works if you need read-only access to the mails. However, I have no issue regarding performance and blocking of Outlook while processing data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Thanks Qlemo. Where would I find one of those articles? I don't really understand how DeEvents can help. I thought that once a VBA function is running in Outlook it would keep running until its finished. Are you saying that if, for example, I put a DoEvents after each line of my VBA (exaggerated use I know) the code would loop through my emails and do its work while letting me select other emails, look at and reply to them? That sounds too good to be true
0
Rob4077Author Commented:
Wow! I just tried it and it works brilliantly. I had no idea that's what DoEvents did. That is soooo cool. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.