• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

VBA MS Outlook to MS Access (2007)

Hey Experts!

I'm looking for VBA that I can put in outlook 2007 that will grab the email sent date/time, email senders email address, email subject, and email body from an email and put it in an access 2007 form (or table).  I would prefer that it put it in the form.

I can read and understand VBA to a certain extent so a basic example that I can make work will suffice.  One other thing is that the senders are stored in a separate table in the database.  A simple error handler to deal with instances where a contact is not set up would be appreciated - but is not required.

I am aware that I can create forms that are emailed - I am looking for VBA for outlook so that when I receive an email I can click the macro button in outlook and it will populate the open form in access (note both apps will be open and the form that I need outlook to fill will also be open).
0
NEVAEHSIN
Asked:
NEVAEHSIN
  • 7
  • 4
1 Solution
 
darbid73Commented:
I think I have commented the code below pretty well.

This will get you started.  you will need to cut and copy and then check.

Then you can add your outlook code as a macro to your toolbar.  I note you have 2007 (me 2003) - I do not but lets see if that causes any problems.

'++++++++++++++++++++++++++++++++++++++++++++
'code for outlook
'+++++++++++++++++++++++++++++++++++++++++++
Sub sendtoaccess()

Dim appAccess As Object
Dim objDBase As Object
Dim out_app As Outlook.Application
Dim out_Exp As Outlook.Explorer
Dim out_Sel As Outlook.Selection
Dim out_mail As Outlook.MailItem
Dim fs As Object

'you do not need to have this set but I have done it this way to re-use the code.
Set out_app = Application

'get the active explorer
Set out_Exp = out_app.ActiveExplorer

'this is for when they have nothing choosen and are on the main page of outlook
If out_Exp.CurrentFolder.WebViewOn = False Then
        Set out_Sel = out_Exp.Selection
    Else
        MsgBox "wrong item type"
        Set out_Exp = Nothing
        Exit Sub
    End If

'first make sure they have only choosen one item if not exit sub
If out_Sel.Count > 1 Then
    MsgBox "more than one item"
    Exit Sub
Else
    If out_Sel.Count = 0 Then
    MsgBox "nothing choosen"
    Exit Sub
    End If
End If

'check that it is in fact an email that has been choosen
If Not out_Sel.Item(1).Class = olMail Then
    MsgBox "not an email"
    Exit Sub
End If


'next try to get the email item - if there is an error 13 then it is encrypted - you may not need this one
On Error Resume Next

Set out_mail = out_Sel.Item(1)

If Err.Number = 13 Then
    Err.Clear
    MsgBox "email encrypted"
    Exit Sub
End If


'***********************************
'now you have your email item so you can get any property you want
'I will just show an example
'

Dim str_emailBody As String
Dim str_emailSubject As String

str_emailBody = out_mail.Body
str_emailSubject = out_mail.Subject
'add your others here


Set appAccess = GetObject(, "Access.Application")

If Err.Number = 429 Then
    Err.Clear
    MsgBox "no access"
    Exit Sub
End If

'There are other ways to do this but I use this way in case there is more than one access open.

Set objDBase = appAccess.CurrentDb

If objDBase.Name = "full name of your db here" Then
    appAccess.Run "outlookToAccess", str_emailBody, str_emailSubject
End If

Set appAccess = Nothing
Set objDBase = Nothing
Set out_Exp = Nothing
Set out_Sel = Nothing
Set out_mail = Nothing
Set out_app = Nothing

End Sub


'************************
'for access
'******************
'this must be a public function in a module
'you just add your variables to this function as you add them to your routine in outlook
Public Function outlookToAccess(ByVal olmail_body As String, ByVal olmail_subject As String)

'you said your form would be loaded but if not I would just do a check like this - you could actually call the form if it is not on if you want.

If Not CurrentProject.AllForms("yourform").IsLoaded Then
    msgbox "form is not loaded"
    exit function
end if

forms("yourform").txt_body = olmail_body
forms("yourform").txt_subject = olmail_subject


end function

Open in new window

0
 
Patrick MatthewsCommented:
Hello NEVAEHSIN,

Access can actually connect to an Outlook/Exchange data store; you may want to explore that angle instead.
The chief benefit is that that way you never need to worry about synchronizing the data in Access with what is
presently in Outlook.

