Solved

adjust the function Import Emails into Tabs

Posted on 2013-01-03
12
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 31

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 31

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 31

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 31

Expert Comment

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

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 31

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

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

627 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