This is a bit of a messy one. Our ERP system outputs a spreadsheet every day with the day's deliveries on it, that has the delivery reference number, along with the customer name. That gets stored in the same place on a network drive every day, replacing the previous day's file.

Every afternoon the ERP outputs quality certificates as PDFs for the day's deliveries and emails them to the customer and to my office. The emails have the delivery reference number on them but not the customer name, meaning that the PDF files have to be opened to find out which customer it belongs to. We get quite a lot of them so it's a bit of a waste of time, and I want to use the details in the spreadsheet to append the customer name to the email subject when the email is received.

The way I figured I would do that would be to use the NewMailEx event to trigger an SQL query of the closed spreadsheet, put that data into an array, and then lookup the data in the array to find the information needed to append the customer name to the subject line.

So far I have the following in ThisOutlookSession:

Please Login or Register  to view this content.
And this in a module:

Please Login or Register  to view this content.
(With obvious apologies to Ron de Bruin)

The Excel library is enabled, and the code executes with no errors. But as far as I can tell (using COUNTA) the array is length 1 with no data in it. Does anyone have any idea where I'm going wrong?