By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Search within a file

P: n/a
Scenario:

I have a database with user account information, including their
status. Due to different circumstances, we disable these user
accounts. First, we send them a warning e-mail and then a suspension
notification e-mail, through Outlook. All these .msg's (Outlook
E-mails) are saved in the same place. All the messages have the same
text, except the username.

Question:

My question is... Is it possible, within MS Access, to search WITHIN a
file for keywords (in this case the username). The desired outcome
would be to have this script locate the .msg based on the "Username"
field on the form.

Any help would be greatly appriciated!

Thank you!

Jan 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Ted
What kind of a file would you be searching through?
Access can read the body of an email (or even just the subject) and
extract the data it needs from it to perform the actions you talk
about.
You can also read in text files, Word documents, etc., so the answer to
you question would depend on which format the data is coming to you,
then we can determine what to do with it.
Ted

Jan 24 '06 #2

P: n/a
Ted,

I would like to read the body of an Outlook E-mail.

Something like this (though I am not sure how well this will work)

USERNAME: MIKE SMITH
SUSPENSION DATE: 12-23-05
view

When the user clicks on "view", it will open the sent e-mail.

Jan 24 '06 #3

P: n/a
Ted
Here is what I do when I want Access to read emails, extract the data,
and then perform tasks based on what I find in that data:
Import the selected emails (based on the subject line or you can select
the email based on the sender) into a database table.
I use a memo field to hold the body of the email.
Even though you cannot see them each line in the body ends with the
Chr(13) for Carriage Return and/or Chr(10) for Line Feed.
I check the length of the memo field and assigned it to a variable
field.
I then use the variables to determine the start and end positions of
each new line.
I check the line using the InStr function to determine if a particular
key word or phrase is in that line, such as "USERNAME:"
If it exists, then I take the first position of the term USERNAME and
store the value in a variable.
I then run a loop to look for the end of that line by finding the next
Chr(13) and storing that position number in a variable.
I then use the Mid string function to extract the data from that line.
Here is an example:
Assuming the Chr(13) is at position 80 and the memo field is called
txtBody:
i = 1

If InStr(i, txtBody, "USERNAME:") > 0 Then
intStart = InStr(1, txtBody, "USERNAME:") 'determines what
position USERNAME: starts at
x = intStart
While Not Mid(txtBody, x, 1)
x = x + 1
Wend
intEnd = x
strValue = Mid(txtBody, intStart + 10, intEnd)
End If
i = intEnd + 1

I keep track of the last position and the new starting position using
the i variable and copy the previous section of code and modify it to
look for the next key word I need. I also increment the size of the key
word to match the length of the key word in the line strValue =
Mid(txtBody, intStart + 10, intEnd).

I hope this makes sense. If not I will try to be more specific if you
email me one of the emails you use.

Ted

Jan 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.