Find specific emails in Outlook from Access

[spikepl]

I need to be able to find one or more emails in one specific Outlook folder. Each email has some specific number in the subject. The number comes from an Access 2007 table.

The purpose is to be able to display the content of those emails.

I am not sure whether this is best done in Access or in Outlook.

In Access, I can link directly to the given folder, and search through the Subject. But this seems relatively slow with many (a few thousands) of mails. Also the mails are are HTML based, and displaying them in Access (in a RichText textbox) is not a great success.

So, perhaps I can search for the mails in Access, and somehow open the given mails in Outlook from Access for inspection? Although I am not sure how to identify a given mail.

Or , perhaps the entire search should somehow be instigated from Access but run inside Outlook? Not sure how, but presumably this is doable.

Please offer you opinions or experiences.

 

[darbid]

In Access, I can link directly to the given folder, and search through the Subject. But this seems relatively slow with many (a few thousands) of mails. Also the mails are are HTML based, and displaying them in Access (in a RichText textbox) is not a great success.

So, perhaps I can search for the mails in Access, and somehow open the given mails in Outlook from Access for inspection? Although I am not sure how to identify a given mail.

Or , perhaps the entire search should somehow be instigated from Access but run inside Outlook? Not sure how, but presumably this is doable.

Please offer you opinions or experiences.

How are you currently doing this? In my opinion it should not matter whether you use the Outlook object model from access or do the search within outlook vba. But I think using access would be better.

 

[spikepl]

Right now I am NOT doing it. The problem is that there are thousands of mails, and seaching for a specific one is very slow. With WIndows Search installed, which indexes the emails, to find an email inside Outlook manually no longer takes any time. But it is not obvious whether one can access WIndows Search -facilities, as integrated into Outlook, from inside Access

Ideally I should be able to directly find, in the Sent Mails folder, a mail sent from Access. The problem is I have difficulties identifying the mail. I am still looking into it, because Outlook assigns some MessageID to each mail, perhaps when it is sent or perhaps when it is created. So storing that ID in Access - if I could get hold of it - would obviate the need for searching. There is one more ID, but it is not easy to get hold of.

For the time being the process will remain manual until I have some time to look into it - unless someone has a good hint or two.

 

[darbid]

Windows search is I believe also available to VBA but I have never tried to use it.

Can you explain more about what you want to find. Is it a mail that you have sent, i.e. you have control over how it is written or made? If this is the case then there is a solution for this and it is not the ID. That number changes.

 

[spikepl]

WHen I speak of windows search I don't mean the standard windows file search but the Desktop Search that on eg WIndows XP can be downloaded separately and integrated heavily into eg Outlook 2007

I send a lot of mails from Access, and would like to create some reference in Access that would allow me to easily locate the sent mail in Outlook (no Exchange). So have you got a nice trick?

I'd also like to log certain received emails in Access. I can easily log standard stuff like time, header, sender etc. but then to go back from Access log into Outlook and actually locate that mail again would require a search, unless some unique ID trick can be found.

 

[darbid]

WHen I speak of windows search I don't mean the standard windows file search but the Desktop Search that on eg WIndows XP can be downloaded separately and integrated heavily into eg Outlook 2007

So did I. WDS version 4.0 seems to be able to be accessed, but am not sure whether you can narrow down your search to just Outlook folders.

http://social.msdn.microsoft.com/Fo...t/thread/3ba54dca-f454-4280-a85f-32b61c7f61b5

I send a lot of mails from Access, and would like to create some reference in Access that would allow me to easily locate the sent mail in Outlook (no Exchange). So have you got a nice trick?

Ok First I am still an Office 2003 man so I am not up with 2007 or 2010, but I think all I am going to say applies to 2007. The ID used in Outlook for many of its objects will change if you mave the object to another folder (by object I mean a mail item or calendar item or reminder item). Thus you should not get used to it. The best way to find things is to put your own unique ID into a field. This field is the Mileage field which the Outlook Mail Object has. Thus when you create your email just add your primary key ID for that record (or your own generated ID number) to your Mileage field of the email.

Now when you want to search for this email you use something like what i have written in this post, but just for email objects and not calendar objects
http://www.access-programmers.co.uk/forums/showpost.php?p=1000549&postcount=6

The main part you would have to change is this bit to how you get your folder that you want;

Set objFolder = objNS.GetSharedDefaultFolder(objRecip, olFolderCalendar)

I do not think you will get anything faster then this.

I'd also like to log certain received emails in Access. I can easily log standard stuff like time, header, sender etc. but then to go back from Access log into Outlook and actually locate that mail again would require a search, unless some unique ID trick can be found.

The same rules apply, You have some kind of code I assume which goes through the folder and gets each mail object and reads the properties and saves them into access. At that point when Access creates a new record, get the primary key ID number for the record and put it into the mail objects Mileage field.

Now you can find it.

 

[darbid]

By the way I have tried to use WDS but I am in an administered environment at work and have an old version of WDS. Something like this should work, but I would suggest you use Outlook first.

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

Dim sConString As String
Dim sSQLString As String

sConString = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
con.Open sConString

sSQLString = "SELECT Top 5 System.ItemPathDisplay FROM SYSTEMINDEX"

Set rst = con.Execute(sSQLString)

Do Until (rst.EOF)
   Debug.Print rst.Fields.Item("System.ItemPathDisplay").Value
   rst.MoveNext
Loop

rst.Close
Set rst = Nothing

con.Close
Set con = Nothing

I actually posted this code as a question in this forum (which you may or may not be able to see) http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_25043936.html

 

[spikepl]

Thanks darbid.

I will have to digest all this and that may take a while. I had the idea of abusing some existing field, but could not find anything suitable, so thanks for Mileage - it also exists in 2007. It probably still is slow to locate the specific mailitem in Outlook, since I guess I will have to search for it, that is unless I manage to access WS4. My initial hope was for some unique internal Outlook ID, that could be used to get at a mailitem directly.

Thanks for that WS4 link too. I had spent a few hours googling for WS4 from VB/VBA, but failed to find anything definitive, so that is a great post.

In my searches for some unique ID I had run across PR_INTERNET_MESSAGE_ID

http://www.gregthatcher.org/Scripts/VBA/Outlook/GetEmailInfoUsingPropertyTagSyntax.aspx

but temporarily gave up, as I had to get something up and running here and now.

 

[spikepl]

As to experts-exchange .. I had been thinking about signing up. Can you let me know what you think of that site?

 

[darbid]

As to experts-exchange .. I had been thinking about signing up. Can you let me know what you think of that site?

It is ok. I prefer here for access questions.

EE tries to do it all.

Don't pay to be a member, answer questions so that you get a free account. I answer questions about iOS now and not about Access.