Listbox populated from outlook sent items (1 Viewer)

[jamie57]

I am trying to populate a listbox with data from an outlook sent items folder, the code is as follows:

Dim olApp As Outlook.Application
Dim nsNameSpace As Outlook.NameSpace
Dim fldFolder As Outlook.MAPIFolder
Dim objMessages As Object
Dim strData As String

Set olApp = New Outlook.Application
Set nsNameSpace = olApp.GetNamespace("MAPI")

Set fldFolder = nsNameSpace.GetDefaultFolder(olFolderSentMail)

Set objMessages = fldFolder.Items


For Each objMessage In objMessages

strData = strData & "'" & objMessage.Recipients & "',"

Next objMessage

List6.RowSource = strData

I thought this should list all the recipients email addresses, but it returns an error. If I replace the Recipients with Subject, it works fine, so I am guessing I am using a wrong property. I have searched everywhere for a list of the properties for this object but I can't seem to find anything anywhere that makes sense. I would also like to do this similar thing with the olFolderInbox and have all sender email addresses populate a listbox...

 

[Drevlin]

In your declaration section change

Dim objMessages As Object

to:

Dim objMessages As Outlook.Items

And add:

Dim objMessage As Outlook.MailItem

Then when you type in "objMessage." it will give you a list of all available properties. In this particular case I believe you want "objMessage.To".

You may also want to catch the .CC and .BCC if you want all recipients.

Anyway, hope this works for ya.

Peace

 

[jamie57]

Thank You! That looks like it will work, but when it gets to the Next objMessage, its now giving a "Type Mismatch Error". So I am only getting the first message... Any ideas?

 

[Drevlin]

Put a break on the line that is giving you the error, I'm assuming its the:

strData = strData & "'" & objMessage.To & "',"

Hold the mouse over the objMessage.To (or whatever you have on that line) and see what the value is when it errors out. It could be trying to pass a Null value...

I've tested this code on my machine and it seems to be working appropriately.

 

[jamie57]

Actually, I just noticed its not just getting the first message, its getting a bunch, and then its giving the mismatch error... and the error is at the Next objMessage. I did the mouse over to check the objMessage.To, and it shows as object variable or with not set, so maybe I will have to use a different loop instead of the for each or do you think there is a different problem?

 

[Drevlin]

Can you tell what Message it's having problems with? You could try changing the .To to .Subject and seeing if it errors out there. I'm thinking it's having a problem with the Object it's pulling, like perhaps its not an actual email message and thus doesn't have a .To item to read, which could be giving you your error.

If this is the case you could just add an:

On Error Resume Next

And it should cause it to skip over that particular item.

 

[jamie57]

Yeah I changed to Subject and its the same problem. So I put the On Error Resume Next and it all seems to work fine. Thanks!

On another note, is there some kind of unique key for each message? So once the list box it populated we can add an onclick event to do a objMessage.Display based on that key?

We are also going to be using the Sendobject to send email, is it possible to somehow retreive the unique key (if there is one) after the message is sent, so we can store it in access?

 

[Drevlin]

I don't know all aspects of e-mail setup but there is an EntryID but it's a very long string of numbers and using it would be a waste of memory. If it were me I'd use ReceivedOn to get (what should be) a unique Time stamp for the message. If you really wanted to make sure you could use both ReceivedOn and SenderName.

I don't know exactly what your setup is so there may be a reason to use something different then the time stamp and senders name but it should cover most conditions.

 

[jamie57]

Yes, Thank you, the date stamp will probably work fine, I've tried this code on my dblclick for the listbox with no success:

Dim olApp As Outlook.Application
Dim nsNameSpace As Outlook.NameSpace
Dim fldFolder As Outlook.MAPIFolder
Dim objMessages As Outlook.Items
Dim objMessage As Outlook.MailItem
Dim strData As String
Dim intCnt As Integer

On Error Resume Next

Set olApp = New Outlook.Application
Set nsNameSpace = olApp.GetNamespace("MAPI")

Set fldFolder = nsNameSpace.GetDefaultFolder(olFolderSentMail)

