Access VBA to Search Outlook

[vik808]

Hey Guys,

Does anyone know how I could initiate a search from MS Access on an Outlook folder for an email addresss.

I would like to to work just as if you were using the outlook 'Try searching in All Mail Items'

Thanks for any pointers

 

[spikepl]

It's not that difficult but requires that you have a fair inkling of the Outlook Object Model (google it). Also try googling with essential keywords like outlook search vba etc. I do not recall seeing anything on this subject on AWF. I did it ages ago, but do not have the code.

 

[spikepl]

BTW: you can link to an Outlook folder directly from Access. However, any searches might be pretty slow, without the indexing, which is available from WIndows in Outlook.

 

[TJPoorman]

I created some code here that looks in outlook calendars, but it could be adapted to search other folders.

 

[vik808]

This seems to work for now...

Sub outlookSearch(searchString As String)
Dim app As Outlook.Application
'This will throw an error if there's no instances of Outlook running
'   so resume after the error.
On Error Resume Next
Set app = GetObject(, "Outlook.Application")
On Error GoTo 0 'Replace this with a real error handler
'If the app variable is empty
If app Is Nothing Then
    'Create a new instanc eof outlook
    Set app = CreateObject("Outlook.Application")
    'Add an explorer showing the inbox
    app.Explorers.Add app.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    'Make the explorer visible
    app.Explorers(1).Activate
End If
'Search all folders for searchString
app.ActiveExplorer.search searchString, olSearchScopeAllFolders
Set app = Nothing
End Sub

Thanks for the help!!

 

[spikepl]

The below is crap code, spread all over the web by some idiot "guru", and stemming from lack of understanding how Outlook and CreateObejct work together, when CreateObject "knows" that only one instance of Outlook is allowed.

So this BS:

'This will throw an error if there's no instances of Outlook running '   so resume after the error. 
On Error Resume Next 
Set app = GetObject(, "Outlook.Application") 
On Error GoTo 0 'Replace this with a real error handler
'If the app variable is empty 
If app Is Nothing Then     'Create a new instanc eof outlook
     Set app = CreateObject("Outlook.Application")

can all be replaced by

Set app = CreateObject("Outlook.Application")

 

[TJPoorman]

Or you can use this nice little bit of code:

Function isAppThere(appName) As Boolean
'---------------------------------------------------------------------------------------
' Procedure : isAppThere
' Author    : Rick Dobson, Ph.D - Programming Microsoft Access 2000
' Purpose   : To check if an Application is Open
' Arguments : appName The name of the Application
' Example   : isAppThere("Outlook.Application")
'---------------------------------------------------------------------------------------
Dim objApp As Object

On Error Resume Next
   
Set objApp = GetObject(, appName)

If Err.Number = 0 Then isAppThere = True
End Function

Then in your calling procedure:

If isAppThere("Outlook.Application") = False Then
    Set app = CreateObject("Outlook.Application")
Else
    Set app = GetObject(, "Outlook.Application")
End If

 

[spikepl]

@TJPoorman

No. Your code is based on the same totally erroneous assumption.

Set app = CreateObject("Outlook.Application")

is enough. CreateObject GETS the open instance of outlook, if there is one, or creates it, if there is not.

 

[vik808]

LOL. K I just used Set app = CreateObject("Outlook.Application")

Thanks

 

[spikepl]

Nice code. I might need such stuff shortly, so this is one of the links to be saved

 

[TJPoorman]

@TJPoorman

No. Your code is based on the same totally erroneous assumption.

Set app = CreateObject("Outlook.Application")

is enough. CreateObject GETS the open instance of outlook, if there is one, or creates it, if there is not.

http://msdn.microsoft.com/en-us/library/office/aa220083(v=office.11).aspx
Perhaps you should read the first line of this article before making your own assumptions. CreateObject creates a NEW instance of the object, thereby using more space.

 

[spikepl]

No it doesn't. Not for an application that can only live as one instance, like outlook. If you don't believe me, try it. Throw out your code and replace it with CreateObject. It DOES NOT create a second instance of Outlook.