Start macro by saving Outlook attachments into a certain fol

[AJ]

Hi everyone,

I have a macro that processes a bunch of files received every day on email that I have saved into a certain folder.
What would be very cool though is if when the macro starts the first thing it does is go to Outlook, go to an email folder where all these messages are received and save the attachments for me.
This would save hours of arsing about!
Is this something Excel VBA can do?
Or should I be looking at some Rules, VBA, whatever else in Outlook instead?

Any help, very gratefully received as always.
I don't really know where to begin on this one!

Thanks
AJ

P.S. Office 2000 on Win2K (incase that has any bearing on the answer)

 

[dk]

Hi everyone,

I have a macro that processes a bunch of files received every day on email that I have saved into a certain folder.
What would be very cool though is if when the macro starts the first thing it does is go to Outlook, go to an email folder where all these messages are received and save the attachments for me.
This would save hours of arsing about!
Is this something Excel VBA can do?
Or should I be looking at some Rules, VBA, whatever else in Outlook instead?

Any help, very gratefully received as always.
I don't really know where to begin on this one!

Thanks
AJ

Hi Aj,

Here's some code which should do the trick - it'll need modifying to suit your requirements e.g. folder name, path where you want the files. Let me know if it works for you or if you have any questions. You need to set a reference to the Outlook object library (Tools, References in the VBE) for this to work.

Regards,
Dan

Sub SaveAttachments()
Dim olApp As Outlook.Application, olNameSpace As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder, olMail As Outlook.MailItem
Dim olAttachment As Outlook.Attachment, lngAttachmentCounter As Long

On Error GoTo Oooops

'Create an instance of Outlook and allow the user to choose
'which folder they want to process
Set olApp = New Outlook.Application
Set olNameSpace = olApp.GetNamespace("MAPI")
Set olFolder = olNameSpace.PickFolder

If olFolder Is Nothing Then Exit Sub    'User cancelled

For Each olMail In olFolder.Items
    If olMail.UnRead = True Then    'If you want ALL messages processed then take this line out and the End If
        For Each olAttachment In olMail.Attachments
            lngAttachmentCounter = lngAttachmentCounter + 1
            olAttachment.SaveAsFile "C:tempsaved attachments" & olAttachment.DisplayName
        Next olAttachment
    End If
Next olMail

AppActivate "Microsoft Excel"

MsgBox lngAttachmentCounter & " attachments saved.", vbInformation, "Success!"

Exit Sub

Oooops:

MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

This message was edited by dk on 2002-04-23 05:38
This message was edited by dk on 2002-04-23 05:41

 

[AJ]

Thanks Dan. That looks great!

 

[AJ]

Hi Dan,

Quick related query. I thought it would be easy but I can't work it out and it's bugging me now! - How do I set the folder I want rather than using the PickFolder dialog box as the folder will always have the same name.
(It's a folder called Dump in the Public Folders)

Thanks again!
AJ

 

[dk]

AJ,

I can't remember off the top of my head. I've got an urgent appointment at the Hog in the Pound so I'll have a look tomorrow

Regards,
Dan

 

[AJ]

No worries!
I'll look forward to a reply tomorrow (assuming your head doesn't take too much of a battering in the pub!)

I afraid I have yet another query though...

I've been playing with what you sent and it seems to run into problems if the attachment is in fact another email message. Is there any reason why it would fall over in this instance?
And in actual fact, the most appropriate course of action if the attachment is another message, would be to then check to see if the attached message itself has an attachment as well and save that attachment instead! Basically the idea being to keep on drilling down through the embedded messages until it actually finds something that is not in msg format and save it! Or am I now in the realms of fantasy!

Rgds
AJ

(Having a bit of a BOFH day! - sorry folks!)

 

[dk]

No worries!
I'll look forward to a reply tomorrow (assuming your head doesn't take too much of a battering in the pub!)

I afraid I have yet another query though...

I've been playing with what you sent and it seems to run into problems if the attachment is in fact another email message. Is there any reason why it would fall over in this instance?
And in actual fact, the most appropriate course of action if the attachment is another message, would be to then check to see if the attached message itself has an attachment as well and save that attachment instead! Basically the idea being to keep on drilling down through the embedded messages until it actually finds something that is not in msg format and save it! Or am I now in the realms of fantasy!

AJ,

The first part of your question can be solved by doing this:-

Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox).Folders("Dump")


As for the second bit I'm not sure. The first problem is that as you want to 'drill' through you will need to use a recursive procedure (that is one which calls itself). This can be tricky. The second problem is identifying that the attachment is a message rather than a file. If you examine the Class property of an attachment it will be the same regardless of whether it's a file or a message. I could probably have a look when I've got a bit more time but if you need an answer sooner it might be worth posting the code onto the Outlook newsgroup.

news://msnews.microsoft.com/microsoft.public.outlook.program_vba

HTH,
Dan
This message was edited by dk on 2002-04-24 06:28

 

[AJ]

Hi Dan,

Many many thanks for all your help. I reckon I now have enough to go on. You're a star!

Cheers
AJ

 

[Ziggy]

This looks like something I could use but I'm not sure which part of the code I need to change to integrate it into my PC. I get User-type not defined compile error>
olApp As Outlook.Application

I get the same(s) file from the same people and wouldn't mind having excel get them from Outlook and saving them in a particalar format(the last part I can do).

Any one else doing something similar?

Thanks

Ziggy

 

[Ivan F Moala]

On 2002-04-25 20:22, Ziggy wrote:
This looks like something I could use but I'm not sure which part of the code I need to change to integrate it into my PC. I get User-type not defined compile error>
olApp As Outlook.Application

I get the same(s) file from the same people and wouldn't mind having excel get them from Outlook and saving them in a particalar format(the last part I can do).

Any one else doing something similar?

Thanks

Ziggy

you need to reference the;
Microsoft Outlook 9.0 Object Library = msoutl9.olb or what ever version you have.

In VBA Ed
Tools > References