Set objMessages = fldFolder.Items.Find("[SentOn] = '" & Me!List14 & "'")

For Each objMessage In objMessages
objMessage.Display
Next objMessage


I tried to use Restrict instead of Find with the same results. Both ways, I guess NO results. I know I could remove that and put an If in the For loop, but that would be a little slow.

 

[Drevlin]

I think you pretty much have to use an If statement for this particular case. And I can't see how it would slow you down enough to matter.
The Following works nicely:

Dim olApp As Outlook.Application
Dim nsNameSpace As Outlook.NameSpace
Dim fldFolder As Outlook.MAPIFolder
Dim objMessages As Outlook.Items
Dim objMessage As Outlook.MailItem
Dim strData As String
Dim intCnt As Integer

On Error Resume Next

strData = List14
Set olApp = New Outlook.Application
Set nsNameSpace = olApp.GetNamespace("MAPI")

Set fldFolder = nsNameSpace.GetDefaultFolder(olFolderInbox)
Set myExplorer = fldFolder.GetExplorer
Set objMessages = fldFolder.Items

For Each objMessage In objMessages
If Str(objMessage.ReceivedTime) = strData Then
objMessage.Display
End
End If
Next objMessage

Peace

 

[jamie57]

Thanks, yeah I guess we will have to use the If statement.. I just tried it with an inbox with 1000 messages and it took about 45 seconds... Oh well, I guess we will have to deal with it.

On the other note, I found out why I was getting the error before. There was an appointment that wasn't yet accepted or declined in the inbox. I now am trapping that error to get past it.

Thanks for all your help!

 

[jamie57]

I forgot to ask why can I not use the .Find or .Restrict in this case? Does it not allow these fields? What does it allow? Thanks.

 

[Drevlin]

Ok, because you asked so nicely. I went in and tried to find out why .Restrict wouldn't work. It actually does work, but it's picky. It seems that the string of the Date stamp that is returned from ReceivedTime doesn't want to match up to what is held inside of ReceivedTime, I have an idea why, but don't want to try to explain here. Anyway, I've written code to pull the items by using .Restrict. I assume it will speed up your process by first shrinking the amount of items that are needed to be searched. Give it a try and let me know how it works.

Public Sub GetItem()
Dim olApp As Outlook.Application
Dim nsNameSpace As Outlook.NameSpace
Dim objMessages As Outlook.Items
Dim strRestrict As String 'holds the Filter
Dim lngSpace As Long 'to hold where the first space is.
Dim dtDate As Date 'to hold the date of the message your looking for

Set olApp = New Outlook.Application
Set nsNameSpace = olApp.GetNamespace("MAPI")
Set objMessages = nsNameSpace.GetDefaultFolder(olFolderInbox).Items
'Get Date string from TextBox then get location of first space
'to seperate the date
List14 = "9/27/2002 5:26:32 AM"
lngSpace = InStr(1, List14, " ") - 1
dtDate = Left(List14, lngSpace)
'Couldn't get "= dtDate" to work. So had to improvise =(
strRestrict = "[ReceivedTime] > '" & dtDate - 1 & "' And [ReceivedTime] < '" & dtDate + 1 & "'"
Set myRestrictItems = objMessages.Restrict(strRestrict)
For Each MyItem In myRestrictItems
strRec$ = Str(MyItem.ReceivedTime)
If strRec$ = text14 Then
MyItem.Display
End
End If
Next MyItem
End Sub

I personally feel it's kind of ugly... but it works and you can clean it up to your liking. Let me know if it's actually an improvement.

Peace

 

[jamie57]

Thank You! Yeah, that works good. Now, another question... I am going to be doing this for both the Inbox and Sent Items, which I can get working. But, we have changed how we are going to do this now, instead of using the time/date stamp, we are just going to Restrict by an email address. Now, the problem I have noticed is that in the sentmail, the To field shows the Name and not the email address sometimes (usually on a Reply), and the inbox SenderName always shows the name as well and not the email address. Is there some way to do this or is there another field I am missing?