I am trying to pull an email from outlook and grab the excel table that was attached in that email. The code works fine, except the fact that I can't understand how to let VBA code to grab only today's email, otherwise it keeps grabbing previous days email and saving numerous files with the current date.

Here is my code:

Sub ExportOutlookTableToExcel()
Dim oLookInspector As Inspector
Dim oLookMailitem As MailItem

Dim oLookWordDoc As Word.Document
Dim oLookWordTbl As Word.Table

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlWrkSheet As Excel.Worksheet

'Grab Email Item
 Set oLookMailitem = Application.ActiveExplorer.CurrentFolder.Items("Apples Sales")

 Set oLookInspector = oLookMailitem.GetInspector

 Set oLookWordDoc = oLookInspector.WordEditor

 'Create a New Excel App
 Set xlApp = New Excel.Application

xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Add
Set xlWrkSheet = xlBook.Worksheets.Add

Set oLookWordTbl = oLookWordDoc.Tables(2)
oLookWordTbl.Range.Copy
xlWrkSheet.Paste Destination:=xlWrkSheet.Range("A1")
xlBook.SaveAs FileName:="xxx" & Format(Now, "yyyy-mm-dd") & ".xlsx"

End Sub

CodePudding user response：

You need to sort the Items collection first, then grab the first match:

set folder = Application.ActiveExplorer.CurrentFolder
set items = fodler.Items
items.Sort "ReceivedTime", true 'sort in descending order
set oLookMailitem = items.Find("[Subject] = 'Apples Sales'")
if not (oLookMailitem is Null) Then
  ..
End If

CodePudding user response：

To find only today's emails in Outlook you can use the Find/FindNext or Restrict methods of the Items class. Read more about these methods in the following articles:

• How To: Use Find and FindNext methods to retrieve Outlook mail items from a folder (C#, VB.NET)
• How To: Use Restrict method to retrieve Outlook mail items from a folder

For example, to find items on a specific date you can use the following code in C#:

private void FindItems(Outlook.MAPIFolder folder)
{
    string dateTimeStart = DateTime.Now.ToString("MM/dd/yyyy hh:mm tt");
    DateTime dt = new DateTime(DateTime.Now.Year, DateTime.Now.Month,
                               DateTime.Now.Day, 0, 0, 0, 0);
    string dateTimeEnd = dt.ToString("MM/dd/yyyy hh:mm tt");
    string searchCriteria = "[ReceivedTime]<=\""   dateTimeEnd   "\"";
    StringBuilder strBuilder = null;
    int counter = default(int);
    Outlook.MailItem mailItem = null;
    Outlook.Items folderItems = null;
    object resultItem = null;
    try
    {
        folderItems = folder.Items;
        folderItems.Sort("[ReceivedTime]");
        if (folderItems.Count > 0)
        {
            resultItem = folderItems.Find(searchCriteria);
            if (resultItem != null)
            {
                strBuilder = new StringBuilder();
                do
                {
                    if (resultItem is Outlook.MailItem)
                    {
                        counter  ;
                        mailItem = resultItem as Outlook.MailItem;
                        strBuilder.AppendLine("#"   counter.ToString()  
                                              "\tSubject: "   appItem.Subject);
                    }
                    Marshal.ReleaseComObject(resultItem);
                    resultItem = folderItems.FindNext();
                }
                while (resultItem != null);
            }
        }
        if (strBuilder != null)
            Debug.WriteLine(strBuilder.ToString());
        else
            Debug.WriteLine("There is no match in the "  
                               folder.Name   " folder.");
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
    finally
    {
        if (folderItems != null) Marshal.ReleaseComObject(folderItems);
    }
}

In VBA you can use the Format function which prepares the date time object for using in Outlook search conditions:

sFilter = "[ReceivedTime] > '" & Format("12/28/2021 0:00am", "ddddd h:nn AMPM") & "'"

Note, you can combine the search string with any other properties like the Subject line. Just use the AND operator in the search string. Read more about string operators in the String (for Text fields) section.

Also you may find the AdvancedSearch method of the Application class helpful. 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). You can read more about this in the Filtering article in MSDN. 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 this method in the Advanced search in Outlook programmatically: C#, VB.NET article.