0

In my question, In MS Outlook VBA, why is looping through a table five times faster than looping through the folder?, in the accepted answer by Dmitry Streblechenko, he made a comment that to efficiently include the count of attachments in the output from a table, I should first test for PR_HASATTACH being true, and only if not, do the slow access of MailItem.attachments.count. I implemented that suggestion with the following code:

nAttachments = iif(not(oTableRow("http://schemas.microsoft.com/mapi/proptag/0x0E1B000B")), 0, _
                   Application.GetNamespace("MAPI").GetItemFromID(oTableRow("EntryID")).Attachments.Count)

There are two problems with that. One is that it did not improve the efficiency because VBA's iif() stupidly evaluates both conditions. But the worse problem is that the value of the above code is always zero because

not(oTableRow("http://schemas.microsoft.com/mapi/proptag/0x0E1B000B"))

is mysteriously always true.

Why is that? And how do I fix the code? (I am posting this question in order to post the answer that I've found.)

NewSites
  • 1,402
  • 2
  • 11
  • 26
  • what does `oTableRow("http://schemas.microsoft.com/mapi/proptag/0x0E1B000B")` evaluate to? http://schemas.microsoft.com/mapi/proptag/0x0E1B000B doesn't seem to go anywhere if I type into google. Maybe the schema has moved? Also `NOT` is not a function `not()` but an operator without brackets e.g. `NOT oTableRow("http://schemas.microsoft.com/mapi/proptag/0x0E1B000B")`. If oTableRow returns an integer like 1 or 0, then NOT does a bitwise not rather than logical. So you need to check `oTableRow = 0` rather than `NOT oTableRow` – Greedo May 30 '23 at 11:00
  • `Iif()` is a function in vba so it makes sense it evaluates both arguments, consistent with other functions like `Max(a,b)`. There is no ternary operator in vba sadly. – Greedo May 30 '23 at 11:02
  • "http://schemas.microsoft.com/mapi/proptag/0x0E1B000B" is the "DASL name" of `PR_HASATTACH`. Sorry, I don't completely understand that. See a comment by Dmitry Streblechenko saying so at https://stackoverflow.com/questions/76268753/how-to-add-a-table-column-for-an-object-property/#76273756 – NewSites May 30 '23 at 15:50
  • In my coding style, I put optional parens around the argument of `not` because it makes the code easier to read, as well as for potential compatibility with other languages in which `not` is a function. Sorry if you disagree or don't like that style, but it's not wrong. – NewSites May 30 '23 at 15:55

1 Answers1

1

The problem is that VBA and MAPI use different definitions of the data type Boolean.

True False
VBA -1 0
MAPI 1 0

For VBA, the documentation Boolean Data Type (Visual Basic) states that "When Visual Basic converts Boolean values to numeric types, False becomes 0 and True becomes -1."

For MAPI, PR_HASATTACH is the associated property of the MAPI property PidTagHasAttachments, whose documentation at that link says that it has data type PT_BOOLEAN, whose documentation at that link links to [MS-OXCDATA]: Data Structures, which links to a pdf specification document, which states on page 90 that PT_BOOLEAN is "1 byte; restricted to 1 or 0" and on page 95 that "The Unicode value of the element is interpreted as a Boolean value either "1" (TRUE) or "0" (FALSE)."

VBA's documentation of not() does not explain the problems that can arise from such inconsitent definitions of true, but there has been extensive discussion of it on StackOverflow. In all of that discussion, I don't see any correct workaround, but there is a very simple one:

not(A) ==> (A = 0)

In my case, I was only using the not() to make the iif() code easier to read. Since I needed to get rid of iif() anyway, the simpler solution for me was simply to also drop the not(). With that, the code in the question transforms to:

If (oTableRow("http://schemas.microsoft.com/mapi/proptag/0x0E1B000B")) Then
    nAttachments = Application.GetNamespace("MAPI").GetItemFromID(oTableRow("EntryID")).Attachments.Count
  Else
    nAttachments = 0
  End If

Using that code, I got the correct attachment count in my results with the reduction in speed from 5 seconds per thousand items to 8 seconds, which is an acceptable cost of getting that count and is a lot better than the 29 seconds per thousand (34 items/second) that it took to loop through the folder instead of the table.

NewSites
  • 1,402
  • 2
  • 11
  • 26