Solved

adjust the function Import Emails into Tabs

Posted on 2013-01-03
12
252 Views
Last Modified: 2013-01-04
gowflow, this is a tweak to the solution:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27893051.html

How this needs to be adjusted is that some of our customers have two  Liberty Reserve accounts, so I need to have two values (or more) in Col E (their LR account number) so that if either are found the email data is imported.

As you may remember the part of the email needing importing is the below where the 'From Account' value is the value that is searched on in the Mastercard workbook.

Date: 1/3/2013 9:36 AM
Batch: 123456789
From Account: U7787878 (Generet)
Amount: $6,000.00
Memo: reference card number 31234567890 Deternerina

Any questions, please ask.
0
Comment
Question by:JaseSt
  • 6
  • 6
12 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38740172
Sorry I do not understand what is requested.
gowflow
0
 

Author Comment

by:JaseSt
ID: 38740226
When the Import Emails into Tabs button is clicked it will look for Liberty Reserve emails (examples attached on referenced solution) and I need there to be the option of having TWO account numbers (example of one: U7787878) in Col E of the Mastercard workbook that the macro looks for and if it finds one of them, the begin the import.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38740288
ok I was confused when you said values I thought you wanted the amount's column to hold 2 values this is impossible.

1) You mean to say you want Cell E2 to possibly hold more than 1 account is that correct ?
2) Just to make sure the email Imported will ALWAYS hold 1 Account is that correct ? or it is possible that the email could hold more than 1 account ???
3) This situation is only valid for Liberty Emails or could extend to other emails types ?

gowflow
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:JaseSt
ID: 38740324
1) You mean to say you want Cell E2 to possibly hold more than 1 account is that correct ?
CORRECT

2) Just to make sure the email Imported will ALWAYS hold 1 Account is that correct ?
CORRECT. ONLY ONE, NOT TWO.

3) This situation is only valid for Liberty Emails or could extend to other emails types ?
ONLY LIBERTY RESERVE EMAILS

Thank you.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 38740543
ok here it is I think this should do it.
1) Make a copy of ur latest MC file and give it a new name.
2) Doubleclick on module1 and click on the lower left icon to view 1 sub at a time.
3) Locate the Function FindExcelTab and delete it.
4) Paste the below code after any End Sub

Function FindExcelTab(ByVal CC As String, EmailType As String) As String
Dim FoundIt As Boolean

For Each WS In ActiveWorkbook.Worksheets
If Mid(WS.Name, 1, 3) <> "MC " And _
       WS.Name <> "Main" And _
       WS.Name <> "Final Report" Then
    If EmailType <> "Liberty" Then
        If Len(WS.Range("D2")) > 15 Then
            If Left(Format(WS.Range("D2"), "#"), Len(Format(WS.Range("D2"), "#")) - 1) = Left(CC, Len(CC) - 1) Then
                MsgBox ("Found " & WS.Name)
                FoundIt = True
                Exit For
            End If
        End If
    Else
        If WS.Range("E2") = CC Or InStr(1, WS.Range("E2"), CC) <> 0 Then
            MsgBox ("Found " & WS.Name)
            FoundIt = True
            Exit For
        End If
    End If
End If
Next WS

If FoundIt Then
    FindExcelTab = WS.Name
Else
    FindExcelTab = ""
End If

End Function

Open in new window


5) SAVE and Exit the Workbook.
6) open it and try it with a sheet that has 2 or more code in E2 and try import an email.

Let me know
gowflow
0
 

Author Comment

by:JaseSt
ID: 38740934
question: how should the account numbers be separated? Is a space ok?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38740949
a space is A MUST !!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38743833
Any news ? Did you try it out ?
gowflow
0
 

Author Closing Comment

by:JaseSt
ID: 38743878
Just finished trying it before you posted. Works perfectly! Thank you very much, gowlfow.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38743900
Great. Pls feel free to post here any other issue you may need help with.
Gowflow
0
 

Author Comment

by:JaseSt
ID: 38743904
having a problem with Create Monthly Totals. I'll post in another question and give link to it here.
0
 

Author Comment

by:JaseSt
ID: 38743912
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question