outlook to access: follow up

You now need to step through this code and watch what is happening and make sure it is working. Turn on your access MDB otherwise this will not work.

Code: 
Public WithEvents olInbox As Outlook.Items


Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

Dim appAccess As Object 'late binding of the MSAccess object
Dim objDBase As Object 'late binding


If Item.Class = 43 Then
    Set appAccess = GetObject(, "Access.Application")
    'get a database
    Set objDBase = appAccess.CurrentDb

    'here I check the name of the current database - incase there is more than one open
    If objDBase.Name = "Orders.mdb" Then
    appAccess.Run "FromOutlook", Item
    End If

    Set appAccess = Nothing
    Set objDBase = Nothing




End If
    

Exit_olInbox_ItemAdd:
    
    Set olFolder = Nothing
    Exit Sub

Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olSentItems_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
End Sub
Private Sub Application_Quit()
    Set olInbox = Nothing
End Sub

Private Sub Application_Startup()
Set olInbox = OlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub
 
Unfortunately nothing seems to happen, is there something I should be looking for?

Am I supposed to do the OpenForm.NewOrders in the fromOutlook module?
 
actually alot should happen - you should be getting errors and you should be telling me about them Are you testing the code?

put this in outlook. Save it and then close outlook. Then open outlook and send yourself an email. What Happens?

Code: 
Public WithEvents olInbox As Outlook.Items


Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

Dim appAccess As Object 'late binding of the MSAccess object
Dim objDBase As Object 'late binding


If Item.Class = 43 Then
'    Set appAccess = GetObject(, "Access.Application")
'    'get a database
'    Set objDBase = appAccess.CurrentDb
'
'    'here I check the name of the current database - incase there is more than one open
'    If objDBase.Name = "Orders.mdb" Then
'    appAccess.Run "FromOutlook", Item
'    End If
'
'    Set appAccess = Nothing
'    Set objDBase = Nothing

MsgBox "hello"


End If
    

Exit_olInbox_ItemAdd:

    Exit Sub

Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olSentItems_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
End Sub
Private Sub Application_Quit()
    Set olInbox = Nothing
End Sub

Private Sub Application_Startup()


Set olInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub
 
New2VB said:
Just a MsgBox "hello"
Click to expand...
congratulations you have now set up a listener on your inbox in outlook.

Now either send all items to Access or using the same criteria you did on your Outlook Rule send only those items to Access.

I assume you will also want to move them also to your Orders folder.


Before coming back here
1. Take it step by step
2. Put some breaks in your code and step through it
3. Note down any error messages you get
4. Post here the code you have made and the error message and line that it happens on.
 
Ok, so have I got this right?

Public WithEvents olInbox As Outlook.Items
Click to expand...
Declare the Inbox as an Outlook item?

Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd
Click to expand...
Declare items being added to Inbox, any items added to be treated as objects?
Run the Err_olInbox_ItemAdd routine if there is an error

If Item.Class = 43 Then
Set appAccess = GetObject(, "Access.Application")
Click to expand...
If the new item is a particular item (Class 43 =??) then load the Access application?

Set objDBase = appAccess.CurrentDb
Click to expand...
Set objDbase as the database currently loaded in Access
If objDBase.Name = "Orders.mdb" Then
appAccess.Run "FromOutlook", Item
End If
Click to expand...
If the current database is called Orders.mdb run the module FromOutlook in the currently loaded Access application?

Set appAccess = Nothing
Set objDBase = Nothing
Click to expand...
reset the values of appAccess & objDbase?

Exit_olInbox_ItemAdd:
Exit Sub
Click to expand...
Quit the subroutine?

Private Sub Application_Quit()
Set olInbox = Nothing
End Sub
Click to expand...
Reset the value of olInbox from Outlook.Items to Nothing?

Private Sub Application_Startup()
Set olInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub
Click to expand...
Reset olInbox as a MAPI type application, set the default folder as Inbox?

I have now tested this a few times but I don't think the FromOutlook module is being called. I get the "1" MsgBox but nothing else...

The code in Outlook's VbaProject.OTM is...
Code: 
Public WithEvents olInbox As Outlook.Items

Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

    Dim appAccess As Object 'late binding of the MSAccess object
    Dim objDBase As Object 'late binding


If Item.Class = 43 Then
    Set appAccess = GetObject(, "Access.Application") 'get a database
    Set objDBase = appAccess.CurrentDb

If objDBase.Name = "Orders.mdb" Then 'here I check the name of the current database - incase there is more than one open
   appAccess.Run "FromOutlook", Item
End If

Set appAccess = Nothing
Set objDBase = Nothing

MsgBox "1"

End If
   
Exit_olInbox_ItemAdd:

Exit Sub

Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olSentItems_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
    
End Sub

Private Sub Application_Quit()
MsgBox "2"
    Set olInbox = Nothing
End Sub

Private Sub Application_Startup()
MsgBox "3"
Set olInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub

The code in Access's FromOutlook module is...
Code: 
Public Sub FromOutlook(ByRef olMail As Outlook.MailItem)
MsgBox "4"
End Sub
 
I think the problem may be here
Code: 
Public WithEvents olInbox As Outlook.Items

Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

    Dim appAccess As Object 'late binding of the MSAccess object
    Dim objDBase As Object 'late binding


If Item.Class = 43 Then
 MsgBox "1"
    Set appAccess = GetObject(, "Access.Application") 'get a database
    Set objDBase = appAccess.CurrentDb

