Solved

adjust the function Import Emails into Tabs

Posted on 2013-01-03
12
248 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 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

13 Experts available now in Live!

Get 1:1 Help Now