CODEWITHSUNDEEP
excelvbaemailoutlookoffice-automation
AoT_1503
AoT_1503

Reputation: 51

Check outlook emails of past 7 days

I am trying to code a macro which checks the mails of the default outlook folder of the last seven days and extracts the body of the mail to an existing excel sheet if the mail contains a specific subject and sender name.

I already coded a macro, which checks every new mail as soon as received and extracts the content to excel if the specific subject & sender name is given. Although it worked, it was not a good solution for me to automatically check every new incoming mail. The code is:

Option Explicit
Private WithEvents olItems As Outlook.Items

Private Sub Application_Startup()

'Variablen dimensionieren
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace


'Variabeln initialisieren

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olItems = olNS.GetDefaultFolder(olFolderInbox).Items


End Sub



Private Sub olItems_ItemAdd(ByVal item As Object)

'Variablen dimensionieren
Dim olMail As Outlook.MailItem
Dim oxLApp As Object, oxLwb As Object, oxLws As Object


'Prüfen ob Item eine Mail ist
If TypeName(item) = "MailItem" Then

    
    Set olMail = item
    
If InStr(olMail.Subject, "APPROVAL REQUIRED") And _
olMail.SenderName = "Test, Name" Then

Set oxLApp = GetObject(, "Excel.Application")
Set oxLwb = oxLApp.Workbooks.Open _
("C:\Users\A2000\Desktop")
Set oxLws = oxLwb.Sheets("Slide 3")

With oxLws

    .Range("Q24") = olMail.VotingResponse
    .Range("E41") = olMail.Body
    

End With
End If
End Sub

Any ideas how to check the mails of the last seven days?

Upvotes: 1

Views: 163

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

You can use the Restrict or Find/FindNext methods of the Items class to get items that corresponds to the search criteria. Read more about them in the following articles:

The search criteria can be:

sFilter = "[RecievedTime] > '" & Format("1/15/22 3:30pm", "ddddd h:nn AMPM") & "'"

The MailItem.ReceivedTime property returns a Date indicating the date and time at which the item was received.

If you need to get such items from multiple folders at once you may consider using the AdvancedSearch method of the Application class. The key benefits of using the AdvancedSearch method in Outlook are:

  • The search is performed in another thread. You don’t need to run another thread manually since the AdvancedSearch method runs it automatically in the background.
  • Possibility to search for any item types: mail, appointment, calendar, notes etc. in any location, i.e. beyond the scope of a certain folder. The Restrict and Find/FindNext methods can be applied to a particular Items collection (see the Items property of the Folder class in Outlook).
  • Full support for DASL queries (custom properties can be used for searching too). To improve the search performance, Instant Search keywords can be used if Instant Search is enabled for the store (see the IsInstantSearchEnabled property of the Store class).
  • You can stop the search process at any moment using the Stop method of the Search class.

Read more about that in the Advanced search in Outlook programmatically: C#, VB.NET article.

Upvotes: 1

Related Questions