Striping (mdb) Attachments Outlook!!

[Milan]

Hi !! This is a real tough one!!!! can any help!! Basically the code below should open up oulook (2000) and look throught the inbox and find any email which has an attachment which is named "openxl.mdb". but the code does not work!!. I really could do with some help here . I need to get mdb's from outlook inbox (as mail attachments) into a temp folder, from Access 2000. I have trying this solution for days now!! but getting more and more fed up!! and i need to have it done very soon!! CAN RELY ON YOU EXPERTS !! TO FIND ME A SOLUTION!! OR POINT ME IN SOME DIRECTION OF HOPE!!! AWAIT EAGERLY!!!

THANKS

MILAN


Private Sub Command7_Click()
Dim objOL As Outlook.Application
Dim objMsg As Object
Dim objAttachments As Outlook.Attachments
Dim objSelection As Outlook.Selection
Dim i As Long
Dim lngCount As Long
Dim strFile As String
Dim strFolder As String
Dim strerFile As String
On Error Resume Next

Set objOL = CreateObject("Outlook.Application")
' Get the collection of selected objects.
Set objSelection = objOL.ActiveExplorer.Selection


strFolder = "C:\DUMP"
If strFolder = "" Then
MsgBox "Could not get Temp folder", vbOKOnly
GoTo ExitSub
End If

'objMsg.objSelection
objMsg.Class = olMail
' Get the Attachments collection of the item.
Set objAttachments = objMsg.Attachments
strFile = objAttachments.Item(i).OPENXL.mdb

strerFile = strFolder & strFile
objAttachments.Item(i).SaveAsFile strerFile
objMsg.Save
ExitSub:
Set objAttachments = Nothing
Set objMsg = Nothing
Set objSelection = Nothing
Set objOL = Nothing

End Sub

 

[Drevlin]

Before I give you the answer I wanted to point out a couple of problems with your code.

*******************
strFolder = "C:\DUMP"
If strFolder = "" Then
MsgBox "Could not get Temp folder", vbOKOnly
GoTo ExitSub
End If
*******************

This will never pop-up the message box. Your if statement is not actually looking for a file. It is just seeing if there is a string in strFolder, and since you placed a "c:\Dump" into it the line before it, this will always return false.

*******************
strFile = objAttachments.Item(i).OPENXL.mdb
*******************

Because your using "i" in the Item array I assume you want to loop through the items. In order to do this you would need a line such as:

for i = objAttachments.Items.Count to 1 step -1
Your Code HERE
Next i

I'm not too sure about the OPENXL.mdb so I'll leave that alone.

Anyway, just thought I'd give you a couple of explanations about your code before giving you this Sub. What this will do is give you a pop up that asks which Folder you want to pull the Attachments from. After you select the folder it will pull all attachments and place them into your C:\Temp directory.
I've quoted out two lines. The first quoted code is what you would want in order to pull the attachments from your inbox and not give the user the choice. The second quoted code just displays Outlook for you (which you could probably figure out on your own).

Sub saveAttachments()
Dim myOlApp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myAttachments As Outlook.Attachments
Dim myItems As Outlook.Items
Dim strFolder As String

On Error Resume Next

strFolder = "C:\Temp\"

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
'Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myFolder = myNameSpace.PickFolder
Set myExplorer = myFolder.GetExplorer
Set myItems = myFolder.Items
'myExplorer.Display

For Each obj In myItems
For Each myAttachment In obj.Attachments
myAttachment.SaveAsFile strFolder & _ myAttachment.DisplayName
Next
Next

Set myOlApp = Nothing
Set myNameSpace = Nothing
Set myFolder = Nothing
Set myExplorer = Nothing
Set myItems = Nothing

End Sub

Anyway I've just recently gotten interested in the whole outlook automation process and I'm still just a mediocre coder, so if anyone wants to Critique, feel free! =)

Hope this works for you. If you need help finessing it to your needs just post back.

 

[Tim K.]

Check the sample dbs at http://www.helenfeddema.com/CodeSamples.htm, and look at the Outlook part.

