Would this architecture realistically work? 1

[DrMingle]

I'm not sure if this would work and wanted to get out there among you all to see what your thoughts were. Below is the staged processes as I think it would need to progress:

Let me know your thoughts!

Stage 1
• Auditor sends Excel file attachment to Outlook inbox
• Outlook message rule routes to inbox sub-folder (Example: Inbox>Auditors/Net MGMT>Leah Street)
Stage 2
• Timed Macro (Runs at 5:30 PM)
• Timed Macro = Outlook macro runs at 5:30 PM to pull all attachments in designated files in Outlook with the subject line “Visual Audit” to a designated folder on the desktop.
o Macro needs to not only save attachment, but delete file within Outlook inbox sub-folder (so the following day there is no overwriting of files)
o Macro needs to only run on weekdays
Stage 3
• The macro which saves the requested “Visual Audit” file to a desktop location needs to be able to trigger another macro to copy critical data from the saved file and past it into an existing Excel file

 

[starsky51]

Hi DrMingle,

You would be better doing stage 2 with a VBScript. These are easier to schedule (using the Windows Scheduler) than VBA macros.

If you haven't worked with VBScript before, write a VBA module to perform the task and then copy the text into a vbs file. A few changes will have to be made to the code to convert it into a VBScript. You will be able to find advice for this on Google.

Stage 3 could be done in VBA and called from VBScript, but it would be better to do the whole thing in VBScript.

It sounds like a lot of work, but i'm sure it could be implemented.

Dave.