How to read email from Exchange in VB

There are two versions of MAPI, Simple and Extended.  Outlook itself is built on Extended MAPI.  As to whether or not switching from using the Outlook object model to MAPI would, in my opinion, depend on what it is that's causing the current app which uses Outlook to hang.  Do you have any idea what the problem is?  If not, then I don't know that a switch to using MAPI, actually Simple MAPI unless you want to use Redemption, a third-party library that uses Extended MAPI, will sovle the problem.  Any chance you could post the code you're currently using so we could have a look and see if we see anything wrong?  I'd hate to recommend a switch to MAPI only to find that it hasn't solved anything.

As to sample code using MAPI, here's a very simple bit of code that reads the messages in the inbox and puts the subjects into a listbox.

   'Include a reference to Microsoft CDO 1.21 Library

    Dim objSession As New MAPI.Session, _
        objFolder As MAPI.Folder, _
        objMessage As MAPI.Message
    objSession.Logon "Outlook"
    Set objFolder = objSession.GetDefaultFolder(CdoDefaultFolderInbox)
    For Each objMessage In objFolder.Messages
        List1.AddItem objMessage.Subject
    Next
    objSession.Logoff

I don't know why Outlook is hanging.  This process runs multiple times (like every 5 minutes for several hours) and I suspect it is erroring out at some point and not cleaning up the Outlook session.  The developer said he has tried quite a few things, but wasn't able to solve the problem.

This is the portion of the code that has to do with reading the email from Outlook.  There is code within this sub that also opens the spreadsheet and writes the contents to a SQL database that I cut out.    

sub main()

On Error GoTo FailOut

    Dim OutlookApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myDestFolder As Object
    Dim Email As Object
    Dim objAttachments As Object
    Dim objAttach As Object

    Set OutlookApp = CreateObject("Outlook.Application")
    Set myNameSpace = OutlookApp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myDestFolder = myFolder.Folders("Test")

    If myFolder.Items.Count = 0 Then GoTo FailOut

    For Each Email In myFolder.Items
        If Email.Class = olMail And InStr(1, Email.Subject, "Test") > 0 Then
            If Email.Attachments.Count > 0 Then
                Set objAttachments = Email.Attachments
                For Each objAttach In objAttachments
                    If Dir("\\server\directory\") = "" Then GoTo FailOut
                    objAttach.SaveAsFile "\\server\directory\myfile.xls"
                Next
                Set objAttachments = Nothing
                Email.Move myDestFolder

            <some code here processes the Excel spreadsheet>

            End If
        End If
    Next Email
    OutlookApp.Quit
   

FailOut:
   
    On Error Resume Next
    Set myFolder = Nothing
    Set myNameSpace = Nothing
    Set OutlookApp = Nothing
    Set myDestFolder = Nothing

Well, just glancing over the code you supplied very quickly the only thing I see that I'd do differently is to issue an OutlookApp.Logoff command rather than OutlookApp.Quit.  I don't think I've ever used Quit to get out of an Outlook application, but I don't know that's more than just personal preference.  In other words, I don't know that's what causing the problem.  Let me see if I can duplicate the problem and I'll get back to you.  Two quick questions.  First, is this a true VB application or is it a VBscript routine?  Second, are you using Windows built-in scheduler to run this at specific intervals?

I ran some tests using the code you supplied and kept running into problems.  Sometimes I'd get an error when the program was trying to terminate the Outlook session, other times when reading through the messages.  Rather than spend time right now trying to figure out why, I elected to try the MAPI approach and see if that'd make any difference.  It seems to have.  Using the code below I ran through about 15 program executions in a row, running at one minute intervals, without a problem.  Of course I can't tell if this'll solve the problem you've run into, but it's worth a try.  I think the code below pretty well duplicates the functionality of your code.  If I missed anything important, then let me know and I'll fix it.  The code below was written using VB, not VBScript.  If you need VBScript, then I'm sure we can make the necessary mods.

' Include a reference in the project to Microsoft CDO 1.21 Library
Public Sub MAPIVersion()
    On Error GoTo MAPIVersionError
    Dim objSession As New MAPI.Session, _
        objFolder As MAPI.Folder, _
        objSubFolder As MAPI.Folder, _
        objDestFolder As MAPI.Folder, _
        objMessage As MAPI.Message, _
        objMovedMessage As MAPI.Message, _
        objAttachments As MAPI.Attachments, _
        objAttachment As MAPI.Attachment
    objSession.Logon "Outlook", "MYPASSWORD", , True
    Set objFolder = objSession.GetDefaultFolder(CdoDefaultFolderInbox)
    Set objDestFolder = objFolder.Folders("Test")
    For Each objSubFolder In objFolder.Folders
        Debug.Print objSubFolder.Name & " " & objSubFolder.ID
    Next
    For Each objMessage In objFolder.Messages
        Debug.Print objMessage.Subject
        If objMessage.Class = CdoMsg And UCase(objMessage.Subject) = "TEST" Then
            Set objAttachments = objMessage.Attachments
            If objAttachments.Count > 0 Then
                For Each objAttachment In objAttachments
                    objAttachment.WriteToFile App.Path & "\" & objAttachment.Name
                Next
                Set objAttachment = Nothing
                Set objAttachments = Nothing
                Set objMovedMessage = objMessage.MoveTo(objDestFolder.ID)
            End If
        End If
    Next
    objSession.Logoff
    GoTo MAPIVersionCleanup
MAPIVersionError:
    MsgBox "Houston, we have a problem"
MAPIVersionCleanup:
    Set objMessage = Nothing
    Set objFolder = Nothing
    Set objSession = Nothing
End Sub

BlueDevilFan,

This is great!  I really appreciate you going to so much trouble to help.  I'm going to pass this code to my developer and have him try it.  I'll get back to you on how it works and to award points hopefully sometime today.

Just as an FYI, this is a true VB 6.0 executable, not VBScript so your code should be fine.  It is run from the SQL Server Agent Job Scheduler.

Cool!

The code works perfectly!  Thanks!

You're welcome.  Glad I could help.

BlueDevilfan:
how would i use this code if i wanted to login as an account that is different then what i am already logged into the computer as?

I don't believe that's possible.  What version of Outlook are you using?

Hi BlueDevilFan,

Can u help me with this -
I get an error on this line -
Set objDestFolder = objFolder.Folders("Test")

and
objFolder  has no value when checked in debug.  I changed "Test" to "Misc" (actual folder), but still I get  MAPI_E_NOT_FOUND(8004010F) ERROR

AND
Is it possible to login to a specific mail box.  For instance, I want to login to a message box on the exchange server, can I do that?

Thank YOu for your help.
CM

Hi, CM.

If objFolder has no value (i.e. its value is Nothing), then one of these two lines is failing

    objSession.Logon "Outlook", "MYPASSWORD", , False
    Set objFolder = objSession.GetDefaultFolder(CdoDefaultFolderInbox)

My recommendation is to step through the code in the debugger and see where the failure is occurring.  Did you add the reference to the CDO library?  If not, then the constant CdoDefaultFolderInbox may not have the correct value.

Yes, you can log into any mailbox on the server so long as you have a profile for that mailbox.