Solved

Retrieve E-mail from Exchange Server 2003 using VBA

Posted on 2004-04-06
8
416 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

823 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