related to Outlook to Spreadsheet previous question

gowflow, do you remember the project/question having to do with importing Earthport Emails such as this into the sheet Wire-Staging-FBME of the Visa workbook?

Dear Client,
You have received a Payment.
From: 438101360xxxxxx George Hulu
Amount: EUR 5600.60
Please log in and check your EUR-account statement for more details.

(This is an automatic notification.)
Kind regards
Customer Support


The above email is going to change for many of them so that it will look like this or the above:


Dear Client,
You have received a Payment.
From: 100003109746 - 438101360xxxxxx George Hulu
Amount: EUR 5600.60
Please log in and check your EUR-account statement for more details.

(This is an automatic notification.)
Kind regards
Customer Support


Note the 100003109746 number now preceding the 438 number.
I have yet to receive any of these updated email formats so I need the code to be edited
so that whether the email comes in with the digit '4' after the "From:" or a "100" after
the "From:" they both get imported to the Visa workbook, to the Wire-Staging-FBME sheet as before,
but there is another edit:

1. WHEN there is a number starting with "100" after the 'From:' put that number in Col D AND
2. Put the credit card number (that starts with '4') in Col G.
3. IF there is no '100' and only the '4' number after 'From:' still put that '4' number, the credit
card number, in Col G and leave Col D blank. I will fill it in manually.

That will do it for now. There will be related questions to this once this is done.

Let me know if you need anything else. This question is related to:

 http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27156733.html
JaseStAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
Ahh ok Capiche !!!
ok here you go then.

1) make a new cpy of your present Visa file after you do the change to CC number colums and give it a new name.
2) goto VBA doubleclick module1 and select to view 1 sub at a time and Delete the sub ImportData.
3) Select ALL in the below code and copy is after any End Sub in module1.

 
Function ImportData(Body As String, ByVal RDate As Date, Row As Long) As String
On Error GoTo Errhandler2

Dim CC As String
Dim ID As String
Dim CName As String
Dim Cur As String
Dim Amt As Double
Dim Tmp As String

tmpa = Split(Body, Chr(10))

For I = 0 To UBound(tmpa)
    'Find CC and CName
    st = InStr(1, tmpa(I), "From:", vbTextCompare)
    If st <> 0 Then
        'New Way of Capturing Fields
        tmpb = Split(Mid(tmpa(I), st + 6, InStr(st, tmpa(I), Chr(13), vbBinaryCompare)), " ")
        For J = 0 To UBound(tmpb)
                If IsNumeric(tmpb(J)) And Left(tmpb(J), 1) = "4" Then
                    CC = tmpb(J)
                Else
                    If IsNumeric(tmpb(J)) And Left(tmpb(J), 3) = "100" Then
                        ID = tmpb(J)
                    Else
                        If Not IsNumeric(tmpb(J)) And Len(tmpb(J)) <> 1 And tmpb(J) <> "-" Then
                            CName = CName & tmpb(J) & " "
                        Else
                            If IsNumeric(tmpb(J)) And CC <> "" And Left(tmpb(J), 1) <> "4" Then
                                CC = CC & tmpb(J)
                            End If
                        End If
                    End If
                End If
        Next J
        CName = RTrim(CName)
        If CC = "" Then
            ImportData = Mid(tmpa(I), st + 6)
            Exit Function
        End If
        'Old Way of Capturing Fields
        'If Not IsNumeric(Mid(tmpa(I), st + 6, 1)) Or Mid(tmpa(I), st + 6, 1) <> "4" Then
        '    ImportData = Mid(tmpa(I), st + 6)
        '    Exit Function
        'Else
        '    tmpb = Split(Mid(tmpa(I), st + 6, InStr(st, tmpa(I), Chr(13), vbBinaryCompare)), " ")
        '    For J = 0 To UBound(tmpb)
        '        If IsNumeric(tmpb(J)) Then
        '            CC = CC & tmpb(J)
        '        Else
        '            Exit For
        '        End If
        '    Next J
            
        '    For K = J To UBound(tmpb)
        '        CName = CName & tmpb(K) & " "
        '    Next K
        '    CName = RTrim(CName)
        'End If
    End If
    
    'Find Curency, Amt
    st = InStr(1, tmpa(I), "Amount:", vbTextCompare)
    If st <> 0 Then
        tmpb = Split(Mid(tmpa(I), st + 8, InStr(st, tmpa(I), Chr(13), vbBinaryCompare)), " ")
        If UBound(tmpb) = 1 Then
            Cur = tmpb(0)
            Amt = tmpb(1)
        End If
    End If
