VBA & Oulook

[garydp]

Is there a way of searching through the sent items within outlook to see if a certain subject has been sent and then displaying the time and date within a range?

[garydp]

i see this has been moved but i am trying to do this from EXCEL not from outlook. I want to select the sent items from the outlook folder and search for a specific subject and then put that into a range in excel.

[gmayor]

You can do this from Outlook or Excel. The following code is for Excel and covers a basic function to look for a text string in the subjects of messages in the Sent items folder and reports back with the Date and time sent. You can format that date string as you wish and put it where you wish in the workbook. The only downside is that the code is likely to be slow if there are lots of messages to search through before it finds the one you want. It will be a bit faster is Outlook is already running.


Option Explicit

Public Function SearchSent(strSubjectText As String) As String
Dim olApp As Object
Dim olNS As Object
Dim olFldr As Object
Dim olItem As Object
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set olApp = CreateObject("Outlook.Application")
End If
Set olNS = olApp.GetNamespace("MAPI")
Set olFldr = olNS.GetDefaultFolder(5)
For Each olItem In olFldr.Items
If InStr(1, olItem.Subject, strSubjectText) > 0 Then
SearchSent = olItem.SentOn
'MsgBox olItem.Subject & vbCr & olItem.SentOn
Exit For
End If
Next olItem
lbl_Exit:
Set olApp = Nothing
Set olNS = Nothing
Set olFldr = Nothing
Exit Function
End Function

Sub Test()
'Use the value from SearchSent as required e.g.
MsgBox SearchSent("The text to find")
End Sub

[garydp]

excellent thank you, one other thing. What is the best way to open outlook from excel and have it minimized?

[gmayor]

The macro opens Outlook if it is not running.

[garydp]

i get an error when running this at this piece of code

Set olApp = GetObject(, “Outlook.Application”)

error is “run -time error ‘429’:
ActiveX component cant create object

outlook trys to open but a window pops up saying

Enterprise vault outlook addin could not establish a connection to your exchange mailbox.
when outlook is open the code works fine

[gmayor]

It appears there is an issue with 'Enterprise vault outlook addin' when creating Outlook from code. Change


Set olApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set olApp = CreateObject("Outlook.Application")
End If

to


Set olApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
MsgBox "Start Outlook before running the macro!"
GoTo lbl_Exit
End If

[pelfman1]

I am looking to create a macro to take the active cell holder data, open outlook, and initiate an outlook search. results should be displayed in outlook itself

this code is NOT working, anyone have an idea ???

Sub outlooksearch()
Dim po As String
Dim olMail As Variant

Set mailapp = CreateObject("Outlook.Application")
Set Mail = mailapp.CreateItem(olMailItem)

Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
i = 1


For Each olMail In Fldr.Items
If InStr(olMail.Subject, "Application.Selection.Value") <> 0 Then
olMail.Display
i = i + 1
End If
Next olMail
End Sub