Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code to cycle through Outlook inbox (Access 97)

    Hi,
    We have an email account for a special study. I have to write a program, that will cycle through the outlook inbox looking for emails with attachments and store the attachments in a directory on network. Also sort and store the emails with and without attachment in seprate folders under Inbox folder. Can somebody help me with the code or give me some good hyperlinks....

    Thanks,
    Anshu

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code to cycle through Outlook inbox (Access 97)

    You can use Automation for this, i.e. controlling one application from another in VBA. In this case, you will use Access to control Outlook. A good starting point is the Automation help file for Office 97. You can download it from OFF97: Microsoft Office 97 Automation Help File Available. This help file contains many code examples.

    I recommend studying this help file; then come back here. I or other Loungers can help, and provide code examples, but it will be easier if you have familiarized yourself with the basic ideas.

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to cycle through Outlook inbox (Access 97)

    Thanks Hans.
    The help file is very informative and I have been able to write the following code to save attachments:

    Public Function SaveAttachments(Optional PathName As String) As Boolean
    Dim oOutlook As Outlook.Application
    Dim oNs As Outlook.NameSpace
    Dim oFldr As Outlook.MAPIFolder
    Dim oMessage As Object
    Dim sPathName As String
    Dim oAttachment As Outlook.Attachment
    Dim iCtr As Integer
    Dim iAttachCnt As Integer

    Set oOutlook = New Outlook.Application
    Set oNs = oOutlook.GetNamespace("MAPI")
    Set oFldr = oNs.GetDefaultFolder(olFolderInbox)
    For Each oMessage In oFldr.Items
    With oMessage.Attachments
    iAttachCnt = .Count
    If iAttachCnt > 0 Then
    For iCtr = 1 To iAttachCnt
    .Item(iCtr).SaveAsFile sPathName _
    & .Item(iCtr).FileName
    Next iCtr
    End If
    End With
    DoEvents

    Next oMessage
    SaveAttachments = True

    End Function

    And it is working perfect. Could you please give some sample code to sort and store the emails with and without attachemnts under folders "WithoutAttach" and "Attach" under Inbox.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code to cycle through Outlook inbox (Access 97)

    Excellent, you got the basics right the first time. Here is the code with some enhancements:
    <UL><LI>The code tests whether OIutlook is already running; it only starts a new instance if necessary.
    <LI>All object variables are released at the end.
    <LI>I added error handling to make sure that object variables will be released even if an error occurs.
    <LI>Because you want to move e-mails to other folders, I changed the loop throught the Inbox to go backwards; you can get unexpected results if you move or delete items while moving forwards - it's as if the rug is being pulled from under you.
    <LI>If you would like to remove the attachments after saving them, you would have to loop backwards through them too.
    <LI>I assume that sPathName was a leftover from an earlier version, or that you left a bit of code out of the post.[/list]Public Function SaveAttachments(Optional PathName As String) As Boolean
    Dim oOutlook As Outlook.Application
    Dim oNs As Outlook.NameSpace
    Dim oFldr As Outlook.MAPIFolder
    Dim oFldrAttach As Outlook.MAPIFolder
    Dim oFldrWithout As Outlook.MAPIFolder
    Dim oMessage As Outlook.MailItem
    Dim oAttachment As Outlook.Attachment
    Dim iMailCtr As Integer
    Dim iCtr As Integer
    Dim iAttachCnt As Integer
    Dim fWeStartOutlook As Boolean

    On Error Resume Next
    ' Try to get reference to running instance
    Set oOutlook = GetObject(, "Outlook.Application")
    If oOutlook = Nothing Then
    ' Try to start Outlook
    Set oOutlook = CreateObject("Outlook.Application")
    If oOutlook = Nothing Then
    GoTo ErrHandler
    End If
    fWeStartOutlook = True
    End If

    Set oOutlook = New Outlook.Application

    On Error GoTo ErrHandler
    Set oNs = oOutlook.GetNamespace("MAPI")
    Set oFldr = oNs.GetDefaultFolder(olFolderInbox)
    Set oFldrAttach = oFldr.Folders("Attach")
    Set oFldrWithout = oFldr.Folders("WithoutAttach")

    ' Loop backwards through e-mails
    For iMailCtr = oFldr.Items.Count To 1 Step -1
    Set oMessage = oFldr.Items.Item(iMailCtr)
    With oMessage.Attachments
    iAttachCnt = .Count
    If iAttachCnt > 0 Then
    For iCtr = 1 To iAttachCnt
    .Item(iCtr).SaveAsFile PathName & _
    .Item(iCtr).FileName
    Next iCtr
    oMessage.Move oFldrAttach
    Else
    oMessage.Move oFldrWithout
    End If
    End With
    Next iMailCtr
    SaveAttachments = True

    ExitHandler:
    ' Clean up (release object memory)
    On Error Resume Next
    Set oMessage = Nothing
    Set oFldrWithout = Nothing
    Set oFldrAttach = Nothing
    Set oFldr = Nothing
    Set oNs = Nothing
    ' Quit Outlook only if we started it
    If fWeStartOutlook = True Then
    oOutlook.Quit
    End If
    Set oOutlook = Nothing
    Exit Function

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    SaveAttachments = False
    ' Always clean up
    Resume ExitHandler
    End Function

  5. #5
    New Lounger
    Join Date
    Jul 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to cycle through Outlook inbox (Access 97)

    Thanks Hans. It worked great.

Posting Permissions

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