Paste content to Excel when .txt file (attachment) is opened

[Nravota]

Hi Guys,

I have a lot of emails with txt attachments that I need to handle every day. I select the message, open the attachement in notepad (without saving) and then manually paste it to excel workbook. I want to be able when I open the attachment that it is automatically loaded in this excel workbook. What I managed to do now is that when I save the file to my desktop I can open it from excel. But I want to do this with each opened txt file. Is there a way to do this in Outlook vba?

Sub ImportText()
Set myPlace = ActiveCell
Workbooks.OpenText _
Filename:=ActiveWorkbook.Path & "\MYFILE.txt", _
DataType:=xlDelimited, Tab:=True
ActiveSheet.UsedRange.Copy Destination:=myPlace
ActiveWorkbook.Close
End Sub
​

I want to trigger the macro each time I open txt file from Outlook. Can this be accomplished? Thank you very much in advance!

 

[Michael Bauer]

Triggering it when you open the attachment wouldn't be easy, there's no event in Outlook for that. Instead write a macro that saves the selected attachment (see ActiveExplorer.AttachmentSelection) to the hd, then use an adapted version of your Excel macro in Outlook to import the file.

To be able to use the Excel macro in Outlook, set a reference via Tools/References on "Microsoft Excel 14.0 Object Library". Here's a sample for how to open an Excel file from Outlook:
Open Excel File from within Outlook - VBOffice

Instead of ActiveCell use the Rn variable from the sample, and instead of ActiveWorkbook use Wb.