Analogous Outlook code to read info into an array (or collection or whatever)

[Dr. Demento]

Operating system::    Win 11
Outlook version:     365
Email type or host:    365 Exchange

---

In Excel, you can copy a range into an array (Range.value = array), thereby removing the time/memory needed to constantly access the spreadsheet. Is there an analogous method to read email data (send, subject, to, body, attachments, etc) into a similar digital construct en masse as an Excel array so that each individual email doesn't need to be accessed.

It's probably important to note that most of these emails are digitally encrypted; if this isn't possible for encrypted email, is there a method for unencrypted emails??

Thanks much.

 

[Diane Poremsky]

You can read the values into an array - but would need to touch each message to get the field(s) you need. Basically, get the values from each message field into a string - strSubject = olmail.subject & "," & strSubject. Then convert the finished string in to an array.

 

[Dr. Demento]

Copy. Thank you, Diane.

 

[Hornblower409]

One possible approach would be to construct a Search Folder that includes the Items you're interested in. But Search Folders come with a few limitations on where they can pull Items from, so this may not work for you.

Once you have the Search Folder built, you can access the Items in the folder using the standard Outlook Items collection syntax. You can walk it with "For Each" or get an individual Item with "Items(n)".

e.g. With a Search Folder as the Active Explorer:

    Dim oFolder As Outlook.Folder
    Set oFolder = ActiveExplorer.CurrentFolder
    
    Dim Item As Object
    For Each Item In oFolder.Items
        Debug.Print Item.Subject
    Next Item
    
    Dim Items As Outlook.Items
    Set Items = oFolder.Items
    Dim Inx As Long
    For Inx = 1 To Items.Count
        Debug.Print Items(Inx).Subject
    Next Inx

 

[Hornblower409]

If your selection criteria are dynamic then you could use a DASL Filter and Application.AdvancedSearch instead of a Search Folder to get a Results collection. (I find that DASL Filters can get pretty complex really quick and the syntax is abominable). A similar approach to get a subset of Items from a Folder (also using a DASL Filter) is to build a Table Object. The advantage here is that the Table Object looks to you like a two dimension array of Rows (Items) and Columns (Fields from the Item).

 

[Hornblower409]

To build some DASL queries you can use the Outlook View Filter dialog on any View to build your query using the Filter tabs and then copy the DASL from the "SQL" tab. See these Slipstick articles:

Create views using SQL/DAVX filters
Using Query Builder

e.g. Using Query Builder

e.g. Using the Messages, More Choices, and Advanced tabs

 

[Hornblower409]

And then there is Items.Restrict to dynamically filter a Folder's Items collection. (Sorry for all the separate post. Microsoft has so many ways to skin this cat, that every time I finish one post I remember another one). There is an overview of the different ways you can filter a single folder here. But for queries that span multiple folders you have to look at other methods.

 

[Hornblower409]

On rereading your original post (and looking up how an Excel Range works) I think you want a Table.

"You can regard each row of a Table as an item in the folder, each column as a property of the item, and the Table as an in-memory lightweight rowset that allows fast enumeration and filtering of items in the folder. If you don't specify any filter, you'll obtain all the items in the folder."