Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using excel to read outlook mail (2k)

    Ok, i did a search on "outlook" in the excel forum and searched all the way back to 2001. In alllllllllllll the posts they seem to deal with SENDING mail in outlook. what i want to do is READ mail from outlook. Basically what i have is a bunch of mails in my inbox, and if they are from a specific sender, I want to parse that message looking for a specific word, such as "exists". After following a very basic tutorial dealing with calling an application, and putting together a small compilation of codes i found from searches and my own newbie excel VBA, this i what i have so far (Its pretty ugly!):

    Sub Verify()
    Dim outlook As Object <font color=448800>'create outlook variable (for easy calling)</font color=448800>
    Dim strfrom As String <font color=448800>'whos it from?</font color=448800>
    Dim strbody As String <font color=448800>'body of email</font color=448800>
    Dim strexists As String <font color=448800>'what we wanna find</font color=448800>

    Set outlook = CreateObject("Outlook.application") <font color=448800>'create outlook object</font color=448800>
    outlook.Visible = True <font color=448800>'make sure outlook is visible</font color=448800>

    <font color=448800>'for statement cycling through inbox starts here...
    'for something = 1 to end of inbox</font color=448800>
    strfrom = objMessage.Reply.Recipients(1).Address <font color=448800>'find sender (thanks jscher!! - starpost)</font color=448800>
    If strfrom = "something@something.com" Then
    strexists = InStr(1, strbody, "exists")
    If strexists <> "" Then
    Excel.Range(C2) = strexists <font color=448800>'copy strexists into appropriate cell</font color=448800>
    End If
    End If
    <font color=448800>'strexists = ""
    'next for</font color=448800>

    outlook.Quit <font color=448800>'close outlook</font color=448800>
    outlook = Nothing <font color=448800>'free resources</font color=448800>

    End Sub


    any ideas??
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Using excel to read outlook mail (2k)

    In the first place, don't name your object variable outlook, since that is also the name of the library. It is bound to lead to confusion.
    In the second place, you must always use Set to assign an object variable (you omitted Set in the next to last line)
    In the third place, you must always use error handling with Automation, to avoid copies of the application remaining in memory if an error occurs.

    Here is code that loops through the Inbox.

    Sub Verify()
    Dim objOL As Object 'create outlook variable (for easy calling)
    Dim strFrom As String 'whos it from?
    Dim strBody As String 'body of email
    Dim strExists As String 'what we wanna find
    Dim objNameSpace As Object
    Dim objFolder As Object
    Dim objMessage As Object
    Dim lngRow As Long

    On Error GoTo ErrHandler

    Set objOL = CreateObject("Outlook.Application") 'create outlook object
    'objOL.Visible = True 'make sure outlook is visible
    Set objNameSpace = objOL.GetNamespace("MAPI") ' required name
    Set objFolder = objNameSpace.GetDefaultFolder(olFolderInbox)

    lngRow = 2
    'for statement cycling through inbox starts here...
    For Each objMessage In objFolder.Items
    strFrom = objMessage.Reply.Recipients(1).Address 'find sender
    If strFrom = "something@something.com" Then
    strBody = objMessage.Body
    strExists = InStr(1, strBody, "exists")
    If Not strExists = "" Then
    'copy strexists into appropriate cell
    ActiveSheet.Range("C" & lngRow) = strExists
    lngRow = lngRow + 1
    End If
    End If
    Next objMessage

    ExitHandler:
    On Error Resume Next
    Set objMessage = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    objOL.Quit 'close outlook
    Set objOL = Nothing 'free resources
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    wow, i am pretty bad at VBA. i didn't know you had to use Set to set an object to nothing, i thought you could just assign the object no value by setting it equal to nothing. also didn't know the outlook reference libary was called just outlook... how do you find out more about the things available in a library? - like properties and thingsl ike that i guess (.body, .sender, etc.). and for the error handling, i should have known better, how else could i even find my errors?!

    thanks for the help hans. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Using excel to read outlook mail (2k)

    The method used in the code is called late binding. All variables associated with Outlook are declared As Object. The advantage of this method is that it's independent of the version of Outlook the user has, but the disadvantage is that you're programming in the blind, as it were.
    The other method is early binding. You tell VBA in advance which object library you are going to use. The advantage is that you can press F1 to get help, and that you get IntelliSense (if you type objOL followed by a period, a list of available properties and methods pops up). The disadvantage is that the code will not work if the user has an older version of Outlook (a later version will probably be OK).

    To use early binding:
    - Select Tools | References..., and tick Microsoft Outlook 9.0 Object Library, then click OK.
    - Change the declarations:

    Dim objOL As Outlook.Application
    Dim objNameSpace As Outlook.NameSpace
    Dim objFolder As Outlook.MapiFolder
    Dim objMessage As Outlook.MailItem

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    can i use late binding if the library is selected too? or will that cause problems?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    You can. I do that regularly: set a reference to the library I need, do al the programming (with intellisense and help) and then before release remove the reference. and change the declarations from "Dim oOLApp as Outlook.Application" to "Dim oOLApp as Object"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using excel to read outlook mail (2k)

    In addition to Jan's suggestions:
    You must also remember to change any of the names of "built-in constants" to their actual values.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    thank you
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    it didn't work, see attached error message.

    all it does is it closes outlook after not working, and I use the exact code Hans posted to try it out, just changing the e-mail address to one that exists.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Using excel to read outlook mail (2k)

    To find out what exactly is happening, temporarily comment out the line On Error GoTo ErrHandler by inserting an apostrophe in front of it:

    'On Error GoTo ErrHandler

    When you run the code now, you'll get a choice between End, Debug and Help. Click Debug. You'll be taken to the Visual Basic Editor with the offending line highlighted in yellow. Which line is that?
    (You can end the macro now by clicking the Stop button on the toolbar)

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    It highlights:

    Set objFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Using excel to read outlook mail (2k)

    As stated higher up in this thread, if you use late binding, you must replace symbolic constants by their values. In this case, olFolderInbox. If you look it up in the Object Browser in Outlook VBA, you'll find that its value is 6, so try

    Set objFolder = objNameSpace.GetDefaultFolder(6)

  13. #13
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    ok... maybe i'll just use early binding then since i'll be the only one using it. then it errors again at this line:

    strFrom = objMessage.Reply.Recipients(1).Address 'find sender

    is it beacuse of the same reason?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Using excel to read outlook mail (2k)

    No, this should be OK. What it the error message?

  15. #15
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using excel to read outlook mail (2k)

    438: object doesn't support this property or method
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Page 1 of 2 12 LastLast

Posting Permissions

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