copy data from outlook attached excel file into a local one

s_samira_21 · #1 09-17-2014, 11:09 PM

Thanks

I will test and tell you what happens.

But Where should I paste this code? ThisOutlookSession or a Module?

s_samira_21 · #2 09-17-2014, 11:32 PM

I wrote this code base on my googling data, in thisOutlookSession.
this code is like the one you kindly wrote for me.
But when I receive a new mail, nothing happens

Quote:

Option Explicit
Dim WithEvents OLInboxItems As Items

Private Sub Application_Startup()

Dim OLNS As Outlook.NameSpace

Set OLNS = Application.GetNamespace("MAPI")
Set OLInboxItems = OLNS.GetDefaultFolder(olFolderInbox).Items

End Sub

Private Sub olInboxItems_ItemAdd(ByVal Item As Object)

'On Error Resume Next

Dim OLMailItem As MailItem
Dim AttPath, AttName

If TypeOf Item Is MailItem Then
Set OLMailItem = Item

'--------------------------
If OLMailItem.Attachments.Count > 0 _
And OLMailItem.Subject = "AHOrdUpdate" Then

AttName = OLMailItem.Attachments.Item(1).FileName
AttPath = "G:\Projects\Excel\TAC\TAC Data Management\Orders\AHOrders\Updates\updates\" + AttName
OLMailItem.Attachments.Item(1).SaveAsFile AttPath

End If
'---------------------

Dim xlApp, AttWB, UpdateWB, AttWS, UpdateWS As Object
Dim rng As Range
Dim AttEOC, AttEOR, EOC As Integer

'-----------------------
Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.EnableEvents = False
End With

Set AttWB = Workbooks.Open(AttPath, , False, , , , , , , True)
AttWB.Activate
Set AttWS = AttWB.sheets("sheet1")

With AttWS
Set AttEOC = Application.WorksheetFunction.CountA("A:A")
Set AttEOR = Application.WorksheetFunction.CountA("1:1")
Set rng = .Range("A1:" & AttEOR & AttEOC)
rng.Copy
.Close Save:=False
End With

'-------------------------------

'----------------------------
Set UpdateWB = Workbooks.Open("G:\Projects\Excel\TAC\TAC Data Management\Orders\AHOrders\Updates\updates.xlsx", , False, , , , , , , True)
UpdateWB.Activate
Set UpdateWS = UpdateWB.sheets("sheet1")

With UpdateWS
Set EOC = .cells(Rows.Count, "A").End(xlUp).Row
.cells(EOC, "A").Paste
.Close Save:=True

End With

'-----------------------------------------------

End If
Set Item = Nothing
Set OLMailItem = Nothing

End Sub

gmayor · #3 09-17-2014, 11:46 PM

Quote:

Originally Posted by s_samira_21

But Where should I paste this code? ThisOutlookSession or a Module?

The code goes in an ordinary module.
You can test it by selecting a message and running the following:

Code:

Sub Test()
Dim olMsg As MailItem
Set olMsg = ActiveExplorer.Selection.Item(1)
ProcessAttachment olMsg
End Sub

Note that you will have to change the paths and the filename of your target workbook, and of course add the code to transfer whatever it is that you wish to transfer from one sheet to the other.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
excel 2013 local file contact to shared path when i open it	osama6767	Excel	3	05-28-2014 06:29 AM
How to Copy data from Outlook mail and Paste it in a Excel sheet?	padhu1989	Outlook	0	09-11-2012 04:07 AM
How to copy automatically data from Excel file to Word file?	fuchsd	Word	6	10-25-2011 05:52 AM
Problem with MS Outlook when open email with attached file	moniros	Outlook	0	03-01-2009 12:50 AM
Outlook xp slow when Browsing to attached a file	jmelgar1	Outlook	0	09-23-2008 01:49 PM