Please help me Parse this

[sjones]

I have a email that comes from my supplier.

The email looks like this:

C U S T O M E R R E C E I P T

Supplier Order Number: 175382
America's Premier Value Merchant Order Date: 4/4/2004
1384 Commercial Ship Via: FedEx
Huntington, IN 46759 Payment: MC
800-348-1239
Fax: 418-326-1806



Bill To: Ship To:
Steve Jones Dusty Riley
5008 South Powell Ave 5421 NE 50th Street

Blue Springs, MO 64015 Kansas City, MO 64119
Telephone: (816)229-7386
E-Mail: (e-mail address removed)


How can I extract the Order number, Order Date, Ship to Name and Address.


Steve J

 

[Milly Staples [MVP - Outlook]]

You might want to post this to an Outlook programming group as it will
require some code to extract the information you wish.

--
Milly Staples [MVP - Outlook]

Post all replies to the group to keep the discussion intact. Due to
the (insert latest virus name here) virus, all mail sent to my personal
account will be deleted without reading.

After searching google.groups.com and finding no answer, sjones asked:

| I have a email that comes from my supplier.
|
| The email looks like this:
|
| C U S T O M E R R E C E I P T
|
| Supplier Order Number: 175382
| America's Premier Value Merchant Order Date: 4/4/2004
| 1384 Commercial Ship Via:
| FedEx Huntington, IN 46759 Payment: MC
| 800-348-1239
| Fax: 418-326-1806
|
|
|
| Bill To: Ship To:
| Steve Jones Dusty Riley
| 5008 South Powell Ave 5421 NE 50th Street
|
| Blue Springs, MO 64015 Kansas City, MO 64119
| Telephone: (816)229-7386
| E-Mail: (e-mail address removed)
|
|
| How can I extract the Order number, Order Date, Ship to Name and
| Address.
|
|
| Steve J

 

[Rob van Gelder]

Regular Expressions does a very good job of this.

Although I'm so-so at Regular Expressions, I'm not familiar with the
VBScript Regular Expressions library, so I couldn't give you examples.


Here's an approach using Instr matching - it's pretty rough but should do
the job.

Assumes your billing and shipping address lines are separated by at least 3
spaces.
Also assumes that there are no further sections after address that are
separated by two columns using 3 spaces.

Sub test()
Dim str As String, i As Long
Dim strOrderNumber As String, strOrderDate As String
Dim strShipToName As String, strShipToAddress As String

str = _
" C U S T O M E R R E C E I P T" & vbNewLine & _
vbNewLine & _
"Supplier Order Number: 175382" &
vbNewLine & _
"America's Premier Value Merchant Order Date:
4/4/2004" & vbNewLine & _
"1384 Commercial Ship Via:
FedEx" & vbNewLine & _
"Huntington, IN 46759 Payment: MC" &
vbNewLine & _
"800-348-1239" & vbNewLine & _
"Fax: 418-326-1806" & vbNewLine & _
vbNewLine & _
vbNewLine & _
vbNewLine & _
" Bill To: Ship To:" &
vbNewLine & _
"Steve Jones Dusty Riley" &
vbNewLine & _
"5008 South Powell Ave 5421 NE 50th Street" &
vbNewLine & _
vbNewLine & _
"Blue Springs, MO 64015 Kansas City, MO 64119" &
vbNewLine & _
"Telephone: (816)229-7386" & vbNewLine & _
"E-Mail: dafella<at>swbell.net" & vbNewLine

On Error Resume Next
i = InStr(1, str, "Order Number: ") + Len("Order Number: ")
strOrderNumber = Mid(str, i, InStr(i, str, vbCr) - i)

i = InStr(1, str, "Order Date: ") + Len("Order Date: ")
strOrderDate = Mid(str, i, InStr(i, str, vbCr) - i)

i = InStr(1, str, "Ship To:")
i = InStr(i, str, " ")
strShipToName = Trim(Mid(str, i, InStr(i, str, vbCr) - i))
i = InStr(i, str, vbCr)
i = InStr(i, str, " ")
strShipToAddress = Trim(Mid(str, i, InStr(i, str, vbCr) - i))
i = InStr(i, str, vbCr)
i = InStr(i, str, " ")
strShipToAddress = strShipToAddress & vbNewLine & Trim(Mid(str, i,
InStr(i, str, vbCr) - i))
i = InStr(i, str, vbCr)
i = InStr(i, str, " ")
strShipToAddress = strShipToAddress & vbNewLine & Trim(Mid(str, i,
InStr(i, str, vbCr) - i))
End Sub