Next I

'Save to Excel
With wsVisa
    .Cells(Row, "B") = RDate
    .Cells(Row, "C") = CName
    .Cells(Row, "D") = ID
    .Cells(Row, "G").NumberFormat = "@"
    .Cells(Row, "G") = CC
    
    .Cells(Row, "E") = Cur
    If Cur = "EUR" Then
        .Cells(Row, "J") = Amt
    Else
        .Cells(Row, "K") = Amt
    End If
    wsMain.Range("L" & CRow) = "Import Data : <" & CName & " " & RDate
    CRow = CRow + 1
End With
ImportData = ""

Exit Function

Errhandler2:
MsgBox (Error(Err))
wsMain.Range("L" & CRow) = "Import Data - Error: <" & Error(Err) & "> Item " & CName & " " & RDate
CRow = CRow + 1

Resume Next

End Function

Open in new window


4) Save the workbook and Exit
5) Start it and try an email or more

Let  me know
gowflow
0
 
gowflowCommented:
OK fine question
You mention not having a sample email yet but can you confirm the format of the email in the From area
From: 100003109746 - 438101360xxxxxx George Hulu
will it be From: the ID number then space dash space CC number space Customer name ?
I need to know the format here. Can you revert on this as without a sample and a confiration on the format wonder what can I plot !!
Rgds/gowflow
0
 
gowflowCommented:
An other issue you say:
Put the CC in G and the ID in D
Now the CC is in D and G is blank Why don't you put ID in G and leave CC in D ??? Why this twist to change ?
gowflow
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
JaseStAuthor Commented:
Yes, it will be that because I am changing all the numbers to that format:

ID number then space dash space CC number space Customer name

Everything else will be the exact same as the original email. Basically just adding the ID space dash space.
0
 
JaseStAuthor Commented:
"Now the CC is in D and G is blank Why don't you put ID in G and leave CC in D ??? Why this twist to change ?"

What I do now is copy and paste columns D, E and F and paste it into another spreadsheet that I then email to the bank and the bank is now requiring I don't use credit card numbers. Just the ID.

(My next related question is to ask you to automatically create and populate that Visa submitted spreadsheet from both  Wire-Staging-FBME and WU-Staging-FBME. Just a fyi.)
0
 
gowflowCommented:
ok so you need to know that you will have an inconsistent worksheet where part of it in col D has CC numbers then at 1 point you start to have ID there that what I am refering to. Regardless of what the bank ask you need to have your own files all the time consequent !
Pls confirm what will go in what column
gowflow
0
 
JaseStAuthor Commented:
Yes, I know that it will be inconsistent, but not really. I can just manually switch the numbers and change the column headings which I will do. Thank you.
0
 
gowflowCommented:
I am confused. Where is CC data now in Col G ????
gowflow
0
 
JaseStAuthor Commented:
Currently credit card numbers are in Col D. I am going to move them to Col G which is where I'd like you to put them and the ID number to Col D.
0
 
JaseStAuthor Commented:
Finally got a chance to test it with a real incoming email and it worked just great. Thank you. Now will ask a related question to do something more with this incoming email like calculate loading fees, etc.

Thanks, gowflow.
0
 
gowflowCommented:
Welcome.
gowflow
0
 
JaseStAuthor Commented:
I love that Currency Rate solution you did for me awhile back. Saves me a LOT of time.
Here's another similar request

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27478808.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.