Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Start macro by saving Outlook attachments into a certain fol

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Code:
    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 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dan. That looks great!

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!)

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/microsof...ok.program_vba

    HTH,
    Dan

    [ This Message was edited by: dk on 2002-04-24 06:28 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dan,

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

    Cheers
    AJ

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Ontario, Canada
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •