• Subject: Outlook and Timesheet Submittal - Critique
  • Author: Greg Wilson
  • Date: 09 Apr
  • References:
Background: We are trying to automate timesheet submittal and ultimately automatic export to Quickbooks via a third party utility. I have developed an Excel timesheet program that enables submittal to an interim text file database. This works fine. However, we have many field staff that don’t have computers allocated to them. There will be a huge bottleneck on Monday morning when they all try to access the few computers available. So we want to enable many of these staff to enter their timesheets via email. (Remote access such as by FTP server is too expensive. Something to do with limited bandwidth.) Grand Plan: I have successfully implemented an email option from Excel via CDO. This works great. What I want is to have Outlook kept running on the server and utilize the Application_NewMail event to fire a macro that immediately transfers the contents of the new mail item to the same text file database. Then move the mail item to a subfolder to archive submitted timesheets. So Outlook only serves as a conduit to get the timesheet data into the database. Another benefit is that this situation enables subsequent development of a simplistic form of remote access. What I’m thinking is that you could email a message in a specific format that includes a password and file path. The same macro can recognize this format and send the file back as an attachment. And if the file path is incorrect, then instead send back a list of all the files and folders in the last part of the path that does not resolve to an error. You could use this info to (albeit slowly) drill down to the file. Not an elegant solution, but it is not needed very often (and it’s free). Problem: I don’t have Outlook at home to experiment with and I am no longer allowed to work on it at work. (A political issue. I’ll spare you the details.) I want to prove that this or something similar will work so that I have ammunition in favor of the Excel/Outlook approach. I don’t want to waste time barking up the wrong tree either if that’s the case. Request: Hoping a guru will critique the above plan. I am very experienced programming Excel but have only a few hours worth of experience programming Outlook. Not an IT guy. Most important issue is whether the NewMail event will fire and allow macro execution in this situation. Very appreciative of responses. Greg
09 AprOutlook and Timesheet Submittal - Critique.Greg Wilson
All times are in (US) Eastern Daylight Time (GMT -4:00)