Suggestions for solution to run access macro in receipt of email

[Broker666]

Hi,

Currently I have a rule within Outlook to move mails meeting certain criteria to a specific folder and display a msgbox confirming that a new mail (which contains a report in text file format) has been received. I then run some code within Access which downloads the text file attachment, converts it to Excel format and then imports the data into a table. What I would like to do is bypass this step and have the code automatically run when the mail is received. So basically test to see if a current session of Access is open, or if not open one, and then run the macro. I will probably include an If statement based on the result of a displayed msgbox to give the user discretion as to whether or not to import the file. I know that there is a runcode option within Outlooks rule options, I'm just unsure of the specfic code to use in Outlook to achieve my aim. Any advice would be greatly appreciated.

Regards,

B666.

 

[Broker666]

Ok, I have it running now using the below code but still can't work out how to test for an open session of Access and only create one if it is not already open, otherwise sub routine in the current session?

Sub RunAccessMacro(MyMail As MailItem)


Dim LPath As String
Dim LCategoryID As Long
Dim DATABASE As String
Static oApp As Access.Application

DATABASE = "C:\OS\FIPTDB\FIPTB.accdb"
Set oApp = GetObject(, "Access.Application")
If (Err.Number <> 0) _
Or (oApp.CurrentDb.Name <> DATABASE) Then


Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase DATABASE
oApp.Application.Run "Test"
oApp.CloseCurrentDatabase


End If



End Sub

 

[spikepl]

Try

If NOT YourObject Is Nothing Then
' it exists
ELSE
'it doesn't exist
End if

This

Static oApp As Access.Application

requires setting references to Access within Outlook, and that is a real pain when distributing the thing. I use late binding, so declare oApp As Object

You can also a query updating stuff in an Access db directly inside Outlook.

 

[Broker666]

Hi Spike,

That works perfectly, thankyou. I do however now have another problem.
When I substitute my 'test' macro (which just contained a msgbox as a proof of concept) with the intended macro it says that I am making an illegal function call.

The macro is linked to a button (Command60) and is called Sub Command60_Click(). It was originally a private sub which I thought was causing the issue but I have changed to a public sub and it still generates the same error message. The macro works fine when called in Access by clicking the button but generates the above error when the sub routine is called from Outlook. I have included the code in the attached text file, any ideas?