Retrieve E-mail from Exchange Server 2003 using VBA

Hi there,
   I have an Access application that automically sends e-mails (Work Orders) to various vendors.  There is a specific return e-mail address for support of this.  Instead of having all the various personnel setup to monitor this account, I would like to pull all the e-mails from that account in to my Access application.  I don't want to have to go through outlook, I would like to communicate directly with exchange server e-mails for a specific account.

   We are running Windows Advanced Server 2003, Terminal Services, Exchange Server 2003.

   Any thoughts?

LVL 18
Who is Participating?
boxcar7Connect With a Mentor Commented:
There are actually a lot of good samples in the reference I sent you.

Here is one of the tests I did.  Unfortunately, it is poorly documented because I was just using it to learn.  You will see calls to a few other functions.  GetParamInt and GetParamStr just pull data from tables that have the application set-up information.  LogError just is used to write error information into a table.  Basically, it checks e-mail, records the subject, sender, and other information into a table (tblReportEmailCollected), and then deletes the messages.  It also uses the DAO 3.6 reference library.

Function GetMessages()
    If GetParamInt("DebugOn") = -1 Then
        On Error GoTo 0
        On Error GoTo GetMessages_Error
        Dim strErrorSource As String, strErrorInfo As String
        strErrorInfo = "GetMessages()"
        strErrorSource = "GetMessages"
    End If
    Dim objSession As MAPI.Session ' use early binding for more efficient
    Dim objMessage As Message      '               code and type checking
    Dim objInbox As Folder
    Dim objMessages As Messages
    Dim ObjFrom As AddressEntry
    Dim dbase As DAO.Database, rst As DAO.Recordset
    Set dbase = CurrentDb()
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' create a session and log on -- username and password in profile
    Set objSession = CreateObject("MAPI.Session")
    objSession.Logon profileInfo:=GetParamStr("MAPIExchServer") & vbLf & GetParamStr("MapiUser")
    Set objInbox = objSession.Inbox
    Set objMessages = objInbox.Messages
    Set objMessage = objMessages.GetFirst
    If objMessage Is Nothing Then Exit Function
    Set rst = dbase.OpenRecordset("tblReportEmailCollected")
    Do While Not objMessage Is Nothing
        Set ObjFrom = objMessage.Sender
        rst!Subject = objMessage.Subject
        rst!EmailType = ObjFrom.Type
        rst!EmailAddress = ObjFrom.Address
        rst!DateReceived = objMessage.TimeReceived
        rst!DateProcessed = Now()
        Set objMessage = objMessages.GetNext
    GetMessages = "SUCCESS::::::Messages retrieved"
    Exit Function
    GetMessages = LogError(Error$(Err), , strErrorSource, Err, strErrorInfo)
    Exit Function
End Function
boxcar7Connect With a Mentor Commented:
Yes, add in the reference you can add to CDO 1.21 (In VBA in your Access database, go to Tools...References...)

Check the box next to "Microsoft CDO 1.21 Library".  This will open up a lot of functions to allow you to access your Exchange mailboxes.

The CDO 2.0 Library is not MAPI, so I would NOT recommend using that instead, although you could probably could do something.  You could alternatively use the Outlook Reference Library, but it must be used interactively (you can't be used in services, and you couldn't, say, set up a scheduled job to run your access database with a macro, unless the machine it was running on was logged in.)

Here is the MSDN reference for CDO 1.21

Data-ManCOOAuthor Commented:
Thanks, I'll set the reference and give it a try.  Do you have any code examples or links to code examples?

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Data-ManCOOAuthor Commented:
Thanks for the code example...I'm having problems setting a reference....sometimes I hate terminal services.....the registry permisisons go haywire....UUUUGGGGGGHHHHH.

I'll keep you posted.
Data-ManCOOAuthor Commented:
I need more time for testing the solution.  Thanks for the code example.  If I need more help, I'll post another more detailed question....thanks again....Mike
Data-ManCOOAuthor Commented:
One other comment...what if the profile doesn't exist...I would rather not create one.  Can I just specify the account and password?  What would that look like?

Yes.  In my sample code, I do exactly that:

  objSession.Logon profileInfo:=GetParamStr("MAPIExchServer") & vbLf & GetParamStr("MapiUser")

This in essense creates a temporary profile.  By using the profileinfo parameter of the Logon method (rather than specifying a profile), I am in essense bypassing the need for a profile which bypasses the need for the user to be logged onto the machine.

The form for profileInfo is basically the string for your exchange server, a line feed (vbLf), and then the string for your username.
For further clarification, I didn't need a password in my case because it ran as a scheduled service under a specific account.  I am not certain because I haven't tried this, but you might try using the profilepassword parameter of the logon method and see if that will allow you to do what you want.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.