Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Working with Access and Outlook

    ok, I have an little app done in Acces 2K that uses the object library of Outlook 2K (yes, I know, a bit messy). It opens a specified mail folder, trolls unread messages, pulls information out, etc.

    When I run the same program on my laptop, which has Access 2000, but Outlook 2003 and Windows XP, it completely crashes the moment I press the Get Email key. I can not even step through the code - as soon as the command button is pressed, it crashes.

    I checked references and they seem to be ok.

    I have 2 options:
    1) Find out why it don't work and fix it
    2) Use the "Outlook Linked Table" offered in Access 2K

    Option 2 is pretty easy, except that it is read only and the linked table gives no indication of which messages are unread and no way to set them as read

    Any ideas?

    Thanks,
    TC

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i do similar and had some strange issues with folder navigation a couple of years ago - one user just wasn't finding 'specified folder'.
    check if this is your issue with a test version modified a bit so it looks in a 'default' folder e.g. deleted
    Code:
        '   /// get all deleted items (olFolderDeletedItems = 3)
        Set myFolder = myNamespace.GetDefaultFolder(3)
    i also late-bind all my Outlook stuff to bypass any reference issues.

    not solving your issue, but a comment anyway: i don't trust users to leave messages unread - i rename the subject of the message once it's attachment has been sucked up into Access - that way i'm in charge of what gets processed and what doesn't.

    my app has users with all permutations of WinXP/2K and Off2k/2K3.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    izy,

    I'll check it out - the default folder. It appears as though it's the dimensioning of the variables themselves to outlook objects since the sub compiles fine, but crashes access the moment it tries to run. I have used very similar modules in other environments without issue (as long as each office component was 2000 or newer).

    I must admit, I'm not savy enough to fully understant what "late binding" is other than dim'ing a variable as an object and assigning the specific type on the fly, something I actually never do. Please expound ...

    Finally, in scenerio 2 (using the linked table to outlook method) I was going to do just that: change the subject line. But the linked table is read only.

    tc

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's my test late-bind collecting attachments from olFolderDeletedItems to try to de-merde the lost folder user (in my case, it's always the first attachment in potentially N mails each with subject 'blahblah')

    Code:
        '   /// declarations to do with outlook
        Dim myOutlook As Object
        Dim myNamespace As Object
        Dim myFolder As Object
        Dim allItems As Object
        Dim myItem As Object
        Dim allAttach As Object
        Dim myAttach As Object
        
        '   /// other declarations
        Dim strSQL As String    'SQL query
        Dim strDat As String    'creation date-as-string of message from deleted items
        Dim olDat As Date       'creation date-as-date of message from deleted items
        Dim udDat As Date       'last update date of the database
        Dim amDone As Boolean   'loop control
        Dim msg As String       'generic message used all over the place
        
        '   /// constants
        Const filespec As String = "C:\TEMP\blahblah.CSV"    'the csv file
        
        On Error GoTo err_blahblah
           
        '   /// create outlook instance
        Set myOutlook = CreateObject("Outlook.Application")
        
        '   /// get the MAPI namespace
        Set myNamespace = myOutlook.GetNamespace("MAPI")
        
        '   /// get all deleted items (olFolderDeletedItems = 3)
        Set myFolder = myNamespace.GetDefaultFolder(3)
        Set allItems = myFolder.Items
        
        '   /// sort date-ascending (the 'False')
        allItems.Sort "CreationTime", False
        
        '  /// data collection loop
        '  /// go through all uncollected daily data (rename once collected)
        amDone = False
        Do Until amDone
            '   /// get the earliest unhandled blahblah report
            Set myItem = allItems.Find("[Subject] = 'blahblah'")     	'find the earliest unhandled
            If TypeName(myItem) = "Nothing" Then                            'did we find?
                amDone = True                                                   'no: prepare to exit loop
            Else
                
                '   /// grab the creation date
                strDat = myItem.CreationTime
                olDat = CDate(strDat)
                
                '   /// save the first attachment in c:\temp
                Set allAttach = myItem.Attachments
                Set myAttach = allAttach(1)
                myAttach.SaveAsFile filespec
                myItem.Subject = "DONEblahblah"
                myItem.Close 0 'olSave = 0
    ...don't forget to tidy up all those late.binding objects to Nothing

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •