I think you have specified the exact problem and solution yourself. As I
see it, there is not enough consistency in the workbooks, or email  names
being received. Whilst it is possible to code for these exceptions, it
would be much easier to just enforce a common standard onto your data
supply group.

Things like different starting  points in workbooks, or different sheet
names should not be allowable.

Your task is rather complicated due to the lack of consistency with
email and spreadsheet data. It may be worth while to look outside the
box on this one. Is all this information being sent to you internally or
from outside business contacts? Depending on the answer you may be
better off speaking with management about creating, or using an existing
database and have internal staff enter data  this way. If you are dealing
with external contacts, then it's time to discuss having them send you
tab delimited text files. This not only helps enforce data integrity but
you can have your contacts upload the files via FTP to your network
server. Then by writing a script, have your program search for each file
and load it automatically into your database.