Extract String from memo field

[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

 

[John W. Vinson]

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.

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.

 

[Jado]

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)

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

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.

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.

 

[John W. Vinson]

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)

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?

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!