outlook to access: follow up

[New2VB]

OK, so from my last post (http://www.access-programmers.co.uk/forums/showthread.php?t=197719) I have had no replies and research, 2 weeks worth,
has indicated that this is an overly complex way of doing what I would like.

So my research as shown that the easiest method would be to simply (?) call the form directly from Outlook and this is where I am stuck. I have searched several boards and found massive amounts if information on how to get Access to send an email through Outlook but only 2 articles on using VB to get Outlook to enter email details in a table and 0 articles about getting Outlook to launch an Access form.

Currently my most successful code is:-

Sub Application_NewMail()
 
 ' Set up DAO Objects.
 Dim oDataBase As DAO.Database
 Dim rst As DAO.Recordset
 Set oDataBase = OpenDatabase("C:\Orders\Orders.mdb")
 Set rst = oDataBase.OpenRecordset _
("Orders", dbOpenDynaset, dbSeeChanges)

I think this opens the connection to the db and the table/recordset I want to work with.

Could anyone please tell me what the code is to launch the NewOrder form?

I have tried the DoCmd.OpenForm method but regardless of syntax I keep getting the 424 Object Required error.

Help please...

 

[New2VB]

Yay me...finally getting somewhere...

This works

Dim appAccess As Access.Application
    Set appAccess = New Access.Application
    appAccess.Visible = True
    appAccess.Application.OpenCurrentDatabase ("C:\Orders.mdb")
    appAccess.DoCmd.OpenForm "NewOrders"

BUT

1. It keeps opening the db and the form in some weird loop, how do you end it
2. How do you see whether Access is already open? If it is open the form, if not open the application then the form.

Any ideas please.

 

[darbid]

OK, so from my last post (http://www.access-programmers.co.uk/forums/showthread.php?t=197719) I have had no replies and research, 2 weeks worth,
has indicated that this is an overly complex way of doing what I would like.

I did see your other question but I have no experience with ExchangeServer so did not try to help.

So my research as shown that the easiest method would be to simply (?) call the form directly from Outlook

here is how my Outlook plays with access

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

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

'you need to deal with the case that access is not open like use the createobject if you want
If Err.Number = 429 Then
    MsgBox "access is not open"
End If

'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 = "YOUDBNAME.mdb" Then
    appAccess.Run "A_PUBLIC_ROUTINE_IN_A_MODULE", parameter1, parameter2
End If

Set appAccess = Nothing
Set objDBase = Nothing

A_PUBLIC_ROUTINE_IN_A_MODULE can be with or without parameters. You can also pass an object byref like an MailItem or a string byval.

 

[New2VB]

darbid, I am SO pleased you are here..saw your contributions to CHAOSinACT and hoped you would help me too..

OK, so I have got this (thanks for the GetObject tip) which opens Access to the form I want or loads the form if Access is already open.

Sub NewMail(NewMail As MailItem)

Dim NewOrder As String
NewOrder = "C:\Orders.mdb"

Dim appAccess As Access.Application
Set appAccess = GetObject(Console)
    appAccess.Visible = True
    appAccess.DoCmd.OpenForm "NewOrder"
    MsgBox "NewOrder", vbOKOnly

End Sub

My next mission is to bring the users attention to the new request. I had a look at setting the form as the foreground but got completely lost using the hWnd and TopMost methods described in http://support.microsoft.com/kb/84251. I am now trying a similar method using a MsgBox where clicking OK brings the form to the foreground.

After that all I have to do is try and find a way to match the senders name to list in the database and record a history of it all.

Happy days...

 

[New2VB]

Whoops, that should have read

Set appAccess = GetObject(NewOrder)

 

[darbid]

try one or BOTH of these

Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long

call it like this

SetForegroundWindow Application.hWndAccessApp

AND put this in a module

Option Compare Database
Option Explicit

Global Const SW_HIDE = 0
    Global Const SW_SHOWNORMAL = 1
    Global Const SW_SHOWMINIMIZED = 2
Global Const SW_SHOWMAXIMIZED = 3


Private Declare Function apiShowWindow Lib "user32" _
    Alias "ShowWindow" (ByVal hwnd As Long, _
          ByVal nCmdShow As Long) As Long

Function hideaccess()
    Forms(Forms.Count - 1).SetFocus
    fSetAccessWindow SW_SHOWMINIMIZED
End Function


Function fSetAccessWindow(nCmdShow As Long)
'Usage Examples
'Maximize window:
'       ?fSetAccessWindow(SW_SHOWMAXIMIZED)
'Minimize window:
'       ?fSetAccessWindow(SW_SHOWMINIMIZED)
'Hide window:
'       ?fSetAccessWindow(SW_HIDE)
'Normal window:
'       ?fSetAccessWindow(SW_SHOWNORMAL)
'
Dim loX  As Long
Dim loForm As Form
    On Error Resume Next
    Set loForm = Screen.ActiveForm
    If Err <> 0 Then 'no Activeform
      If nCmdShow = SW_HIDE Then
        MsgBox "Cannot hide Access unless " _
                    & "a form is on screen"
      Else
        loX = apiShowWindow(hWndAccessApp, nCmdShow)
        Err.Clear
      End If
    Else
        If nCmdShow = SW_SHOWMINIMIZED And loForm.Modal = True Then
            MsgBox "Cannot minimize Access with " _
                    & (loForm.Caption + " ") _
                    & "form on screen"
        ElseIf nCmdShow = SW_HIDE And loForm.PopUp <> True Then
            MsgBox "Cannot hide Access with " _
                    & (loForm.Caption + " ") _
                    & "form on screen"
        Else
            loX = apiShowWindow(hWndAccessApp, nCmdShow)
        End If
    End If
    fSetAccessWindow = (loX <> 0)
End Function

call like this

fSetAccessWindow (SW_SHOWMAXIMIZED)

I use both of these when Outlook calls Access and I want to bring Access and all forms to the front.

 

[New2VB]

It keeps getting better...

Dim NewOrder As String
NewOrder = "C:\Orders.mdb"

Dim appAccess As Access.Application
Set appAccess = GetObject(NewOrder)
    appAccess.Visible = True
    appAccess.DoCmd.OpenForm "NewOrder"
   
Dim Obj As Object
Set Obj = CreateObject("wscript.shell")
Obj.PopUp "New Order Request", 60

darbid, I had a good look at that code but there where 3 problems:
1. I'm too thick to understand it
2. I have no idea how to call the new module from the existing code and I don't want to bother you gurus with noob questions
3. I have no idea to apply the code to the launching of form

I do however appreciate your estimation of my abilities

so, I had a look around and found the wscript which pops open a MsgBox- type doohickey and flashes on the taskbar until you pay it some attention. This then fulfils my 3 basic requirements:
1. It's simple enough for me to understand (I am not a vb developer)
2. It's easy to find and fix if it goes wrong
3. Even though it is not exactly what I would have liked it serves the purpose.

Of course I would like to know if there is a better way but it has to fulfil requirements 1 & 2 above.

Now to work out how to get the sender's name to appear in the UserName field of the NewOrder form by matching the sender's email address to the UserName in the Users table...

 

[darbid]

The reason you are having problems is that you are opeing a form from outlook.

Why not follow my suggestion and call a public function in access from outlook.

This fucntion opens the form and then calls my suggested code. Thus

Form open
set access to the front.

 

[New2VB]

Hi,

Yes, I realised I was making my life difficult when I decided to use Outlook to handle incoming mail (I completely gave up on trying to get Exchange talking to SQL) I am bad enough with VB let alone SQL. I have spent hours trying to get the Outlook rule which runs a script when a new message arrives to even recognise that there was a script to run.

In typical MS fashion the script has to be presented in a certain way just to show up in the selection box.

I would very much like to get Outlook to pass all the data handling off to Access. So the methodology would be...

1. New email is received by an Outlook mailbox called Orders (this part is done. I created a user/mailbox on the Exchange server called Orders, installed Outlook on the server hosting the SQL backend and configured the Outlook mail account.This is where the code calling Access is running btw.)
2. NewOrder form loads with senders/user's details auto-populated by looking up the details in the "User's" table (under construction)
3. Recipient processes the order (can do this already through Access where a telephonic order is being placed and the employee manually launches the NewOrders form)
4. Recipient uses an OnClick "Submit" button which saves the processed info into the Orders table (same as 3 above, can already do this)

From what I have read, to get Access to handle the event you need a linked "Inbox" table which gets polled every few seconds looking for new mail or the Dimming of olApp criteria similarly polling Outlook for new messages every few seconds. This will create CPU resource usage (not much granted but more than needs be), and is another link in a chain of events which can break.

Basically I have no idea how to get Outlook to go "Oh, a new mail has arrived in my Inbox. I need to send this information to a form in Access" (other than what I have already done in the preceeding code) and then passing all the work over to Access.

I would be overjoyed if you hear your ideas.

Thanks for your time...

 

[darbid]

I would very much like to get Outlook to pass all the data handling off to Access. So the methodology would be...

1. New email is received by an Outlook mailbox called Orders (this part is done. I created a user/mailbox on the Exchange server called Orders and installed Outlook on the server hosting the SQL backend)

This is an event which Outlook does well. So use it. I hope you are doing this by checking for a new item in this folder?

So here is how I would do it.

1. Set up listener for new items in the Orders folder.
2. Check that item is in fact a mailitem and / or is a mailitem I want to deal with
3. send this mailitem as an object ie Outlook MailItem to Access.
4. Any and all forms pop up in Access
5. Any and all data needed can be got at or saved in Access

 

[New2VB]

If I had your knowledge and skill I would like to do it that way too.

Unfortunately I have neither and am learning vb on-the-fly researching many forums, too many to list here but at least a dozen, and trying to understand what other developers have done, why they have done it that way, cutting and pasting their code into mine and using trial and error to get the whole shebang to actually work.

When I still can't get it to work or have no idea where to start I rely on the genius of people like you, Poppa Smurf, vbaInet et al to reply to my questions and guide me down the path of righteousness.

How do I set up a listener would be a good example of not knowing where to start.

Many thanks again.

 

[darbid]

here is an example with sentitems. I am not sure if you can do it for your own folders or not. If you cannot you can do it with your inbox. You just have to add criteria to check that the incomming item is an item for the orders folder and then move it there in your code.

You put this at the very top of Outlook. As soon as you do this you will see that olSentItems becomes an object in your drop down list and you will then have all the events as well.

Public WithEvents olSentItems As Outlook.Items

You need to add this to when outlook Starts

Set olSentItems = OlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail).Items

Then this event will fire every time the folder gets any new item

Private Sub olSentItems_ItemAdd(ByVal Item As Object)
On Error GoTo Err_olSentItems_ItemAdd


    
If Item.Class = 43 Then

here you would sent the whole email object to an open access for the user to deal with it.
    

End If
    

Exit_olSentItems_ItemAdd:
    
    Set olFolder = Nothing
    Exit Sub

Err_olSentItems_ItemAdd:
    MsgBox Err.Description & " olSentItems_ItemAdd", vbCritical, "Error"
    Resume Exit_olSentItems_ItemAdd
End Sub

Dont forget to Set olSentItems =Nothing before Outlook closes down otherwise it will remain open as a process.

I dont expect you to get this the first time but have a play with it and learn as well.

 

[New2VB]

OK, I have pulled my code out of Outlook and am going to try the method you have suggested...

 

[New2VB]

I've already fallen over. I think I should add that I am dealing only with an Inbox (no SentItems, drafts or anything else) and all items coming into it will be mailitems i.e. only emails, no appointments or anything else. It is a dedicated mailbox.

I have the setup you can see in the attached jpg but now I cannot crteate an Outlook rule that runs the script when a new message arrives. There is no script name in the selection box.

I don't understand

You need to add this to when outlook Starts

Where in Outlook do I need to add it?

here you would sent the whole email object to an open access for the user to deal with it

I am not sending email, I need Access to receive it. How am I launching Access.

Please show me the code I am supposed to be entering. I need to call the form called NewOrders, [Forms]![NewOrders]![Opened] = Date/Time email was received, [Forms]![NewOrders]![User_Name] = Sender Name, [Forms]![NewOrders]![Requirement] = Subject.

Can you help please?

 

[darbid]

you need to use these events to set the inbox withevents and to set it to nothing.

Private Sub Application_Quit()

End Sub

Private Sub Application_Startup()

End Sub

create in you access DB in a module a PUBLIC SUB

Public sub FromOutlook(byref olMail as Outlook.Mailitem)



Then in your outlook code follow what I have already written to you previously

appAccess.Run "FromOutlook", mail.item

 

[darbid]

please post any future code as text not as a pic

 

[New2VB]

Is this what you mean? I'm sorry I am very far out of my depth here...

 

[New2VB]

sorry about pics..I am trying to show you what I am seeing

 

[darbid]

set olInbox in Application startup

set it to nothing in the quit

follow my previous code on how to communicate between Outlook and access. What you are doing obviously will error. At first just test with Access and you database already open and running.

Please do not post pictures

 

[New2VB]

set olInbox in Application startup

in which application startup? Outlook? Where in the code does it go?

Public WithEvents olInbox As Outlook.Items
Set olInbox = OlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items

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

If Item.Class = 43 Then
appAccess.Run "FromOutlook", mail.Item
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()

End Sub

Private Sub Application_Startup()

End Sub

set it to nothing in the quit

Where?

What am I doing wrong in the above code?

Do I still create an Outlook rule specifying the script must run when a new mail arrives?