Extract String from memo field

  • Thread starter Thread starter Jado
  • Start date Start date
J

Jado

Hi,

I'd like to automate the extraction of information from an email body in
outlook 2000 into an Access 97 db.

This is what the email bodies look like when I receive them...


-------------------------------------------------------------
* Name: Mr Test

* Service: Full Service

* Part Code: kd89475

--------------------------------------------------------------

I've got some code to extract the email body into a single memo field in my
Access 97 db.

I've created 3 blank fields in the same table which are [Name], [Service]
and [Part Code]

Could anyone help me with extracting the data from the email into the
relevant fields.



Thanks



Jado
 
Hi,

I'd like to automate the extraction of information from an email body in
outlook 2000 into an Access 97 db.

This is what the email bodies look like when I receive them...


-------------------------------------------------------------
* Name: Mr Test

* Service: Full Service

* Part Code: kd89475

--------------------------------------------------------------

I've got some code to extract the email body into a single memo field in my
Access 97 db.

I've created 3 blank fields in the same table which are [Name], [Service]
and [Part Code]

Could anyone help me with extracting the data from the email into the
relevant fields.
Click to expand...

Well... no guarantees, but try:

TheName: Mid([memofield], InStr([memofield], "Name: ") + 1,
InStr(InStr([memofield], "Name: "), [memofield], Chr(13) & Chr(10)) - 2)

and similar for the other two fields substituting Service: for Name: etc.
 
Thanks John,
but not quite working yet!

I've changed your code slightly to exclude the 'Name: ' from the results,
but it's still including everything below where it start from. i.e.

TheName: Mid([memofield], InStr([memofield], "Name: ") + 6,
InStr(InStr([memofield], "Name: "), [memofield], Chr(13) & Chr(10)) - 2)
Click to expand...

results in String i want to extract and everything after that too!

how do i just get the string i want and exclude everything below it
including the return line feed at end of line?

Thanks

Jado



John W. Vinson said:
Hi,

I'd like to automate the extraction of information from an email body in
outlook 2000 into an Access 97 db.

This is what the email bodies look like when I receive them...


-------------------------------------------------------------
* Name: Mr Test

* Service: Full Service

* Part Code: kd89475

--------------------------------------------------------------

I've got some code to extract the email body into a single memo field in my
Access 97 db.

I've created 3 blank fields in the same table which are [Name], [Service]
and [Part Code]

Could anyone help me with extracting the data from the email into the
relevant fields.
Click to expand...

Well... no guarantees, but try:

TheName: Mid([memofield], InStr([memofield], "Name: ") + 1,
InStr(InStr([memofield], "Name: "), [memofield], Chr(13) & Chr(10)) - 2)

and similar for the other two fields substituting Service: for Name: etc.
Click to expand...
 
Thanks John,
but not quite working yet!

I've changed your code slightly to exclude the 'Name: ' from the results,
but it's still including everything below where it start from. i.e.

TheName: Mid([memofield], InStr([memofield], "Name: ") + 6,
InStr(InStr([memofield], "Name: "), [memofield], Chr(13) & Chr(10)) - 2)
Click to expand...

results in String i want to extract and everything after that too!

how do i just get the string i want and exclude everything below it
including the return line feed at end of line?
Click to expand...

Sorry! You'll need to open the VBA editor and look at the online help for
Mid() and Instr(), and play around with the arguments. I'm tied up today and
don't have time to dig through the nested parentheses!
 
Back
Top