Solved

adjust the function Import Emails into Tabs

Posted on 2013-01-03
12
253 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

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 …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 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