The process for doing this is similar to the process for setting up a linked table.

Regards,

Patrick
0
 
NEVAEHSINAuthor Commented:
matthewspatrick -  can you provide some links that I can research?
darbid73 - I'm still trying to impliment yours - no issues as of yet - my phone just won't stop ringing today :p
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
NEVAEHSINAuthor Commented:
darbid73 -
I haven't tried it yet - I'm just touching up the access module - and I'm a little confused.  In the code you supplied for the module you put:

'you just add your variables to this function as you add them to your routine in outlook
Public Function outlookToAccess(ByVal olmail_body As String, ByVal olmail_subject As String)

but I don't see how access knows what ol... is (I'm assuming the ByVal has something to do with it).  So am I correct in assuming that for the sender putting in 'ByVal olmail_Sender.Address As String would work?
0
 
darbid73Commented:
Besically what I did was Outlook will call a MSAccess function.  That function name is "outlookToAccess"

To make this function visible to Outlook it must be in a module and be public.

Then my example only had a subject and body.  You wanted more.  So you need to add what ever you want.  Assuming they are all strings then you would need to do 3 things

1. get the information from the mail object "out_mail" eg maybe you add "out_mail.sender"
2. expand you function in outlook and access to handle this.
Access
Public Function outlookToAccess(ByVal olmail_body As String, ByVal olmail_subject As String, ByVal Olmail_sender as string)
Outlook
appAccess.Run "outlookToAccess", str_emailBody, str_emailSubject, str_emailSender

Basically you can keep adding information to your function like this.  As long as you keep them in the right order they will be passed to Access.

By the way once you understand that there is an easier way.

I just pass the object "out_mail" in my function to access

eg appAccess.Run "outlookToAccess", out_mail

Then in Access you have
Public Function outlookToAccess(ByRef olMail as outlook.mailitem)

Then in access you can get anything from this mailitem.  Because you have got the mailitem from outlook it is safe and there will be no security problems with access reading the body of the email or anything like that.
0
 
NEVAEHSINAuthor Commented:
This sounds awesome!  But, I'm stuck...  I simplified the code to jsut grab the subject and still no go...  It appears the code works for outlook - accept I don't think it's calling the access app.  Should the open form just populate in the background - or should it pop up infront?  I tried this right at the begining of the module in access:
Public Function outlookToAccess(ByVal olmail_subject As String)
MsgBox "it's calling me"
And nothing pops up - no errors - no access - nothing???
I've attached the modified code - note that I set it up to just grab the subject.  Also, both access and outlook are 2007 - if it makes a difference.
'++++++++++++++++++++++++++++++++++++++++++++
'code for outlook
'+++++++++++++++++++++++++++++++++++++++++++
Sub sendtoaccess()
 
Dim appAccess As Object
Dim objDBase As Object
Dim out_app As Outlook.Application
Dim out_Exp As Outlook.Explorer
Dim out_Sel As Outlook.Selection
Dim out_mail As Outlook.MailItem
Dim fs As Object
 
'you do not need to have this set but I have done it this way to re-use the code.
Set out_app = Application
 
'get the active explorer
Set out_Exp = out_app.ActiveExplorer
 
'this is for when they have nothing choosen and are on the main page of outlook
If out_Exp.CurrentFolder.WebViewOn = False Then
        Set out_Sel = out_Exp.Selection
    Else
        MsgBox "wrong item type"
        Set out_Exp = Nothing
        Exit Sub
    End If
 
'first make sure they have only choosen one item if not exit sub
If out_Sel.Count > 1 Then
    MsgBox "more than one item"
    Exit Sub
Else
    If out_Sel.Count = 0 Then
    MsgBox "nothing choosen"
    Exit Sub
    End If
End If
 
'check that it is in fact an email that has been choosen
If Not out_Sel.Item(1).Class = olMail Then
    MsgBox "not an email"
    Exit Sub
End If
 
 
'next try to get the email item - if there is an error 13 then it is encrypted - you may not need this one
On Error Resume Next
 
Set out_mail = out_Sel.Item(1)
 
If Err.Number = 13 Then
    Err.Clear
    MsgBox "email encrypted"
    Exit Sub
End If
 
 
'***********************************
'now you have your email item so you can get any property you want
'I will just show an example
'
 
