Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use Excel VBA to access outlook mail folders

    I'm using Excel 2003 and need to use Excel to access outlook 2007 email folders so that I can retreive reports that have been emailed to us. I've currently been using MAPI to help me identify the folders that I need to check. My work has started a new policy that prevents us from using MAPI. Is there any other way for Excel to be able to get access to outlook folders with out using MAPI, CDO or redemptions. If anybody has any examples of how to view the emails in any outlook folder (not just the inbox) without using the above options. Please let me know. Thanks for the help with this!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What specifically do you mean by "using MAPI"? Do you mean automating Outlook or something else?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    Perhaps you should have outlook set up to automatically post email attachements to pre-defined file folders based on the Subject header and mail address. For example, a User could send say, a Request or say, Incident Report, to a predefined mail address??

    The pre-defined folders containing the attachments could then be accessed and processed by Excel.

    zeddy

  4. #4
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    By using MAPI I do mean automating outlook but I don't want to send anything, I only want to read emails and import data from the body of the email into Excel. The emails are auto sorted by outlook rules which place emails based on subjects and senders into predefined outlook folders. I need a way to get to those folders from Excel that doesn't rely on me declaring a MAPI namespace.

    Here is some sample code of how I had been accessing outlook folders with Excel VBA. I'm wondering if there is another way I could access the folder I listed below called eFldr without having to delcare the MAPI namespace in order to get the full folder location.
    ' Set the reference to the oulook MAPI namespace
    Set olNS = olApp.GetNamespace("MAPI")
    ' Define the generic Mailbox name
    MBG = "Mailbox - generic"
    Set eFldr = olNS.Folders(MBG).Folders("Personal Archive Folders").Folders("EXCEPTION REPORTS")

    How can I define the folder location listed above so that I can still get to it without having to ues this section:
    Set olNS = olApp.GetNamespace("MAPI") and then having to include it as olNS.Folders(MBG).Folders....Etc.




    Full sample code listed below

    Option Explicit
    Sub Ltr_Segment_Imports()
    Dim olApp As Object, olNS As Object, olMail As Object, eFldr As Object
    Dim App As String, Subj As String, MBG As String
    Dim NewRpt As Boolean
    Dim PDate As Date
    On Error GoTo eHandler ' Set error handling
    ' Set the reference to outlook or create one
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
    Set olApp = CreateObject("Outlook.Application")
    End If
    ' Set the reference to the oulook MAPI namespace
    Set olNS = olApp.GetNamespace("MAPI")
    ' Define the generic Mailbox name
    MBG = "Mailbox - generic"
    Set eFldr = olNS.Folders(MBG).Folders("Personal Archive Folders").Folders("EXCEPTION REPORTS")
    ' Cycle through the emails in the generic mailbox, Exception folder
    For Each olMail In eFldr.Items
    ' Determine what the next exception report is
    NewRpt = False
    If InStr(1, olMail.Subject, "Report A", vbTextCompare) > 0 Then
    App = "A"
    NewRpt = True
    ElseIf InStr(1, olMail.Subject, "Report B", vbTextCompare) > 0 Then
    App = "B"
    NewRpt = True
    End If

    If NewRpt = True Then
    PDate = Format(olMail.ReceivedTime, "mm/dd/yy")
    Subj = olMail.Subject
    End If

    ' From this point additional scripting is used to extract data from the email.
    ' Not worth including for this example

    NextMsg:
    Next olMail



    eHandler:
    Set olApp = Nothing
    Set olNS = Nothing
    Set olMail = Nothing
    Set eFldr = Nothing

    End Sub

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I think you may be out of luck as it seems your company has ruled out all message interfaces. I confess I cannot begin to fathom the sort of IT department that would come up with that. I thought ours were bad...
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, that was my thought. The IT department here is concerned with sending emails without authetication, which was their reasoning for wanting to remove MAPI capabilities from the client pc's.

    I was really hoping somebody out there had an alternate way of getting access to the outlook folders. I'm not that familiar with using the explorers or inspectors and wasn't sure if there were ways Excel could use those to get to the folders.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Considering the Outlook security guard, that seems a fairly pointless fear unless the user is allowed to use Redemption or ClickYes.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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