Solved

fixing previous solution

Posted on 2013-01-12
10
243 Views
Last Modified: 2013-01-15
The attached scrubbed workbook has a macro that imports emails from Outlook into different sheets when the credit card number in the email = cell D2. It use to highlight the category of the email red or green if not processed or processed.

Since I switched over to Outlook 2010 (had Outlook 2007 before) it no longer puts a color to indicate the status. It does, however  say Not Processed or Processed. I would like it to use colors instead as it is much easier to see.

I also attach a screenshot at what the Visa card is doing (correctly) and what the Mastercard workbook is doing.MC-Spreadsheet-scrubbed.xlsmmcvisa
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
  • 4
10 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 38770527
Is it when you activvate email into tab option ? is it for specific emails or all of them ?
gowflow
0
 

Author Comment

by:JaseSt
ID: 38770533
it's when I activate the email into tabs option - that's when it's supposed to go through the email, extract the data and import it into the spreadsheet, then highlight the emails either red (for not processed) and green (for processed)

However, keep in mind and you may remember, that a pass with the visa function will leave mc emails red which then when picked up the mc function will turn them green.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38770537
Well the function actually put them as Processed and Not Processed in MC so what do you want to do ? Shall we put the Processed to Green and the not processed to Red or .. ?
U tell me
gowflow
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:JaseSt
ID: 38770545
yes, put the Processed to Green and the not processed to Red
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 38770553
ok here it is:

1) Make a copy of ur latest MC file give it a new name.
2) Goto VBA chose to view 1 sub at a time locate the Sub LocateEMailsToTabNew and delete it.
3) Paste the below code after an end sub

Sub LocateEmailsToTabsNew()
On Error GoTo Errhandler1

Dim FoundIt As Boolean
Dim Body As String
Dim MaxRow As Long, EmailMoved As Long, EmailNotMoved As Long, TotItems As Long
Dim I As Long, J As Long, K As Long, L As Long
Dim FMonitor, FTransfer
Dim tmpBody, tmpTransferLine
Dim TransAmount As Double
Dim EmailType As String

Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
'Set objFolder = objNameSpace.Folders(gstFolderToMonitor)

