In each report, when opened (each report is a *.csv file), we see the name of the report (without characters or numbers) in A1. A3 will have something similar to the following:
Date Range Reporting On: Jan 04, 2012 00:00 CST - Jan 04, 2012 23:59 CST
This date range is important with all files.
In the workbook Reports Conversion.xlsm I have a worksheet named "Run Reports".
On this worksheet in B1 and B2 I have a droplist with dates which read off a table on the "Tables" worksheet.
The droplist will show dates beginning with Yesterday's (Tables range A4 begins with =TODAY()-1 ) and carries down for a total of eight dates.
These dates will be selected by the User prior to running the reports this macro file is intended for. For the most part the dates selected for the Beginning (B1) and Ending (B2) Dates will be the same, selected as yesterday's. On Mondays we will run the reports separately for Friday and Saturday and then on Tuesday we will run the reports separately for Sunday and Monday. These are normally done manually and if this works correctly I expect Friday, Saturday and Sunday to be run on Mondays.
Each day there is one report we will run from the current week's Sunday to the day before the report is run "Yesterday". ie if we are running Friday's report we run all of them based on Fridays date for both B1 and B2. When we run that one specific report (we currently call report_week) will run it from Sunday's date (B1) to Friday's date (B2). Keep in mind Sunday would be only run the once with Sunday's date selected for both B1 and B2.
What I would like to have is a macro open one attachment at a time from outlook, then checks the name of the report in A1 and then compares the range dates appearing in A3. If they correspond with the name identified in the macro, then run the script I prepared for the report. Then go to the next attachment.
If it needs to open each file separately that would be fine. The files may arrive in different emails, but should arrive in a specific folder so I hope that would help select the email.
IF opening the files from the emails then I'd be find with manually opening each file, having them all opened and then having this part of the macro find the correct file to run.
I can do most everything but the confirming the report name and dates (and grabbing the files from the emails). Could someone help me with this?
I'd very much appreciate this.
Thank you
- g
Date Range Reporting On: Jan 04, 2012 00:00 CST - Jan 04, 2012 23:59 CST
This date range is important with all files.
In the workbook Reports Conversion.xlsm I have a worksheet named "Run Reports".
On this worksheet in B1 and B2 I have a droplist with dates which read off a table on the "Tables" worksheet.
The droplist will show dates beginning with Yesterday's (Tables range A4 begins with =TODAY()-1 ) and carries down for a total of eight dates.
These dates will be selected by the User prior to running the reports this macro file is intended for. For the most part the dates selected for the Beginning (B1) and Ending (B2) Dates will be the same, selected as yesterday's. On Mondays we will run the reports separately for Friday and Saturday and then on Tuesday we will run the reports separately for Sunday and Monday. These are normally done manually and if this works correctly I expect Friday, Saturday and Sunday to be run on Mondays.
Each day there is one report we will run from the current week's Sunday to the day before the report is run "Yesterday". ie if we are running Friday's report we run all of them based on Fridays date for both B1 and B2. When we run that one specific report (we currently call report_week) will run it from Sunday's date (B1) to Friday's date (B2). Keep in mind Sunday would be only run the once with Sunday's date selected for both B1 and B2.
What I would like to have is a macro open one attachment at a time from outlook, then checks the name of the report in A1 and then compares the range dates appearing in A3. If they correspond with the name identified in the macro, then run the script I prepared for the report. Then go to the next attachment.
If it needs to open each file separately that would be fine. The files may arrive in different emails, but should arrive in a specific folder so I hope that would help select the email.
IF opening the files from the emails then I'd be find with manually opening each file, having them all opened and then having this part of the macro find the correct file to run.
I can do most everything but the confirming the report name and dates (and grabbing the files from the emails). Could someone help me with this?
I'd very much appreciate this.
Thank you
- g