Outlook and access

[rgoth]

Hi what I am trying to do is connect to Access from Outlook. I have tried several ways this way gets me part way there

' Initialize string to database path.
Const strConPathToSamples = "C:\Users\Doc.DENTAL\Documents\"

strDB = strConPathToSamples & "My New App.accdb"
' Create new instance of Microsoft Access.


Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
appAccess.DoCmd.RunSQL "UPDATE tblTest " _
& "SET Name = 'John' " _
& "WHERE ConfirmationCode= 2;"

I can run an update sql but I would also like to run a query based on information with in the subject

i.e. if the subject is xyz

I would like to be able to run a query that would look something like this

Dim rs As RecordSetSet rs = Currentdb.OpenRecordSet("SELECT *, ID FROM MyAccessTable where ConfirmationCode="SUBJECT LINE" ;")
Do While Not rs.EOF
'do some things
rs.MoveNext
Loop

[rgoth]

Further to above I am executing code using rules which will run based on a set of criteria when a new email arrives.

[June7]

Use apostrophes (or double the quotes) for text delimiter:
Dim rs As RecordSetSet rs = Currentdb.OpenRecordSet("SELECT *, ID FROM MyAccessTable where ConfirmationCode='SUBJECT LINE';")

You want to extract a code from subject text of email message? This means reading each email message until you find correct one. How will code know which email is the one? What is the key to search on - FROM address, a word/phrase in subject, a word/phrase in body?

[rgoth]

I am basically looking at how to run a query based on a subject line from a new email. I am sending people appointment reminders via email. when they click on a link in the email a message is sent back to my account with a confirmation code in the subject line. I have a rule set up in outlook that when a new message comes in from a specific email address with the phrase "Confirm Appointment" in the body it will run code in VBA that I would like to update records in a table. Before doing so I need to verify the confirmation code first by looking it up. I can update a table with the following code

appAccess.DoCmd.RunSQL "UPDATE tblTest " _
& "SET apt_status = 2 " _
& "WHERE ConfirmationCode= 22343;"

but I can query the database to confirm the confirmation code is legit first.

If that makes since

[June7]

You already have code that can read the email body for the phrase?

Post that code.

Use If Then statement to conditionally run update based on result of the phrase search.

Then how should code pull the confirmation code from the subject line? Where will it be found within the subject text? Post example.