FMonitor = Split(Mid(gstFolderToMonitor, 2), "\")
If Not SetMonitorFolder(FMonitor) Then Exit Sub
wsMain.Range("L" & CRow) = "Locate Emails - FMonitor: " & objFolderToMonitor
CRow = CRow + 1

FTransfer = Split(Mid(gstFolderToTransfer, 2), "\")
If Not SetTransferFolder(FTransfer) Then Exit Sub
wsMain.Range("L" & CRow) = "Locate Emails - FTransfer: " & objFolderToTransfer
CRow = CRow + 1

Dim VItem As Outlook.MailItem

Set VisaItems = objFolderToMonitor.items.Restrict("[Subject] = 'Payment Received' or ([Subject] >= 'Bank Transfer # ' and [Subject] <= 'Bank Transfer #z') or [Subject] = 'Liberty Reserve Payment Received'")
VisaItems.Sort "receivedtime", False
Set VItem = VisaItems.Find("[Subject] = 'Payment Received' or ([Subject] >= 'Bank Transfer # ' and [Subject] <= 'Bank Transfer #z') or [Subject] = 'Liberty Reserve Payment Received'")

If Not VItem Is Nothing Then
    TotItems = VisaItems.count
    I = 1
    
    
    Do
    'For Each VItem In VisaItems
        wsMain.Range("L" & CRow) = "Locate Emails - VisaItems: " & I & " " & VItem.SenderEmailAddress & " " & VItem
        CRow = CRow + 1
    
        Set objMail = VItem
        
        Body = objMail.Body
        Etime = objMail.ReceivedTime
        
        '---> Determine Email Type
        Select Case objMail.Subject
            
            Case "Payment Received"
                EmailType = "Payment"
                
            Case "Fwd: Liberty Reserve Payment Received"
                EmailType = "Liberty"
                
            Case Is >= "Bank Transfer # ", Is <= "Bank Transfer #z", Is <> "Fwd: Liberty Reserve Payment Received"
                EmailType = "Transfer"
                
        End Select
        
        '---> Depending on Type of Mail route Import
        If InStr(1, objMail.Subject, "Bank Transfer #") <> 0 Then
            '---> Process Bank Transfer Emails
            tmpBody = Split(objMail.Body, Chr(10))
            st = "Bank Transfer"
            For K = 0 To UBound(tmpBody)
                If InStr(1, tmpBody(K), "Bank Transfer #") <> 0 And InStr(1, tmpBody(K), "for USD") <> 0 Then
                    tmpTransferLine = Split(Right(tmpBody(K), Len(tmpBody(K)) - InStr(1, tmpBody(K), "for USD") + 1), " ")
                    For L = 0 To UBound(tmpTransferLine)
                        If IsNumeric(tmpTransferLine(L)) Then
                            TransAmount = tmpTransferLine(L)
                            MaxRow = Sheets("MC Heritage Balance").Range("B1048576").End(xlUp).Row + 1
                            Sheets("MC Heritage Balance").Range("B" & MaxRow) = TransAmount
                            Sheets("MC Heritage Balance").Range("C" & MaxRow) = Etime
                            Exit For
                        End If
                    Next L
                    If st = "Bank Transfer" Then
                        st = ""
                        Exit For
                    End If
                End If
            Next K
        Else
            st = ImportData5New(Body, Etime, MaxRow + 1, EmailType)
        End If
        
        'Application.EnableEvents = True
        If st <> "" Then
            MsgBox ("Email From: [" & st & "] not imported")
            EmailNotMoved = EmailNotMoved + 1
            If VItem.Categories <> "Green Category" Then VItem.Categories = "Red Category"
            VItem.Save
            wsMain.Range("L" & CRow) = "Locate Emails - Not Imported: <" & st & "> "
            CRow = CRow + 2
    
        Else
            'objMail.Move objFolderToTransfer
            'VItem.Move objFolderToTransfer
            EmailMoved = EmailMoved + 1
            VItem.Categories = "Green Category"
            VItem.Save
            wsMain.Range("L" & CRow) = "Locate Emails - Imported and Not Moved: <" & VItem.SenderEmailAddress & "> "
            CRow = CRow + 2
    
        End If
        I = I + 1
        Set VItem = VisaItems.FindNext
    Loop Until I = TotItems + 1
End If

MsgBox ("Total Emails processed from '" & objFolderToMonitor & "' " & TotItems & Chr(10) _
    & "Total Emails Imported and Not Moved: " & EmailMoved & Chr(10) _
    & "Total Emails Not Imported: " & EmailNotMoved & " and kept in '" & objFolderToMonitor & "'" _
    & "Imported Emails were kept in : '" & objFolderToMonitor & "'")
wsMain.Range("L" & CRow) = ("Locate Emails - Total Emails processed from '" & objFolderToMonitor & "' " & TotItems _
    & "Total Emails Imported and Not Moved: " & EmailMoved _
    & "Total Emails Not Imported: " & EmailNotMoved & " and kept in '" & objFolderToMonitor & "'" _
    & "Imported Emails were kept in: '" & objFolderToMonitor & "'")
CRow = CRow + 1

Exit Sub

Errhandler1:
MsgBox (Error(err))
wsMain.Range("L" & CRow) = "Locate Emails - Error: <" & Error(err) & "> Item " & VItem
CRow = CRow + 1
Resume Next

End Sub

Open in new window


4) SAVE and EXIT
5) Try it. Make sure on the same emails you try both this function for the Visa file and the MC file and see their repercussion and if all is ok.

gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38773418
Any chance to hv tired it out ?
gowflow
0
 

Author Closing Comment

by:JaseSt
ID: 38774107
Works perfectly, gowflow! Thank you!
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38774952
gr8. Anything else let me know in here.
gowflow
0
 

Author Comment

by:JaseSt
ID: 38776969
And if you're willing, this one:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27995435.html

Has to do with importing data into Visa Consolidated from the Visa workbook.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38777780
Do not see any Sub for that was developped for that new question. If u hv it pls post it so I can see it.
gowflow
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
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…

734 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