?
Solved

Retrieve E-mail from Exchange Server 2003 using VBA

Posted on 2004-04-06
8
Medium Priority
?
421 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 1000 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 1000 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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: 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.

Question has a verified solution.

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

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
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!
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

771 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