Extracting attached files from outlook

[Guest]

I can create an e-mail with VBA code. But is it possible to get some code
that will go to a particular shared folder in outlook and then go through
each mail and open the attached file and copy a range of data? To then move
onto another folder and do the same.
Or would it be easier to get the code to save the attached files to a folder
and then extract the data?

 

[Bob Phillips]

I did a similar thing recently, and because of the problems, I went for a 2
stage approach.

Stage 1, go through the shared folder and output attachments to a windows
folder.
Stage 2, process the windows folder file by file.

I have a workbook handy that does the first part, but it is a bit too much
code to post here. Drop me your email address if you want a copy.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Sharad]

What I did was for InBox folder, you can modify the code for
sharedfolder.
I could not find a way to open the attachment w/o saving it. I save it
in a temp folder, when done delete it and move to next.
Below is code I used, cut short so as to show method I used.

Sub LookOut()
Dim olApp As Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim myFol As Outlook.MAPIFolder, myAtt As Attachment
Dim myFile As String, myWb As Workbook

Set olApp = CreateObject("Outlook.Application")
Set objNameSpace = olApp.GetNamespace("MAPI")
Set myFol = objNameSpace.GetDefaultFolder(olFolderInbox)

For Each Item In myFol.Items
If Item.Attachments.Count > 0 Then
n = Item.Attachments.Count
For j = 1 To n
If InStr(1, Item.Attachments.Item(j).Filename, ".xls",
vbTextCompare) > 0 Then
Set myAtt = Item.Attachments.Item(j)
myFile = "C:\Temp\MyFile.xls"
myAtt.SaveAsFile myFile
Set myWb = Workbooks.Open(myFile)
With myWb
'your code to extract data
.Close (False)
End With
myWb.Close False
Kill myFile
End If
Next j
End If
Next Item
End Sub

Sharad