?
Solved

VBA MS Outlook to MS Access (2007)

Posted on 2010-01-12
12
Medium Priority
?
364 Views
Last Modified: 2013-11-28
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
Comment
Question by:NEVAEHSIN
[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
  • 7
  • 4
12 Comments
 
LVL 20

Accepted Solution

by:
darbid73 earned 2000 total points
ID: 26292687
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26292854
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
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 26293359
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 26293593
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
 
LVL 20

Expert Comment

by:darbid73
ID: 26293920
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
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 26294192
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
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 26294255
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
 
LVL 1

Author Closing Comment

by:NEVAEHSIN
ID: 31676056
Got it!!!  I need the full path for the access object (as you suggested :p).  Thank you soooo much!
0
 
LVL 20

Expert Comment

by:darbid73
ID: 26294390
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
 
LVL 20

Expert Comment

by:darbid73
ID: 26294426
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
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 26295218
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
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 26295238
I'm an idiot...  Typo... '.TaskRequestEDDate is what it should've been!  Thanks again for your help!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

762 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