Emails in Access

[Chrisopia]

I was just wandering how easy and if its possible to use Access as an email system, using outlook in the background, but access as the GUI...

the idea is emails from already known users on a given table could be linked, and hence record the emails in the database... making it much easier to read, review and archive previous emails from that person.

Also being able to receive emails from new clients with the ability to add them to the database, and the ability to send and reply to emails by clicking a button on the relevant form...

it sounds like a lot, but its quite an exciting project and will improve work efficiency tremendously!!

I am not asking for the solution, I just need a starting point, e.g. a link about how to connect access to outlook, and any advice you can give.

I hope to finish this project b the end of summer, and will publish how I accomplished it in a walk through guide as I believe this will be a very useful tool for people to use!

(Any advice will be credited in the guide)

 

[darbid]

I was just wandering how easy and if its possible to use Access as an email system, using outlook in the background, but access as the GUI...

In my opinion Outlook is the GUI for a database of items (eg. mail, calendar, tasks etc)

This is evident from the fields and searches for example that you can do.

Thus replicating this in Access seems like doing the database side of things twice.

Also turning Access into a mail client also seems strange unless you want to do away with paying for Outlook or not give users access to Outlook.

As you are in the starting phase my approach to this would be to accept that my data is being stored in a .pst file in Outlook (ie a database) and then asking the question of what you want to do with the data.

Another way of putting it is is assume you get all your emails in an Access database what are you then going to do with them?

 

[Chrisopia]

It is nothing against Outlook, just at the moment in time Outlook is a separate entity to the database, so we need to copy and paste emails to email them once we found the email, or to add them to the database...

its also confusing to find previous emails from the same contact in an instant and confusing sometimes if they send a new email instead of replying, integrating the emails into the database allows room for control and efficiency in data management within the company.

I already found, experimented and used a way to send emails, where it uses the email address from the database, and a given reply address connecting through access... I just need to adapt access to be able to receive emails etc...

I believe keeping everything as one single programme will improve everything so its worth the hard work it will take to create it. Receiving emails in access will allow them to be filed with the individual user and track the history of emails etc..

 

[darbid]

First please note that I have kindergarten experience compared to some in this forum.

I already found, experimented and used a way to send emails, where it uses the email address from the database, and a given reply address connecting through access.

Are you using Outlook for this?

I just need to adapt access to be able to receive emails etc...

1. Access acting as an email client - I am not sure this is easy. I did a little search http://bytes.com/topic/access/answers/203278-email-client-within-access

2. Using Outlook.

This is not going to be easy but here are my Air thoughts.

In outlook you will need to set up some code. The two events you need to look at are send and receive.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

'**************
'mynotes on what you would do now
'**************

1. check that Object is olMail item
2. determine based on your criteria that this mail should be added to the DB
3.  extract the information from the email
4. open a connection to your database by DAO or ADO
5. add new email sent record to DB


(I would suggest that you then get the ID number of this DB record and add this ID to the "mileage" field of the email item.  Then you will always be able to find this email in outlook again (unless it is deleted) 


End Sub

Private Sub Application_NewMailEx _
                     (ByVal EntryIDCollection As String)
    Dim arr() As String
    Dim i As Integer
    Dim ns As Outlook.NameSpace
    Dim itm As MailItem
    Dim m As Outlook.MailItem
    On Error Resume Next
    Set ns = Application.Session
    arr = Split(EntryIDCollection, ",")
    For i = 0 To UBound(arr)
        Set itm = ns.GetItemFromID(arr(i))
        If itm.Class = olMail Then
            Set m = itm
            
'**************
'mynotes on what you would do now
'**************

pretty much as above only now you are saving an email received.  Here I would also suggest that you save a unique ID number in the "mileage" field of the email to find it again.

        End If
    Next
    Set ns = Nothing
    Set itm = Nothing
    Set m = Nothing
End Sub

You can use the emailItem ID but if the users changes the folder of the emailItem it is possible that the ID will change.

 

[Chrisopia]

This is a great starting point! Thanks!

(and yes it does need to go through Outlook as some computers have 3 email accounts to send and receive from)

 

[darbid]

This is a great starting point! Thanks!

(and yes it does need to go through Outlook as some computers have 3 email accounts to send and receive from)

Ok so first when sending with code from Access you need to decide from which email account (Profile) this email will be sent.

Second the easiest would be to make it a rule that ALL emails to do with this topic be sent from Access, then you will at least have the control and be able to save in the DB all sent emails. Remember that I would add a unique ID to the "mileage" field of the email so that Acces can always find the email again.

This would also mean that you do not have to have code in Outlook for Sent emails.