Link to home
Start Free TrialLog in
Avatar of Joar
Joar

asked on

Reading Outlook emails into an MS Access 2007 application

I am developing a MS Access 2007 application where it shall be possible to access Outlook emails and select one email and one or more of its attachments in order to save theese into an archive file system, one file for the email and one for each attachment.
I have testet different codes to read the outlook emails and attachments, but I feel that I still miss the complete solution for what I want to solve.

1) If I attach the Outlook folder as a table, I may read the emails into a datasheet as I would with an ordinary table.
But how do I then read the attachments just for the one email that I do select in the datasheet?

2) IF I read the emails using VBA like beneath (not all code are there), how should I display those in the datasheet?
Should I put them into a temp table that I use as recordsource, or ?
And what about the attachments then.

3) If I save the email as a .msg file, it will open as an Outlook message.
May be this is OK, but will it be readable for later versions of Outlook and what if Outlook is now longer a tool to be used?

But if I do save the message to a file directly from Outlook, I get a different file format, that is .txt or .HTML.
I cannot find how to save it this way from the Access VBA code. Could I?

Hope anybody may give me some help.

Dim olApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim SubFolder As MAPIFolder
Dim mItem As MailItem

Dim InboxItems As Outlook.Items
Dim mObj As Object

For Each mItem In InboxItems
.....

        If mItem.Attachments.Count > 0 Then
   
            For Each mAtt In mItem.Attachments


 Next
Dim mAtt As Outlook.Attachment



Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

You might be asking too many questions here to identify a solution.  The problem seems to be you know too much ;-)

Mails can be saved in many ways and we cannot suggest if .msg format will exist forever but perhaps saving mails as .html will be robust enough?

As for attachments they can also be saved so in theory one possible answer is to save mails as .html and attachments as they are to a common folder and then reference them from your database.

If something like this is the requirement then we can certainly help.

Chris
Avatar of Joar
Joar

ASKER

OK, Let me concentrate on question #1:
1) If I attach the Outlook folder as a table, I may read the emails into a datasheet as I would with an ordinary table.
But how do I then read the attachments just for the one email that I do select in the datasheet?

The recordsource for the datasheet could be like this:
select * from Inbox order by RecievedTime desc;

Then I am not able to catch the attachments for the email selected in the dataseet (on Form_Current)????



Personally i'm not well versed in access so have never heard of opening an outlook folder as a table.  If you present the code then I wil try and see if I can figure out how to access attachments using the mechanism ... assuming it is possible of course.

Chris
We've built many systems integrating with Outlook and Exchange. Whether it's logging messages, tagging them, etc., it can get quite complicated.

The best way we've found to do this is to create an application that works with the Exchange server that provides events for the message traffic. You can then record, tag, or route the messages accordingly and do it very quickly. We usually work with .NET and a SQL Server database to do this. Access can then manipulate the contents.

I know it's not an Access solution but may give you some insight into alternative approaches.
Avatar of Joar

ASKER

The bad thing is that there is a Outlook View Control that does not seem to work any more in Access 2007.
I have therefor written some code that I hope will give me a solution (see attached code), but I feel this is not optimal, and it is not complete so far.

I read parts of the Outlook inbox into 2 temporary tables, one for the emails and one for the attachment file names.
The user may also read other parts of the Outlook inbox by specifying parameters for the received date, sendername and subject.
I have not saved the attachenst into the temp table, just the file names of the attactments (I am afraid of space usage).

The result is displayed in a window where the user may scroll trough the emails read in and select one email and one or more of the attachments for archiving.
The problem is when the user select an attachment for archiving I will have to scoll through the Outlook Inbox again to find that attachment.
And there does not seem to be an email ID in Outlook inbox. So I will have to searche the innbox testing for Received date, Sender name, Subject and file name....
Not a presize identification...
Sub HentInnboks()

Dim olApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim SubFolder As MAPIFolder
Dim mItem As MailItem

Dim InboxItems As Outlook.Items
Dim mAtt As Outlook.Attachment


Dim strSubject As String


On Error GoTo HentInnboks_Error

    DoCmd.Hourglass True
    
Set olApp = CreateObject("Outlook.Application")
Set Inbox = olApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)

Set InboxItems = Inbox.Items

InboxItems.Sort "ReceivedTime", True                    ' TRue = Synkende!!!

Dim FomMottatt As Date, TomMottatt As Date


FomMottatt = pMottattFom
TomMottatt = pMottattTom

Dim i  As Integer, TI_ID As Integer

