importing data from outlook with excel

[Ash]

Hi, I want to import data to Excel 2000 that comes from a website stats app. This data will be emailed to me at 12.00am on a weekly or monthly basis and it will then need to be dropped into Excel. So I think I need to know how to get Excel to automatically import a file from an external source but in that case how does Excel know that the file has arrived, is there some sort of date/time function to it or do I have the wrong tool for the job and should be looking at a DB to do this for me? I wanted to do this in Excel because it would automatically update the charts it generates. Thanks for any help, great board.

[Dreamboat]

Hi, Ash. Welcome to the board.

Tho I'm no coder or super-geek, the ones who are will likely have questions for you:

Is the data attached as a file to the email or is it in the message body of the email? If the latter, what format is it in?

Do you have the option of "picking up" the data yourself instead? I think it'd be easier to automate a web query than to automate a receive file or email.

[Ash]

Hi Dreamboat, thanks for your reply, the data will be in .csv format attached as a file to the email. I have the option of 'picking up' the data myself but as this would probably involve an even more complex operation than emailing the files I'd rather try this way first. The application the data comes from is a server based web stats program with a built-in email client, I can automate the program to email reports to me in .csv format but from what I've read so far a web query seems to involve a specific URL and the program I use generates these reports in a cgi-based interface where the actual URL never changes.

[XL-Dennis]

Hi Ash,

Before getting into code etc let me present a frameidea that might be of interest:

Create a VBA-solution that
- import all the data into XL
- clean it up (if necessary)
- add it to the chartserie

Now this can be done either in Outlook OR in XL and saved as an Add-in.

From my point of view the mainproblem/issue is how to trigger the above event.

Since it's only once per month the best way is via a button on a commandbar

[Ash]

Hi XL-Dennis, if by the above event you mean having the program I use automatically/remotely producing the individual reports then the above event is insurmountable. It cannot be done through automation. Perhaps if I outline what I want to do and why then someone may have an idea of how to go about it.
I produce web stats reports for 15 different sites every month in XL and instead of manually inputting the data into the sheets I would like to have the program I use email the data to me and then somehow extract that data and place it in the sheets. The first problem I have is how to take that data out of the emails and place it in XL, which is why I'm here. I have read that Outlook can export data so maybe I have this back to front and it's Outlook I should be asking about and not XL. Any thoughts?

[Dreamboat]

Hi again, Ash.

Outlook can export its objects, not email text or attachments, as data. At least not to my knowledge. However, I believe Dennis might be referring to having the attachment auto-saved in some way....

Anyway, just wanted you to understand about Outlook export capabilities. Meaning you can Export your contacts (for instance) to an Excel file...

[XL-Dennis]

Hi Ash,

No consideration is taken from my side about the CGI-based e-mailgenerating software You use.

I only discuss from the point where the e-mails arrives into an e-mailclient Inbox and how to handle them from that point. OK?

The first problem I have is how to take that data out of the emails and place it in XL

OK - Which e-mailclient do You use, i e in which the e-mails arrives in? MS Outlook?

....csv format attached as a file to the email...

Are the attached files saved to the drive?

(Attached to e-mails does not necessarily mean that the attached files are saved.)

----------------------------------------------------

If yes to both questions then I don't see what I'm missing in my first reply.

The mainproblem is to have the notification as a trigger to automatically read the files and import the data.

I simplified the frame-idea with a button-approach in my first reply which is supposed to trigger the process to read and import.

[Ash]

Thanks for your help, this is really good of you both.
The email client I use is Outlook but I can use OE if thats any easier to do this in. The attached files would be right where they are when they arrive in the email.