Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-17-2014, 12:45 PM
s_samira_21 s_samira_21 is offline copy data from outlook attached excel file into a local one Windows XP copy data from outlook attached excel file into a local one Office 2007
Novice
copy data from outlook attached excel file into a local one
 
Join Date: Aug 2014
Posts: 7
s_samira_21 is on a distinguished road
Default copy data from outlook attached excel file into a local one


Hi

I get some emails each day with an Excel workbook attached that contains data on its first worksheet. I copy and paste data into my local workbook. Is there a way to have VBA in outlook that after receiving email, open the attachment, copy the data and past it into the local workbook and close the attachment?

thanks

regards
Reply With Quote
  #2  
Old 09-17-2014, 10:58 PM
gmayor's Avatar
gmayor gmayor is offline copy data from outlook attached excel file into a local one Windows 7 64bit copy data from outlook attached excel file into a local one Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

That would be fairly straightforward. You would need to set an Outlook rule to identify the incoming messages and run a script from that rule. The script would save then open the attachment in Excel, then open the target workbook and do whatever processing you require before closing the attachment and optionally deleting it from the filing system. Something like the following:

Code:
Sub ProcessAttachment(Item As Outlook.MailItem)
Dim olAtt As Attachment
Dim strFilename As String
Dim xlApp As Object
Dim xlWB As Object
Dim xlTarget As Object
Const strTarget As String = "C:\Path\TargetBookName.xlsx"
    If Item.Attachments.Count > 0 Then
        For Each olAtt In Item.Attachments
            If Right(olAtt.Filename, 4) = "xlsx" Then
                strFilename = "C:\Path\" & olAtt.Filename
                olAtt.SaveAsFile strFilename
                Set xlApp = GetObject(, "Excel.Application")
                If Err <> 0 Then
                    Set xlApp = CreateObject("Excel.Application")
                End If
                On Error GoTo 0
                Set xlWB = xlApp.workbooks.Open(strFilename)
                Set xlTarget = xlApp.workbooks.Open(strTarget)
                '+++++++++++++++++++++++++++++
                'Do stuff with the 2 workbooks
                '+++++++++++++++++++++++++++++
                xlWB.Close 0        'Close attachment without saving
                'Kill strFilename 'Optional delete the saved attachment
                Set xlWB = Nothing
                Set xlTarget = Nothing
                Set xlApp = Nothing
                Exit For
            End If
        Next olAtt
    End If
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 09-17-2014, 11:09 PM
s_samira_21 s_samira_21 is offline copy data from outlook attached excel file into a local one Windows XP copy data from outlook attached excel file into a local one Office 2007
Novice
copy data from outlook attached excel file into a local one
 
Join Date: Aug 2014
Posts: 7
s_samira_21 is on a distinguished road
Default Where sould I Paste this Code?

Thanks

I will test and tell you what happens.

But Where should I paste this code? ThisOutlookSession or a Module?
Reply With Quote
  #4  
Old 09-17-2014, 11:32 PM
s_samira_21 s_samira_21 is offline copy data from outlook attached excel file into a local one Windows XP copy data from outlook attached excel file into a local one Office 2007
Novice
copy data from outlook attached excel file into a local one
 
Join Date: Aug 2014
Posts: 7
s_samira_21 is on a distinguished road
Default my Job That doesnt work when a new mail receives

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
Reply With Quote
  #5  
Old 09-17-2014, 11:46 PM
gmayor's Avatar
gmayor gmayor is offline copy data from outlook attached excel file into a local one Windows 7 64bit copy data from outlook attached excel file into a local one Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Quote:
Originally Posted by s_samira_21 View Post
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy data from outlook attached excel file into a local one 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
copy data from outlook attached excel file into a local one 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:14 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft