?
Solved

How to read email from Exchange in VB

Posted on 2004-08-02
14
Medium Priority
?
6,161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 11697704
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
ID: 11697854
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
ID: 11698362
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 76

Expert Comment

by:David Lee
ID: 11700428
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 2000 total points
ID: 11700438
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
ID: 11703696
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
ID: 11703716
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
 
LVL 76

Expert Comment

by:David Lee
ID: 11706401
Cool!
0
 
LVL 7

Author Comment

by:Lori99
ID: 11720737
The code works perfectly!  Thanks!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 11721326
You're welcome.  Glad I could help.
0
 

Expert Comment

by:CRCool
ID: 22618595
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
ID: 22619785
I don't believe that's possible.  What version of Outlook are you using?
0
 
LVL 2

Expert Comment

by:chandukb
ID: 24032535
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
ID: 24033515
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 12 hours left to enroll

765 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