Strange data with Automation of Outlook from Access

[Mr. B]

I have run into the strangest thing that I have ever seen in over 15 years of
VBA programming in Access.

I am using Access 2007 and Outlook 2007.

I am having code from Access start and instance of Outlook for the purpose
of iterating through all emails in a specific folder and read a delimited
string from the email. Each of several fields of data is then extracted from
that delimited string.

Where the problem comes in is when the following code is executed to read
the content from the body of the email.

strContent = OlMail.Body

When the content is viewed from Outlook is looks like this:
charles|hall|80 N Church
Street||SomeTown|MS|39437|601-555-5555||[email protected]|||Other|friend|
N|N

However, when I check the content of my "strContent" variable, which is
defined as a string variable, the content of the variable is:

charles|hall|80 N Church
Street||SomeTown|MS|39437|601-555-5555||[email protected]|||Other|fri
Street||SomeTown|MS|39437|601-555-5555||end|
N|N

Some of the data and the "|" delimiters have been repeated.

Does anyone have any idea why the value shown in the email body and the
value stored in the "strContent" variable as populated from the OlMail.Body
would be different?

If I forward the email from the Outlook 2007 computer to my personal email
that is using Outlook 2002, then the reverse happens. When the email arrives
I see the content as I described it when stored in the variable.

If I open the email with the bad data string and go to View/Encoding and
select a different encoding option, the data string is immediately change
from the bad string to the correct string, delimited as it should be

I know this sounds impossible but I swear this is exactly what has happened
in several cases. To make matters worse, sometimes the two values are the
same.

And, no, I have not been drinking. I can actully reproduce these occurances
and I would be happy to do a "gotomeeting" session with anyone who can help
me figure out what is going on.

Thanks for reading this very long explanation. Any help is appreciated.

Mr. B

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

I have run into the strangest thing that I have ever seen in over 15 years of
VBA programming in Access.

I am using Access 2007 and Outlook 2007.

I am having code from Access start and instance of Outlook for the purpose
of iterating through all emails in a specific folder and read a delimited
string from the email. Each of several fields of data is then extracted from
that delimited string.

Where the problem comes in is when the following code is executed to read
the content from the body of the email.

strContent = OlMail.Body

When the content is viewed from Outlook is looks like this:
charles|hall|80 N Church
Street||SomeTown|MS|39437|601-555-5555||[email protected]|||Other|friend|
N|N

However, when I check the content of my "strContent" variable, which is
defined as a string variable, the content of the variable is:

charles|hall|80 N Church
Street||SomeTown|MS|39437|601-555-5555||[email protected]|||Other|fri
Street||SomeTown|MS|39437|601-555-5555||end|
N|N

Some of the data and the "|" delimiters have been repeated.

Does anyone have any idea why the value shown in the email body and the
value stored in the "strContent" variable as populated from the OlMail.Body
would be different?

Have you tried linking into the Exchange Server?
I have used several processes this way which avoids the necessity if opening
the Outlook instance. Once you link to the Exchange server your emails are
all available with each field shown in it's distinct format in table form,
you may be able to visualize what is going on this way.

When you use File-Get External Data-Link choose the Exchange "file type".
This allows me to declare a record set on any of the available fields and
opens up new possibilities for data capture.

Hope this helps - Good Luck.

 

[Mr B]

Jeff C,

Thanks so much for the response.

I have never tried linking to the exchange server. Would mind to elaborate
a little more on how you "declare a record set on any of the available fields
".

Am I to assume that you establish the link the same way you establish a link
to other tables and then use and sql statement to extract the data?

Thanks for your input.

Mr B

 

[Jeff C]

Jeff C,

Thanks so much for the response.

I have never tried linking to the exchange server. Would mind to elaborate
a little more on how you "declare a record set on any of the available fields

I was thinking of a DAO.Recordeset and looping though fields

Am I to assume that you establish the link the same way you establish a link
to other tables and then use and sql statement to extract the data?

Yes, just follow the File-Link-External Data- and choose file type Exchange
and you will be directed to the Exchange server. There is quite a bit about
that in OutlookCode.com

Once you link to to the Exchange Table you have a multitude of Outlook data
available and you can schedule real time data pulls with your email.
Isolating the fields you want you can do string searches etc to extract text.

 

[GeoffG]

The email address, (e-mail address removed), is turning part of the string
(the part that's highlighted in blue) into a hyperlink.

A hyperlink is special: it contains display text and the link
(the email or web address). In your case, some of the text is
repeated because it is being used for the display text and the
link.

(You can right-click a hyperlink in an Outlook email and, from
the right-click menu, select Edit, Select, Open, Copy or Remove
Hyperlink. See Hyperlink in Outlook help for more
information.)

Using your example in my version of Outlook, strContent contains:

charles|hall|80 N Church HYPERLINK
"mailto:Street%7C%7CSomeTown%7CMS%7C39437%7C601-555-5555%7C%[email protected]%7C%7C%7COther%7Cfriend%7CN%7CN"Street||SomeTown|MS|39437|601-555-5555||[email protected]|||Other|friend|N|N

Notice the unwanted text (the hyperlink) begins with the word
"HYPERLINK" and ends with the first double-quotation mark (")
from the END of the string. The text that needs to be retained
is the display text, which follows the second quotation mark.

If your delimited strings are similar to the above example, and,
if your strings can never contain the text "HYPERLINK", nor
double quotation marks, then you could remove the unwanted text
using VBA as follows:

Dim lngPos1 as Long
Dim lngPos2 as Long

strContent = mobjMSG.Body
lngPos1 = InStr(strContent, "HYPERLINK")
lngPos2 = InStrRev(strContent, """")
strContent2 = Left(strContent, lngPos1 - 1) _
& Mid(strContent, lngPos2 + 1)

Regards
Geoff

PS - I think this newsgroup is supposed to be for questions about
programming DAO using VBA.