Solved

Retrieve E-mail from Exchange Server 2003 using VBA

Posted on 2004-04-06
8
411 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This video demonstrates basic masking and how to edit the mask to reveal the desired image.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

708 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

12 Experts available now in Live!

Get 1:1 Help Now