Can an Excel Macro open Outlook attachments?

[AndyTampa]

I've searched the forum but can only find references to sending workbooks as email attachments in Outlook. What I want to do is the opposite.

I have to create reports from other reports that I receive in email. Those source reports are generated by a proprietary application overnight after the day is over. It sends the reports as file attachments. The subject lines of the emails are always the same except that the number of results found is appended to it. I take these reports (.csv files), open them in Excel, generate Pivot tables, and make new reports from them. I'm trying to automate as much of this as possible with macros.

I have several ideas but I don't know if they are feasible. I'm not asking for someone to write the macros for me. I just need to know what is possible before I waste alot of time on something that's impossible.

1) Can an Excel macro open Outlook, look in a particular folder of my inbox, find emails with a subject line where the first x characters match a particular string of text, and open the attachment before continuing on?

2) Can an Excel macro instruct the user what steps to take while it pauses and waits for a particular keystroke or sequence to continue?

3) Can an Outlook macro open an attachment into Excel and then call the Excel macro to begin? (This way is not ideal since my Daily report uses 4 emailed reports plus a PDF report.)

4) Does anyone know of a way to import the data from a PDF file to Excel?

Of course, any tips or ideas you can provide would be appreciated.

 

[Derek Brown]

You did not say which version of Office is being used but for Excel 2003 onwards I would say:
1. Yes. Using Excel/Outlook automation, this could be done from Excel or, using the newmail event, it could be done from Outlook.
2. I would consider using a non-modal form with the instructions and a 'Continue' button.
3. I would say 'Yes' but I would prefer to use Excel automation within Outlook VBA to do this.
4. Probably not using VBA. Able2Extract enables you to do this manually, with perhaps some limitations, but I have not come across anything that can do it from VBA.
This may be useful:
http://www.outlookcode.com/
I have a Data Warehouse system that is controlled from a Windows Server 2008 R2 system with Outlook constantly running to process Excel report requests and also process data submitted as Excel attachments - much can be achieved using Office automation.

 

[Michael M]

Hi Andy
Have a look here at JP Technologies...he posts on here as well !!

http://www.jpsoftwaretech.com/blog/2008/05/open-any-email-attachment-from-outlook/

As for importing from PDF, Unless you have the likes of Adobe Pro, it's unlikely.
There are many apps available that purport to copy and paste but most fail miserably.

 

[AndyTampa]

You did not say which version of Office is being used but for Excel 2003 onwards I would say:
1. Yes. Using Excel/Outlook automation, this could be done from Excel or, using the newmail event, it could be done from Outlook.
2. I would consider using a non-modal form with the instructions and a 'Continue' button.
3. I would say 'Yes' but I would prefer to use Excel automation within Outlook VBA to do this.
4. Probably not using VBA. Able2Extract enables you to do this manually, with perhaps some limitations, but I have not come across anything that can do it from VBA.
This may be useful:
http://www.outlookcode.com/
I have a Data Warehouse system that is controlled from a Windows Server 2008 R2 system with Outlook constantly running to process Excel report requests and also process data submitted as Excel attachments - much can be achieved using Office automation.

I'm using Excel 2010. Thank you for the above answers. The answers use terms I haven't heard before, but that's just more for me to look up and learn. At least there were a couple of yeses to get me started.

Michael, thanks for that link too. I'll be checking that out as well.