i = 0
    
    Dim rs As Recordset, rsAtt As Recordset

    CurrentDb.Execute "Delete from Temp_Innboks", dbFailOnError
    CurrentDb.Execute "Delete from Temp_Innboks_Vedlegg", dbFailOnError
           
    Set rs = CurrentDb.OpenRecordset("Temp_Innboks", dbOpenDynaset)
    Set rsAtt = CurrentDb.OpenRecordset("Temp_Innboks_Vedlegg", dbOpenDynaset)
        

    For Each mItem In InboxItems
 
            If mItem.ReceivedTime >= FomMottatt _
                And mItem.ReceivedTime <= TomMottatt + 1 _
                And IIf(Len(pFra) > 0, InStr(mItem.SenderName, pFra), 1) > 0 _
                And IIf(Len(pEmne) > 0, InStr(mItem.subject, pEmne), 1) > 0 Then
            
                rs.AddNew
            
                TI_ID = rs("TI_ID")                     ' Ta vare på ID for lagring i Temp_Innboks_Vedlegg
                
                rs("Mottatt") = mItem.ReceivedTime
                rs("Fra") = mItem.SenderName
                rs("Emne") = mItem.subject
                rs("Innhold") = mItem.Body
                rs("Har vedlegg") = IIf(mItem.Attachments.Count > 0, True, False)
                
                                    
                rs.Update
                
                                
                For Each mAtt In mItem.Attachments
                    'strVedleggFilnavn = strVedleggFilnavn = mAtt.FileName
                    
                    rsAtt.AddNew
                    
                    rsAtt("TI_ID") = TI_ID
                    rsAtt("VedleggFil") = mAtt.FileName
                    
                    rsAtt.Update
                    
                Next
             
            End If
            
            i = i + 1
            If i >= pMaksEposter Then
                Exit For
            End If
    
    Next

Avslutt:
rs.Close
rsAtt.Close

Set olApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set mItem = Nothing
Set SubFolder = Nothing

PJ_Arkiv_Innboks.Requery

    DoCmd.Hourglass False
Exit Sub

HentInnboks_Error:
    MsgBox Err.Description
    GoTo Avslutt
    
End Sub

Open in new window

Each email has a value for the entryid that will allow you to directly address that email ... and each attachment of course in indexed so if you note attachment number 1 and it's name in your table then you can address each email and as such each attachment.

From an entryid to get the item use:

application.Session.GetItemFromID("00000000A6B27C64927F1E45934E9F5F3C971BE5C4032000")

Where 00000000A6B27C64927F1E45934E9F5F3C971BE5C4032000 is the entryid for a specific item in outlook.

Any help?

Chris
Avatar of Joar

ASKER

Thanks Chris!

I am not sure how to implement your code line?

In the attached code I have commented out my implemtation of your line, because I got a run time error saying something like (translated from norwegian):
The message api returns an unknown message. Try to start Outlook again.

The attached code (without your line) functions, but is not quite direct accessing the email item.
Would be happy to have my code modified to what is needed...
Sub ArkiverVedlegg(pEntryID As String, pVedlegg As String)
Dim olApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim mItem As MailItem

Dim InboxItems As Outlook.Items
Dim mAtt As Outlook.Attachment

On Error GoTo ArkiverVedlegg_Error

Set olApp = CreateObject("Outlook.Application")
Set Inbox = olApp.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)

Set InboxItems = Inbox.Items

'mItem = olApp.Session.GetItemFromID("00000000A6B27C64927F1E45934E9F5F3C971BE5C4032000")


InboxItems.Sort "ReceivedTime", True                    ' TRue = Synkende!!!
        
    Set rs = CurrentDb.OpenRecordset("Temp_Innboks", dbOpenDynaset)
    Set rsAtt = CurrentDb.OpenRecordset("Temp_Innboks_Vedlegg", dbOpenDynaset)

    For Each mItem In InboxItems
 
            If mItem.EntryID = pEntryID Then
                                               
                For Each mAtt In mItem.Attachments
                    If mAtt.FileName = pVedlegg Then
                       
                        mAtt.SaveAsFile ("c:\jobb\SHR\Elektronisk arkiv\Arkiv\" & mAtt.FileName)
                        Exit For
                     
                    End If
                   
                                            
                Next
                
                Exit For
                
            End If
            
    
    Next

Avslutt:
rsAtt.Close

Set olApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set mItem = Nothing

Exit Sub

ArkiverVedlegg_Error:
    MsgBox Err.Description
    GoTo Avslutt
    
End Sub

Open in new window

Have you extracted the entry id from the mail you want to access ... each mail has one when saved, and if stored in your database can be used to access the specific item ... note however entryid's change if the item is moved between folders.

Chris

Note I ask because at first sight you seem to have embedded the entry id of my mail item rather than a one that is yours

00000000A6B27C64927F1E45934E9F5F3C971BE5C4032000

00000000A6B27C64927F1E45934E9F5F3C971BE5C4032000
Avatar of Joar

ASKER

Yes I have and I have testet that this EntryID is given to the subroutine ArkiverVedlegg where parameter pVedlegg is the filename of the attactment
Avatar of Joar

ASKER

sorry, stupid as I am, of course I have not put my own entryID in your line. Buf how much of the other code is needed?
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Joar

ASKER

Thank you!
Glad it helped and thanks for the grade

Chris