When I send a new email the MsgBox "1" isn't firing.

Could we use something like
Code: 
  If Mailobject.UnRead Then
 
New2VB said:
I have now tested this a few times but I don't think the FromOutlook module is being called. I get the "1" MsgBox but nothing else...
Click to expand...
put a break point in Outlook at the beginning and step through the code after a new email comes in with F8. Check that you get your objects set for access correctly.
 
then take out this If
If Item.Class = 43 Then
 
I have tried
Code: 
Public WithEvents olInbox As Outlook.Items
Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd
MsgBox "1"
    Dim appAccess As Object 'late binding of the MSAccess object
    Dim objDBase As Object 'late binding
but I don't get the MsgBox
 
Update:-

I can now get Msgbox "1" when new mail comes, MsgBox "2" when I close Outlook & MsgBox "3" when I open Outlook.

I am getting an error "Object variable or With block variable not set olInbox_ItemsAdd
Code: 
Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olInbox_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
and MsgBox "4" in the Access FromOutlook module isn't firing even though I have got the db open.
 
you need to say where, on which line you are getting it.

If you have this still then that will be a problem there is no mail.item just an ITEM.

appAccess.Run "FromOutlook", mail.Item

show me how your code looks now.
 
Outlook VbaProiject.OTM

Code: 
Public WithEvents olInbox As Outlook.Items
Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

    Dim appAccess As Object 'late binding of the MSAccess object
    Dim objDBase As Object 'late binding
 
If Item.Class = 43 Then
    Set appAccess = GetObject(, "Access.Application") 'get a database
    Set objDBase = appAccess.CurrentDb

If objDBase.Name = "Orders.mdb" Then 'here I check the name of the current database - incase there is more than one open
   MsgBox "1" ' appAccess.Run "fromOutlook", Item
End If

Set appAccess = Nothing
Set objDBase = Nothing

End If

 
Exit_olInbox_ItemAdd:

Exit Sub

Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olInbox_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
    
End Sub

Private Sub Application_Quit()
MsgBox "2"
    Set olInbox = Nothing
End Sub

Private Sub Application_Startup()
MsgBox "3"
Set olInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
End Sub

Note:- I think objDbase isn't resolving CurrentDB properly as I don't get MsgBox "1"

FromOutlook module in Access
Code: 
Public Sub FromOutlook(ByRef olMail As Outlook.MailItem)
MsgBox "4"
End Sub
 
Do you know how to put a break point and use F8?

Do you know how to use Debug.print?

Try this

Code: 
Private Sub olInbox_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olInbox_ItemAdd

    Dim appAccess As Object 'late binding of the MSAccess object
    Dim objDBase As Object 'late binding
    
    MsgBox "My Item Class is " & Item.Class
    
 
If Item.Class = 43 Then
    Set appAccess = GetObject(, "Access.Application") 'get a database
    
    If TypeName(appAccess) = "Nothing" Then MsgBox "Failed to get Access"
    
    Set objDBase = appAccess.CurrentDb
    
    If TypeName(objDBase) = "Nothing" Then MsgBox "Failed to get current DB"
        
    MsgBox "objDBase.Name is " & objDBase.Name

If objDBase.Name = "Orders.mdb" Then 'here I check the name of the current database - incase there is more than one open
   MsgBox "1" ' appAccess.Run "fromOutlook", Item
End If

Set appAccess = Nothing
Set objDBase = Nothing

End If

 
Exit_olInbox_ItemAdd:

Exit Sub

Err_olInbox_ItemAdd:
    MsgBox Err.Description & " olInbox_ItemAdd", vbCritical, "Error"
    Resume Exit_olInbox_ItemAdd
    
End Sub
 
Aha...

it was supposed to be looking for C:\Orders.mdb. Yeehaaa.

But I now get the error
"Orders can't find the procedure 'fromOutlook'.olInbox.ItemAdd
 
on which line? do you get that error.

how exactly have your writtne the public procedure in access

is it like this "fromOutlook"
 
all I get is an error box, no line shows up in the code

Access code
Code: 
Public Sub fromOutlook(ByRef olMail As Outlook.MailItem)
MsgBox "4"
End Sub

can I attach a jpg to show you the layout?
 
Ok so you still do not know how to debug.

You need to google search debugging with VBA. You need to learn how to put a break in place, and then how to use F8 to step through your code. While doing this you can put your mouse over objects and variables to see their values.

Learn how to do this and you will see where the error is.
 
I am used to defective code being highlighted yellow when an error occurs which I can then mouse-over to establish it's value and resolve the problem.

I have tried setting breakpoints and using F8 to step through the code but neither has any effect, i.e nothing happens, this is why I have to keep asking you what is going on and using MsgBoxes.

I appreciate you are probably getting frustrated with the amount of your time this is taking and I dearly hope we are nearly finished with this thing.
 
You must log in or register to reply here.

Similar threads

A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
462
Gasman
Gasman
T
Access When emailing brings up Outlook 2016 wizard to add email account
Replies
10
Views
510
The_Doc_Man
The_Doc_Man
C
querying outlook
Replies
2
Views
274
CJ_London
C
Jason Lee Hayes
Solved Open Outlook & check for new mail where search criteria is applied to the email subject line
Replies
19
Views
751
Jason Lee Hayes
Jason Lee Hayes
R
Using Outlook, Word and Access to send bulk emails.
2
Replies
22
Views
975
Silversee
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom