Solved

Retrieve E-mail from Exchange Server 2003 using VBA

Posted on 2004-04-06
8
419 Views
Last Modified: 2008-02-01
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?

Thanks,
Mike
0
Comment
Question by:Data-Man
[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
  • 4
  • 4
8 Comments
 
LVL 4

Assisted Solution

by:boxcar7
boxcar7 earned 250 total points
ID: 10768387
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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo121.asp


0
 
LVL 18

Author Comment

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

Mike
0
 
LVL 4

Accepted Solution

by:
boxcar7 earned 250 total points
ID: 10768577
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
    Else
        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.AddNew
        rst!Subject = objMessage.Subject
        rst!EmailType = ObjFrom.Type
        rst!EmailAddress = ObjFrom.Address
        rst!DateReceived = objMessage.TimeReceived
        rst!DateProcessed = Now()
        rst.Update
        objMessage.Delete
        Set objMessage = objMessages.GetNext
    Loop
       
    objSession.Logoff
    GetMessages = "SUCCESS::::::Messages retrieved"
    Exit Function
   
GetMessages_Error:
    GetMessages = LogError(Error$(Err), , strErrorSource, Err, strErrorInfo)
    Exit Function
End Function
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Author Comment

by:Data-Man
ID: 10768664
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.
0
 
LVL 18

Author Comment

by:Data-Man
ID: 10810789
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
0
 
LVL 18

Author Comment

by:Data-Man
ID: 10810797
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?

Thanks,
Mike
0
 
LVL 4

Expert Comment

by:boxcar7
ID: 10815742
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.
0
 
LVL 4

Expert Comment

by:boxcar7
ID: 10816207
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.  

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_olemsg_logon_method_session.asp
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

730 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