copy data from outlook attached excel file into a local one

s_samira_21 · #4 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

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