Solved

adjust the function Import Emails into Tabs

Posted on 2013-01-03
12
250 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now