Need help with MapiTable and EntryID/StoreID

[Peter Marchert]

Hello,

I did my first steps with MapiTable and it seems to be simple to
handle but this code does not work:

Sub MapiTableTest()

Dim objFolder As Outlook.MAPIFolder
Dim objTable
Dim Columns(2)
Dim Row

Const PR_SUBJECT As Long = &H37001E
Const PR_ENTRYID As Long = &HFFF0102
Const PR_STORE_ENTRYID As Long = &HFFB0102

Set objFolder = Outlook.ActiveExplorer.CurrentFolder

If objFolder.Items.Count > 0 Then

Set objTable = CreateObject("Redemption.MAPITable")

objTable.Item = objFolder.Items

Columns(0) = PR_SUBJECT
Columns(1) = PR_ENTRYID
Columns(2) = PR_STORE_ENTRYID

objTable.Columns = Columns

objTable.GoToFirst

Do

Row = objTable.GetRow

If Not IsEmpty(Row) Then

Debug.Print Row(0)
Debug.Print Row(1)
Debug.Print Row(2)

End If

Loop Until IsEmpty(Row)

End If

End Sub

Row(1) and Row(2) always return a type mismatch error. I don`t know
why and need some help. Row(0) returns the subject of the item without
problems.

Beside this problem I want to ask if there is a simple way to compare
two mapi tables? For example to compare the email adress and the
subject of all items in two folders and get only the different items.

Thanks in advanced!

Peter

 

[Dmitry Streblechenko]

Binary (PT_BINARY) properties are always returned as variant arrays by
Redemption. You can use MAPIUtils.HrArrayToString to convert a variant array
to a hex string.
Or you can use MAPIUtils.ExeSQL - it returns binary properties as hex
strings, plus restrictions are lot easier in SQL (just use a WHERE clause):

set Recordset = objTable.ExecSQL("SELECT EntryID, Subject from Folder")
while not Recordset.EOF
Debug.Print Recordset.Fields("Subject").Value
Debug.Print Recordset.Fields("EntryID").Value
wend

Why do you need to retrieve the PR_STORE_ENTRYID property? It will be the
same for all items in a folder; more than that, it will be the same for all
objects (messages and folders) in the store. You can retrieve it from
objFolder.StoreID.

The only way to compare two folders is to explicitly specify the cretiria
and compare the folders in your code. Two folders cannot be the same since
the parent folders are different, entry ids are different, etc even if all
other properties are the same. You need to specify your criteria of
sameness: just the subjects? Or also sender names/addresses? How about
message bodies? Or dates?

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

Hello,

I did my first steps with MapiTable and it seems to be simple to
handle but this code does not work:

Sub MapiTableTest()

Dim objFolder As Outlook.MAPIFolder
Dim objTable
Dim Columns(2)
Dim Row

Const PR_SUBJECT As Long = &H37001E
Const PR_ENTRYID As Long = &HFFF0102
Const PR_STORE_ENTRYID As Long = &HFFB0102

Set objFolder = Outlook.ActiveExplorer.CurrentFolder

If objFolder.Items.Count > 0 Then

Set objTable = CreateObject("Redemption.MAPITable")

objTable.Item = objFolder.Items

Columns(0) = PR_SUBJECT
Columns(1) = PR_ENTRYID
Columns(2) = PR_STORE_ENTRYID

objTable.Columns = Columns

objTable.GoToFirst

Do

Row = objTable.GetRow

If Not IsEmpty(Row) Then

Debug.Print Row(0)
Debug.Print Row(1)
Debug.Print Row(2)

End If

Loop Until IsEmpty(Row)

End If

End Sub

Row(1) and Row(2) always return a type mismatch error. I don`t know
why and need some help. Row(0) returns the subject of the item without
problems.

Beside this problem I want to ask if there is a simple way to compare
two mapi tables? For example to compare the email adress and the
subject of all items in two folders and get only the different items.

Thanks in advanced!

Peter

 

[Michael Bauer [MVP - Outlook]]

Dmitry, can SQL be used to get objects, e.g. Attachments?

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Sun, 12 Aug 2007 14:32:47 -0700 schrieb Dmitry Streblechenko:

 

[Peter Marchert]

Thank you Dmitry for your answer.

Binary (PT_BINARY) properties are always returned as variant arrays by
Redemption. You can use MAPIUtils.HrArrayToString to convert a variant array
to a hex string.
Or you can use MAPIUtils.ExeSQL - it returns binary properties as hex
strings, plus restrictions are lot easier in SQL (just use a WHERE clause):

set Recordset = objTable.ExecSQL("SELECT EntryID, Subject from Folder")
while not Recordset.EOF
Debug.Print Recordset.Fields("Subject").Value
Debug.Print Recordset.Fields("EntryID").Value
wend

That seems to be a better way, but my code runs into an infinitely
loop:

Sub MapiTableSQLTest()

Dim Table As Redemption.MAPITable
Dim Recordset

Set Table = CreateObject("Redemption.MAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

Set Recordset = Table.ExecSQL("SELECT EntryID, Subject from
Folder")

While Not Recordset.EOF
Debug.Print Recordset.Fields("Subject").Value
Debug.Print Recordset.Fields("EntryID").Value
Wend

End Sub

I tried this with the contacts folder and with the inbox folder. The
debug statement always returns the first item.

Why do you need to retrieve the PR_STORE_ENTRYID property? It will be the
same for all items in a folder; more than that, it will be the same for all
objects (messages and folders) in the store. You can retrieve it from
objFolder.StoreID.

Sorry, was a mistake by me. I tried to get the storeid form the table,
but I have the folder anyway so it is not necessary.

The only way to compare two folders is to explicitly specify the cretiria
and compare the folders in your code. Two folders cannot be the same since
the parent folders are different, entry ids are different, etc even if all
other properties are the same. You need to specify your criteria of
sameness: just the subjects? Or also sender names/addresses? How about
message bodies? Or dates?

Ok, thank you.

Peter

 

[Dmitry Streblechenko]

You can use SQL to perform restrictions against the attachment files names
(e.g. "Attachments LIKE 'filename%' "), but you cannot retrieve any
attachment properties. Just a MAPI limitation...

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

 

[Dmitry Streblechenko]

Sorry, I forgot that piece: you need to move to the next record. At the very
end of the while loop, add

Recordset.MoveNext

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

 

[Peter Marchert]

Now it works fine! Thank you, Dmitry.

Peter

Sorry, I forgot that piece: you need to move to the next record. At the very
end of the while loop, add

Recordset.MoveNext

Dmitry Streblechenko (MVP)http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool


















- Zitierten Text anzeigen -

 

[Peter Marchert]

Need help again

In the SQL-Statement I have sometimes characters which cannot be
parsed by the ExcecSQL-Method, for example:

strSQL = "SELECT Email1Address, Subject from Folder
where(Email1Address='(e-mail address removed)') and (Subject='Lands' End')"

What can I do to get these subjects too? I tried "" instead of ' but
this does not help.

Peter

 

[Dmitry Streblechenko]

Double the single quote :

Subject='Lands'' End'

note that '' above is two single quotes, not a single double quote.

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

 

[Peter Marchert]

Works fine! Thanks again!

Peter

Double the single quote :

Subject='Lands'' End'

note that '' above is two single quotes, not a single double quote.

Dmitry Streblechenko (MVP)http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool









- Zitierten Text anzeigen -

 

[Michael Bauer [MVP - Outlook]]

thanks, Dmitry.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Organize eMails:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>


Am Mon, 13 Aug 2007 09:54:05 -0700 schrieb Dmitry Streblechenko:

 

[Peter Marchert]

Here is the next problem

If a subject includes a prefix like "Re:", "FW:" or something else, no
records will be returned. Here is my SQL-String:

strSQL = "SELECT LastModificationTime, EntryID, ReceivedTime, Subject
FROM Folder WHERE (Subject='" & Replace(objItem.Subject, "'", "''") &
"') AND (ReceivedTime='" & Format(objItem.ReceivedTime, "yyyy-mm-dd
hh:nn:ss") & "')"

Hope for help

Peter

 

[Ken Slovak - [MVP - Outlook]]

In a SQL query if you don't want a full equality test you should use the
LIKE operator with wild cards where appropriate:

WHERE Subject LIKE '%store%' find restore, storefront, not
restoration
WHERE Subject LIKE 'store%' find storefront, not restore
WHERE Subject LIKE '%store' find restore, not storefront

 

[Peter Marchert]

Thanks for your answer, Ken.

I want a full equality test because I`m searching for dublettes (not
only in emails).

With more than 3 charachters it will work (e. g. "FWFW: ....") so I
don`t think this is a SQL Problem but I`m not sure.

Peter

--
Infos, workshops & soft-
ware for your Outlook®:
www.outlook-stuff.com

In a SQL query if you don't want a full equality test you should use the
LIKE operator with wild cards where appropriate:

WHERE Subject LIKE '%store%' find restore, storefront, not
restoration
WHERE Subject LIKE 'store%' find storefront, not restore
WHERE Subject LIKE '%store' find restore, not storefront

--
Ken Slovak
[MVP - Outlook]http://www.slovaktech.com
Author: Professional Programming Outlook 2007
Reminder Manager, Extended Reminders, Attachment Optionshttp://www.slovaktech.com/products.htm

Here is the next problem

If a subject includes a prefix like "Re:", "FW:" or something else, no
records will be returned. Here is my SQL-String:

strSQL = "SELECT LastModificationTime, EntryID, ReceivedTime, Subject
FROM Folder WHERE (Subject='" & Replace(objItem.Subject, "'", "''") &
"') AND (ReceivedTime='" & Format(objItem.ReceivedTime, "yyyy-mm-dd
hh:nn:ss") & "')"

Hope for help

Peter- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

 

[Ken Slovak - [MVP - Outlook]]

A SQL query doesn't care how many characters it's comparing as long as the
comparison is a valid one. One thing to be careful of with tests for things
like FW or RE is they are language specific of course.




Thanks for your answer, Ken.

I want a full equality test because I`m searching for dublettes (not
only in emails).

With more than 3 charachters it will work (e. g. "FWFW: ....") so I
don`t think this is a SQL Problem but I`m not sure.

Peter

 

[Dmitry Streblechenko]

For the Subject property restrictions, Redemption uses PR_NORMALIZED_SUBJECT
(subject without any prefixes) - it is indexed by Exchange and is a lot
faster than searching on PR_SUBJECT.
If you really need PR_SUBJECT, specify its in the DASL format (must be
enclosed with double quotes) - either "urn:schemas:httpmail:subject" or
http://schemas.microsoft.com/mapi/proptag/0x0037001E woudl do.
Also keep in mind that you should never "=" with date/time properties. You
will never get a match because of the round-off errors.; always use a range
(value +- 1 second should work)

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

 

[Ken Slovak - [MVP - Outlook]]

Or plus/minus 1 minute if using Outlook 2007 and any of the local to UTC or
UTC to local function calls <g>

 

[Peter Marchert]

Hmm, thank you Dmitry.

I tried all possibilities shown by Outlook Spy:

strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM Folder WHERE
(""urn:schemas:httpmail:subject""='" & Replace(objItem.Subject, "'",
"''") & "')"

strSQL = "SELECT ""urn:schemas:mailheader:subject"" FROM Folder WHERE
(""urn:schemas:mailheader:subject""='" & Replace(objItem.Subject, "'",
"''") & "')"

strSQL = "SELECT ""http://schemas.microsoft.com/mapi/proptag/
0x0037001E"" FROM Folder WHERE (""http://schemas.microsoft.com/mapi/
proptag/0x0037001E""='" & Replace(objItem.Subject, "'", "''") & "')"

But in all cases it seems to be returned only the subject without
prefixes. Can somebody confirm that this works on his machine?

Thanks for the hint with the time!

Peter

 

[Peter Marchert]

Thanks, Ken, for this hint, too.

Peter

--
Infos, workshops & soft-
ware for your Outlook®:
www.outlook-stuff.com

Or plus/minus 1 minute if using Outlook 2007 and any of the local to UTC or
UTC to local function calls <g>

--
Ken Slovak
[MVP - Outlook]http://www.slovaktech.com
Author: Professional Programming Outlook 2007
Reminder Manager, Extended Reminders, Attachment Optionshttp://www.slovaktech.com/products.htm

For the Subject property restrictions, Redemption uses
PR_NORMALIZED_SUBJECT (subject without any prefixes) - it is indexed by
Exchange and is a lot faster than searching on PR_SUBJECT.
If you really need PR_SUBJECT, specify its in the DASL format (must be
enclosed with double quotes) - either "urn:schemas:httpmail:subject" or
http://schemas.microsoft.com/mapi/proptag/0x0037001Ewoudl do.
Also keep in mind that you should never "=" with date/time properties.. You
will never get a match because of the round-off errors.; always use a
range (value +- 1 second should work)

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

 

[Peter Marchert]

Here is some code for testing:

Sub MapiTableSQLTest()

Dim objItem As Outlook.MailItem
Dim Table As Redemption.MAPITable
Dim Recordset
Dim strSQL As String

Set objItem = Outlook.ActiveExplorer.Selection(1)

Set Table = CreateObject("Redemption.MAPITable")
'Set Table = CreateObject("SafeOutlook.SecureMAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM Folder
WHERE (""urn:schemas:httpmail:subject""='" & Replace(objItem.Subject,
"'", "''") & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend

End Sub

If an email is marked with a prefix no records will be returnd.

Emails without prefix will be returned.

Peter