Find "carriage return" in a mail.body

Question

I have mails like this :

Hello,

Please note we did ... at 16h15

Actions done: Rebuilding etc

sincerely

Mr.

The actions change in every mail and what I want is to insert the action in my Excel. The problem is that I don't know how to get the "carriage return" (idk if this is the right name, this is what traduction gave me). What I find in internet is that vbLfChr(10) is the "carriage return". What I tried is to find the beginning :

TechnicPosition= InStr(1, .Body, "Actions done: ")
TechnicAction= Mid(.Body, TechnicPosition, 14) ' first char from the action

But I can't get the last char (first "carriage return" from TechnicAction). I tried many things like : InStr(TechnicPosition, .Body, vbCrLf)

My question : how to get a sentence that begin from a word to a "carriage return" (the first that comes after the beginning word) ?

`vbLf` or `Chr(10)` = Line Feed, `vbCr` or `Chr(13)` = Carriage Return, `vbCrLf` = Carriage Return & Line Feed — SierraOscar, Jul 30 '15 at 08:03
I tried them all before I asked. They all return all the end of the mail body — Slingr, Jul 30 '15 at 08:05
Try the macro in this answer of mine: [How to copy Outlook mail message into excel using VBA or Macros](http://stackoverflow.com/a/12146315/973283). It should give you all the detail you need. — Tony Dallimore, Jul 30 '15 at 08:14

score 3 · Accepted Answer · edited May 23 '17 at 12:06

3

The carriage return in the email body is usually vbNewline

This is how I usually do it

Sub Sample()
    Dim sBody As String
    Dim MyAr
    Dim i As Long

    '
    '~~> Rest of your code
    '

    sBody = oMail.Body

    '~~> For testing purpose
    'sBody = "Hello," & vbNewLine & vbNewLine
    'sBody = sBody & "Please note we did ... at 16h15" & vbNewLine & vbNewLine
    'sBody = sBody & "Actions done: Rebuilding etc" & vbNewLine & vbNewLine
    'sBody = sBody & "Sincerely"

    '~~> Split the email body on vbnewline and store it in array
    MyAr = Split(sBody, vbNewLine)

    '~~> Loop through array
    For i = LBound(MyAr) To UBound(MyAr)
        '~~> Check if the line has "Actions done:"
        If InStr(1, MyAr(i), "Actions done:") Then
            '~~> This would give you "Rebuilding etc"
            '~~> Split the line on "Actions done:"
            '~~> You will get an array. Ar(0) will have "Actions done:"
            '~~> And Ar(1) will have what you are looking for so we use
            '~~> Split()(1) directly to access that item
            MsgBox Split(MyAr(i), "Actions done:")(1)
            Exit For
        End If
    Next i
End Sub

Edit

ANOTHER WAY

edited May 23 '17 at 12:06

Community

1
1

answered Jul 30 '15 at 08:25

Siddharth Rout

147,039
17
206
250

I don't understand what you've done here, but it works perfectly. Thank you very much :D – Slingr Jul 30 '15 at 08:30
I have added the explanation :) Still if you have a confusion, simply ask :) – Siddharth Rout Jul 30 '15 at 08:35
I don't understand why there is a `(1)` at the end of the `Split`. PLus, is this important to set all the `set` to `nothing` at the end ? – Slingr Jul 30 '15 at 08:38
You may have to refresh the page. I already explained what `Split()(1)` does :) – Siddharth Rout Jul 30 '15 at 08:40
Thank you again, you have no idea how you helped me. It's quite frustrating not to find something that simple – Slingr Jul 30 '15 at 08:40
2

While it is usually not necessary to use `Set obj = Nothing` in VBA but I always like to flush after I use the toilet ;) – Siddharth Rout Jul 30 '15 at 08:40
1

I now understand your whole answer but i'd never think about this solution. Thank you again (and i'll flush ;)) – Slingr Jul 30 '15 at 08:43

score 0 · Answer 2 · answered Jul 30 '15 at 07:53

0

Try this:

TechnicPosition = InStr(1, .Body, "Actions done: ")
TechnicEndPosition = InStr(TechnicPosition, .Body, Chr(10))
TechnicAction = Mid(.Body, TechnicPosition + 14, TechnicEndPosition - TechnicPosition - 14)

answered Jul 30 '15 at 07:53

mielk

3,890
12
19

It still doesn't find the "carriage return" – Slingr Jul 30 '15 at 08:02
It there a way to see all char in a mail (to see how the carriage return is coded) – Slingr Jul 30 '15 at 08:03
Add this to your code: `For i = 1 To Len(.Body): Debug.Print "[" & i & "]: " & Mid(.Body, i, 1) & " (Asc: " & Asc(Mid(.Body, i, 1)) & ")": Next i`. It will print all the characters in the Immediate window. – mielk Jul 30 '15 at 08:07
It gives me `[152]: (Asc: 13) [153]: (Asc: 10) [154]: (Asc: 13) [155]: (Asc: 10)` – Slingr Jul 30 '15 at 08:13
Ok, so try the same code as above but change this: `Chr(10)` to `Chr(13)` – mielk Jul 30 '15 at 08:15

SierraOscar · Answer 3 · 2015-07-30T08:11:16.287

0

Loop through the body to see what the character is:

For i = 1 To Len(.Body)
    If Not Mid(.Body, i, 1) Like "[A-Za-z0-9,'?!"".:]" Then
        Debug.Print Asc(Mid(.Body, i, 1))
    End If
Next

Once you've found the Asc() value, split the body on it and return the second index:

TechnicAction = Split(.Body, Asc(10))(1) '// Or whatever Asc() is required

edited Jul 30 '15 at 08:11

answered Jul 30 '15 at 08:05

SierraOscar

17,507
6
40
68

I've updated the pattern, I made a typo but I've also added some extra characters. – SierraOscar Jul 30 '15 at 08:24

Find "carriage return" in a mail.body

3 Answers3

Linked