Covert Outlook Email Body to Excel Columns

First a couple of warnings ...
1. We don't normally just produce code unless the OP has tried something for themselves
2. You're lucky I got intrigued by this, but I haven't made it particularly tidy - there are better ways of doing some bit and you will have to sort out the different address formats for yourself.
3. I wouldn't have done this in VBA - but I've assumed that's the requirement. This solution will port across to VB.NET without too many issues.

How I approached this...

Quote:

the body of the email is contained within one cell

- OK, that means I can just treat it as a string and split it across the lines.
Then I hit my first problem - the same one you did

Quote:

but there are no clear line breaks that I'm able to utilize

So I utilised the immediate window in Excel VBA and typed

? asc(mid$(s, 46,1))

which gave me the answer 10. That means I can treat each cell as a string and split it using chr$(10) to give me the body of the text as an array of strings.

Dim s As String
s = Range("A1").Text

Dim lines() As String
lines = Split(s, Chr$(10))

Then I took what I knew about the text based on observation. I knew I was going to be interested in specific words or phrases, so I put the text into some constants ...

Const order_no = "Order Number : "
Const bill_to = "Bill To:"
Const shipping = "Shipping: Price+:"
Const salestax = "Sales Tax:"
Const total = "Total:"  'NB must have the colon on the end to diffentiate from cpTotal below
Const headers = "Quantity     Price/Ea."    'This identifies the start of the order list
'The text that indicates the individual components within each order
Const cpName = "Name"
Const cpQuant = "Quantity"
Const cpPrice = "Price/Ea."
Const cpTotal = "Total"

Also from observation I noticed that I was going to need to know not just an overview of what the line contained, but also in which position (column) certain things could be found, so

'the column position for each of the components
Dim bill_to_pos As Integer
Dim pCode As Integer
Dim pName As Integer
Dim pQuant As Integer
Dim pPrice As Integer
Dim pTotal As Integer

Then it was a simple case of moving through the array, determining what, if anything, I could get out of each line, and extracting the data I needed using string functions.

Here's the entire function ... not elegant but it works

Option Explicit
Sub Test()

    Const order_no = "Order Number : "
    Const bill_to = "Bill To:"
    Const shipping = "Shipping: Price+:"
    Const salestax = "Sales Tax:"
    Const total = "Total:"  'NB must have the colon on the end to diffentiate from cpTotal below
    Const headers = "Quantity     Price/Ea."    'This identifies the start of the order list
    'The text that indicates the individual components within each order
    Const cpName = "Name"
    Const cpQuant = "Quantity"
    Const cpPrice = "Price/Ea."
    Const cpTotal = "Total"

    'the column position for each of the components
    Dim bill_to_pos As Integer
    Dim pCode As Integer
    Dim pName As Integer
    Dim pQuant As Integer
    Dim pPrice As Integer
    Dim pTotal As Integer


    Dim order_number As String
    Dim ship_to_name As String
    Dim bill_to_name As String
    Dim ship_to_email As String
    Dim bill_to_email As String
    Dim ship_to_phone As String
    Dim bill_to_phone As String

    Dim ship_to_addr1 As String
    Dim bill_to_addr1 As String
    Dim ship_to_addr2 As String
    Dim bill_to_addr2 As String
    Dim ship_to_addr3 As String
    Dim bill_to_addr3 As String
    Dim shipcost As String
    Dim taxcost As String
    Dim totalcost As String

    'These will hold details for the order items
    Const MaxNoItems = 5    'Set this to whatever you want
    Dim bInList As Boolean
    Dim Codes(MaxNoItems) As String
    Dim ItemNames(MaxNoItems) As String
    Dim Quants(MaxNoItems) As String
    Dim Prices(MaxNoItems) As String
    Dim Totals(MaxNoItems) As String
    Dim NoOfItems As Integer

    Dim s As String
    s = Range("A1").Text

    Dim lines() As String
    lines = Split(s, Chr$(10))

    bInList = False 'This will be set to true if we are in the list of items
    NoOfItems = 0   'This is a counter of the number of items found in the order list

    Dim i As Integer
    Dim p As Integer
    For i = 0 To UBound(lines)

        'Get the order number
        p = InStr(lines(i), order_no)
        If p > 0 Then
            order_number = Mid$(lines(i), p + Len(order_no))
        End If

        'Get the position where bill to address starts
        p = InStr(lines(i), bill_to)
        If p > 0 Then
            bill_to_pos = p
            'Assume Names are on the next line
            i = i + 1   'Note be careful as we are amending the loop index
            ship_to_name = Mid$(lines(i), 1, bill_to_pos - 1)
            bill_to_name = Mid$(lines(i), bill_to_pos)
            i = i + 1
            ship_to_email = Mid$(lines(i), 1, bill_to_pos - 1)
            bill_to_email = Mid$(lines(i), bill_to_pos)
            i = i + 1
            ship_to_phone = Mid$(lines(i), 1, bill_to_pos - 1)
            bill_to_phone = Mid$(lines(i), bill_to_pos)
            i = i + 3   'NB skipping blank lines
            ship_to_addr1 = Mid$(lines(i), 1, bill_to_pos - 1)
            bill_to_addr1 = Mid$(lines(i), bill_to_pos)
            i = i + 1
            ship_to_addr2 = Mid$(lines(i), 1, bill_to_pos - 1)
            bill_to_addr2 = Mid$(lines(i), bill_to_pos)
            i = i + 1
            ship_to_addr3 = Mid$(lines(i), 1, bill_to_pos - 1)
            bill_to_addr3 = Mid$(lines(i), bill_to_pos)
        End If

        p = InStr(lines(i), headers)
        If p > 0 Then
            'Work out where on the line each of the components will start/end
            pCode = 1
            pName = InStr(lines(i), cpName)
            pQuant = InStr(lines(i), cpQuant)
            pPrice = InStr(lines(i), cpPrice)
            pTotal = pPrice + Len(cpPrice)  ' NOT InStr(lines(i), cpTotal)
            bInList = True
            i = i + 1   'Skip over this line so that we don't try to process it again below
        End If

        'NB handling the shipping and total bit before I handle the individual items
        'This is because if we come across "Shipping: Price+:" we know there are no
        'more items to deal with and we can set bInList back to False
        p = InStr(lines(i), shipping)
        If p > 0 Then
            shipcost = Mid$(lines(i), p + Len(shipping))
            bInList = False
        End If

        p = InStr(lines(i), salestax)
        If p > 0 Then
            taxcost = Mid$(lines(i), p + Len(salestax))
        End If

        p = InStr(lines(i), total)
        If p > 0 Then
            totalcost = Mid$(lines(i), p + Len(total))
        End If

        'This bit handles the individual items
        If bInList Then
            'ignore blank lines and separators
            If Trim$(lines(i)) <> "" And Left$(lines(i), 3) <> "---" Then
                NoOfItems = NoOfItems + 1
                Codes(NoOfItems) = Mid$(lines(i), pCode, pName - 1)
                ItemNames(NoOfItems) = Mid$(lines(i), pName, pQuant - pName - 1)
                Quants(NoOfItems) = Mid$(lines(i), pQuant, pPrice - pQuant - 1)
                Prices(NoOfItems) = Mid$(lines(i), pPrice, pTotal - pPrice - 1)
                Totals(NoOfItems) = Mid$(lines(i), pTotal)
            End If
        End If
    Next

    Debug.Print "Order Number  : " + order_number
    Debug.Print ""
    Debug.Print "Ship to Name  : " + ship_to_name
    Debug.Print "Ship to email : " + ship_to_email
    Debug.Print "Ship to Phone : " + ship_to_phone
    Debug.Print "Ship to Addr1 : " + ship_to_addr1
    Debug.Print "Ship to Addr2 : " + ship_to_addr2
    Debug.Print "Ship to Addr3 : " + ship_to_addr3
    Debug.Print ""
    Debug.Print "Bill To Name  : " + bill_to_name
    Debug.Print "Bill to email : " + bill_to_email
    Debug.Print "Bill to Phone : " + bill_to_phone
    Debug.Print "Bill to Addr1 : " + bill_to_addr1
    Debug.Print "Bill to Addr2 : " + bill_to_addr2
    Debug.Print "Bill to Addr3 : " + bill_to_addr3
    Debug.Print ""
    Debug.Print "Shipping Cost : " + shipcost
    Debug.Print "Sales Tax     : " + taxcost
    Debug.Print "Total Cost    : " + totalcost
    Debug.Print ""
    'These will hold details for the order items
    Debug.Print "Number of items : " + CStr(NoOfItems)
    For i = 1 To NoOfItems
        Debug.Print "-- Code  : " + Codes(NoOfItems)
        Debug.Print "-- Name  : " + ItemNames(NoOfItems)
        Debug.Print "-- Price : " + Prices(NoOfItems)
        Debug.Print "-- Total : " + Totals(NoOfItems)
    Next


End Sub

And here is the output from all those Debug.Print statements at the end (Note I changed some of the numbers to prove I was picking up the correct text)

Order Number  : 5

Ship to Name  : FIRSTNAME LASTNAME
Ship to email : email@domain.com
Ship to Phone : 5559991111
Ship to Addr1 : 123 Address
Ship to Addr2 : City ST 55555
Ship to Addr3 : US

Bill To Name  : FIRSTNAME LASTNAME
Bill to email : email@domain.com
Bill to Phone : 5559991111
Bill to Addr1 : 123 Address
Bill to Addr2 : Portland OR 97210
Bill to Addr3 : US

Shipping Cost :          $6.95
Sales Tax     :          $0.00
Total Cost    :         $36.90

Number of items : 1
-- Code  : V-BotAAS2
-- Name  : Item Name
-- Price :    $29.9
-- Total :         $59.90

It's down to you to convert those strings into something useful.
Also watch out because you can't have macros in CSV files, you may have to convert to XLS or have the spreadsheet import the CSV via another macro