Solved

How to read email from Exchange in VB

Posted on 2004-08-02
14
6,128 Views
Last Modified: 2010-08-19
I have a developer who needs to automatically process an Excel attachment that is emailed daily.  He is currently using the Outlook object model to do this.  A job runs his VB executable every few minutes to check to see if the email is available.  This executable runs on a server that also runs SQL Server and is looking for the email in a profile that is set up on the server.

Frequently, an instance of the OUTLOOK.EXE opened by the VB app does not close properly and continues running until we physically kill it.  This is a problem because the running instance of Outlook interferes with SQL Server's ability to send notifications.  I do not consider a process to check for Outlook and kill it to be a good solution for this problem.  

I did some research on this site and Google and saw several references to using MAPI instead of Outlook to do this type of function.  Is this a good solution?  If so, code samples on how to access the mail server, read the email and save the attachment would be appreciated.  I am only a minimal VB programmer and the developer is not familiar with MAPI.  If not, could I get some suggestions on how to prevent the stranded Outlook process?    
0
Comment
Question by:Lori99
14 Comments
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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
0
 
LVL 7

Author Comment

by:Lori99
Comment Utility
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
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
Oops, I forgot to remove some test code.  Disregard the code above and use this instead.

' 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, _
        objDestFolder As MAPI.Folder, _
        objMessage As MAPI.Message, _
        objMovedMessage As MAPI.Message, _
        objAttachments As MAPI.Attachments, _
        objAttachment As MAPI.Attachment
    objSession.Logon "Outlook", "MYPASSWORD", , False
    Set objFolder = objSession.GetDefaultFolder(CdoDefaultFolderInbox)
    Set objDestFolder = objFolder.Folders("Test")
    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
0
 
LVL 7

Author Comment

by:Lori99
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Lori99
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Cool!
0
 
LVL 7

Author Comment

by:Lori99
Comment Utility
The code works perfectly!  Thanks!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.  Glad I could help.
0
 

Expert Comment

by:CRCool
Comment Utility
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?
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
I don't believe that's possible.  What version of Outlook are you using?
0
 
LVL 2

Expert Comment

by:chandukb
Comment Utility
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

0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now