How to Find and open an outlook email from its subject and get the date of reception to a cell

[yaninja]

Good afternnon Mr Excel's forum,

I am trying to
#1 Open the last email received containing a specific reference in the subject
#2 Save it in a specific folder in windows (let's say my documents)
#3 Store it in a subfolder in outlook( which is a shared mailbox, different from the defaultforlder
#4 Get it's date in a excel cells. (let's say sheet1.range("A1") with format yyyy mm dd hh:mm
I have already a begining of the code but as a new developper, I hope some of you could help me:

# it require to activate the library Microsoft Outlook 12.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Search_Inbox()

Dim myOlApp As New Outlook.Application
Dim objNamespace As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Dim filteredItems As Outlook.Items
Dim itm As Object
Dim Found As Boolean
Dim strFilter As String

Set objNamespace = myOlApp.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%Référence XYZ%'"

Set filteredItems = objFolder.Items.Restrict(strFilter)

If filteredItems.Count = 0 Then
Debug.Print "No emails found"
Found = False
Else
Found = True
' this loop is optional, it displays the list of emails by subject.
For Each itm In filteredItems
Debug.Print itm.Subject
itm.Display

Next
End If
'If the subject isn't found:
If Not Found Then
'NoResults.Show
Else
Debug.Print "Found " & filteredItems.Count & " items."
End If

'myOlApp.Quit
Set myOlApp = Nothing
End Sub</code>

---

the results displays all email containing the code but not only the last received, anyway, I guess that's a good point for starting.
I wish some of you guys could help me on the above code.
Many thanks in advance for your assistance.

With Discretion ...
YaNinja

 

[Domenic]

For the last emailed received, replace your For Each/Next loop with the following..

[FONT=Courier New][COLOR=darkblue]With[/COLOR] filteredItems
    Debug.Print .Item(.Count).Subject
    .Item(.Count).Display
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]

Hope this helps!