strFolder = "C:\DUMP"
If strFolder = "" Then
MsgBox "Could not get Temp folder", vbOKOnly
GoTo ExitSub
End If

Try this.

strFolder = "C:\DUMP"
If Dir(strFolder,vbDirectory) = "" Then
MsgBox "Could not get Temp folder", vbOKOnly
GoTo ExitSub
End If

 

[Milan]

Thank you very much for looking at my little problem!. I have pasted your code into myform and made reference to the outlook library. However the code seems to have a number of problems when i debug it, i think some Variables or not declared, but to be honest that's as much as i know, as i do not undertstand the outlook object library , i don't know what they should be name. So i can't get your code to work!. Any chance of posting back with problems fixed?. I really would be greatful!!!!!.


Thanks

Milan

 

[Tim K.]

Also check the KB acticle No.: Q161088 at Microsoft Web Site.

 

[Drevlin]

I suppose I should get used to working with Option Explicit.

I've added lines to declare the three variables that were missing. And the code should work without a problem.

Sub saveAttachments()
Dim myOlApp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myAttachment As Outlook.Attachment
Dim myItems As Outlook.Items
Dim strFolder As String
Dim myFolder As Object
Dim myExplorer As Outlook.Explorer
Dim obj As Outlook.MailItem


On Error Resume Next

strFolder = "C:\Temp\"

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
'Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myFolder = myNameSpace.PickFolder
Set myExplorer = myFolder.GetExplorer
Set myItems = myFolder.Items
'myExplorer.Display

For Each obj In myItems
For Each myAttachment In obj.Attachments
myAttachment.SaveAsFile strFolder _
& myAttachment.DisplayName
Next
Next

Set myOlApp = Nothing
Set myNameSpace = Nothing
Set myFolder = Nothing
Set myExplorer = Nothing
Set myItems = Nothing

End Sub

I would have liked to have declared myFolder as something a little more telling then Object but couldn't figure out what it should have been declared as... Outlook.Folders doesn't work because it's a collection and there is not an Outlook.Folder

Declaring it as an Object seems to work without a hitch.

 

[Milan]

Thanks for that!! The code Snippet works great!!.and has solved most of my problem!!. Is there anyway of specifying certain attachments to be extracted from outlook and put into a Temp Folder . At the moment the code put all of the inbox attachments in the temp folder. I am really only interested in the Mdb.s (Access Databases) only. Sorry to keep throwing these extra's in. But i am very grateful for what you have already given me!!. These forums are real "life savers!!!" Thanks Once again!.


Milan

 

[Drevlin]

You just need to create code to check the extension of the file.

The following function should do the trick:

Function isMDB(strFile As String) As Boolean
Dim lngDotPos As Long
Dim lngStrLength As Long
Dim lngRight As Long
Dim strType As String

lngDotPos = InStrRev(strFile, ".")
lngStrLength = Len(strFile)
lngRight = lngStrLength - lngDotPos
strType = Right(strFile, lngRight)
isMDB = (strType = "mdb")

End Function

Then all you need to do is create an if statement that nests your SaveAs line:

For Each obj In myItems
For Each myAttachment In obj.Attachments
blnMDB = isMDB(myAttachment.Filename)
If blnMDB Then
myAttachment.SaveAsFile strFolder & myAttachment.DisplayName
End If
Next
Next


Just remember to Declare your blnMDB:

Dim blnMDB as Boolean



That should work. I'm not going to guarantee that my isMDB function is as simplified as it could be, but it's Monday morning and I'm not quite functioning to my highest ability.


Just a note and if anybody out there has an answer please let me know. The Sub that run's Outlook creates the App but doesn't shut it down when it's finished. So there is always an Outlook running in the background after you run the Sub. I know I can close outlook outright but that shuts down the users instance of outlook (if it was already open before running the sub). So what occurs is after you shut down Outlook you have to go into Task Manager and shut it down there as well (the open instance of the hidden outlook keeps windows from shutting down).

Any ideas?

And you should know about that as a potential problem Milan.

 

[Milan]

Hey! thanks for that!. It works a treat!!!!

Milan