Dim str_emailSubject As String
'Dim str_emailSender As String
'Dim str_emailSentOn As String
'Dim str_emailBody As String
 
str_emailSubject = out_mail.Subject
'str_emailSender = out_mail.Sender
'str_emailSentOn = out_mail.SentOn
'str_emailBody = out_mail.Body
'add your others here
 
 
Set appAccess = GetObject(, "Access.Application")
 
If Err.Number = 429 Then
    Err.Clear
    MsgBox "no access"
    Exit Sub
End If
 
'There are other ways to do this but I use this way in case there is more than one access open.
 
Set objDBase = appAccess.CurrentDb
 
If objDBase.Name = "iDo.1.Development.accdb" Then
    appAccess.Run "outlookToAccess", str_emailSubject ', str_emailSender, str_emailSentOn, str_emailBody
End If
 
Set appAccess = Nothing
Set objDBase = Nothing
Set out_Exp = Nothing
Set out_Sel = Nothing
Set out_mail = Nothing
Set out_app = Nothing
 
End Sub


Option Compare Database

'************************
'for access
'******************
'this must be a public function in a module
'you just add your variables to this function as you add them to your routine in outlook
Public Function outlookToAccess(ByVal olmail_subject As String) ', ByVal olmail_sender As String, ByVal olmail_SentOn As String, ByVal olmail_Body As String)
MsgBox "it's calling me"
'you said your form would be loaded but if not I would just do a check like this - you could actually call the form if it is not on if you want.
 
If Not CurrentProject.AllForms("frmE_TASK_H").IsLoaded Then
    MsgBox "form is not loaded"
    Exit Function
End If

Forms("frmE_TASK_H").TaskCatDet = olmail_subject
'Forms("frmE_TASK_H").TaskRequestor = DLookup("[Con_ID]", "[M_CON_H]", "[ConEmail1]='" & olmail_sender & "'")
'Forms("frmE_TASK_H").TaskRequestDate = olmail_SentOn
'Forms("frmE_TASK_H").TaskDetails = olmail_Body

End Function

Open in new window

0
 
NEVAEHSINAuthor Commented:
Update - with access closed when I run the macro I get "no access" :) So it is calling - but why no write :(  I'll keep playing.
0
 
NEVAEHSINAuthor Commented:
Got it!!!  I need the full path for the access object (as you suggested :p).  Thank you soooo much!
0
 
darbid73Commented:
I suggest you add some error trapping in there to make sure that things are working fine.

I did not help you here in fact I made it worse :-)

you see after my comment "'next try to get the email item - if there is an error 13 then it is encrypted - you may not need this one" there is a "On Error Resume Next" this is only there for the checking if its encrypted (something that I do not like)

before setting all your email variables (near these ***********)put something like this

On Error GoTo Err_sendtoaccess


Then just before the end of your code put this


Exit_sendtoaccess:
    Exit Sub

Err_sendtoaccess:
    MsgBox "sendtoaccess - " & Err.Description & " - " & Err.Number
    Resume Exit_sendtoaccess

 This is going to catch an error in your setting of objects for the access database.

and /Or to be a little bit more careful

use this after you set the access object.

If appAccess Is Nothing Then
      msgbox "we have a problem"
end if

also do a

debug.print objDBase.Name

to make sure your name is correct.

0
 
darbid73Commented:
thanks for the points.  If I were you I would try and only send the outlook mail item to Access.  This makes your outlook code very very simple.  Then you can do all your coding of what you like in access.
0
 
NEVAEHSINAuthor Commented:
Thanks for the suggestion - will do.  I am stuck on one thing with this - and it's driving me nuts...  I'm getting a 'Run-time error '2465' Application-defined or object-defined error' when trying to get the SentOn to populate a Date field.
I've tried changing the type to Date and still no go - everything else works great.  I have tried extensively testing by changing the field to text, I'm hoping you may be able to ping off the problem.  I'll keep playing - and googling.
'From the Access Module
'~
Forms("frmE_TASK_H").TaskRequestDate = olmail_SentOn

Open in new window

0
 
NEVAEHSINAuthor Commented:
I'm an idiot...  Typo... '.TaskRequestEDDate is what it should've been!  Thanks again for your help!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now