• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Highlighting or marking an email after processing - part 2 for MC workbook

gowflow, asking for you to perform your magic for the Visa workbook as you did for the MC:

a way to flag with a green flag the emails that get processed when they have been successfully imported into the workbook - and a red when not,  I think that will cover all the needs to flag emails. Don't need it for Western Union emails.
0
JaseSt
Asked:
JaseSt
  • 9
  • 8
1 Solution
 
gowflowCommented:
ok here you go

1) Make a copy of the latest Visa file under a new name
2) Open the file goto developper tab and doubleclik on module1
3) click on the loer left icon to view 1 sub at a time
4) Select Sub LocateEmails and delete it
5) SELECT ALL in the below code right click and choose COPY and paste in module1 after any End Sub
6) SAVE the workbook and Exit
7) Start the workbook and give it a try

Pls let me know
gowflow

Sub LocateEmails()
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
Dim FMonitor, FTransfer

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'")
VisaItems.Sort "receivedtime", False
Set VItem = VisaItems.Find("[Subject] = 'Payment Received'")
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
    ' use Instr here to check subject or body
    'MsgBox objMail.Subject
    Body = objMail.Body
    ETime = objMail.ReceivedTime
    MaxRow = wsVisa.UsedRange.Rows.Count
    'MaxRow = wsVisa.Range("B1048576").End(xlUp).Row
    st = ImportData(Body, ETime, MaxRow + 1)
    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 but not Moved: <" & st & "> " & objFolderToMonitor.Items.Item(EmailMoved)
        CRow = CRow + 2

    End If
    I = I + 1
    Set VItem = VisaItems.FindNext
Loop Until I = TotItems + 1

MsgBox ("Total Emails processed from '" & objFolderToMonitor & "' " & TotItems & Chr(10) _
    & "Total Emails Imported: " & EmailMoved & Chr(10) _
    & "Total Emails Not Imported: " & EmailNotMoved & " and kept in '" & objFolderToMonitor & "'" _
    & "All Emails were kept in their original location: '" & objFolderToMonitor & "'")
wsMain.Range("L" & CRow) = ("Locate Emails - Total Emails processed from '" & objFolderToMonitor & "' " & TotItems _
    & "Total Emails Imported: " & EmailMoved _
    & "Total Emails Not Imported: " & EmailNotMoved & " and kept in '" & objFolderToMonitor & "'" _
    & "All Emails were kept in their original location: '" & 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

0
 
JaseStAuthor Commented:
Wow! That was fast! Guess you were expecting this one.

Plugged it in and will try it when a few of those emails come in later today. Thank you, as always.
0
 
gowflowCommented:
no problem
gowflow
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
JaseStAuthor Commented:
Worked like a charm! Thank you, gowflow.

Now on to a bit more challenging aspects of the Western Union work flow with the Visa workbook. I'll post the link here here when I've thought it through - first with an explanation of what I'm trying to accomplish.

Thanks again, gowflow! I appreciate all your hard work.
0
 
gowflowCommented:
your welcome and tks for the grade and nice comments.
gowflow
0
 
gowflowCommented:
u seem to be busy ... ?
gowflow
0
 
JaseStAuthor Commented:
I'm more trying to figure out how I want to do this next step with Western Union. I think I'll have to explain you the whole thing first and the break the questions up into sections.
0
 
gowflowCommented:
ok as you like no problem you can explain here and I will suggest as much as possible.
gowflow
0
 
JaseStAuthor Commented:
There are a few things going on with the Visa spreadsheet, particularly the WU-Staging-FBME sheet.
Once we send in the sheet to Joseph he then sends me back a copy of the spreadsheet with column H, Confirmed Amounts, filled in. He does not pick all of them up at once. Sometimes he picks up a batch I sent to him a week or so later. He picks and chooses, but the end result is that I copy and paste the amounts he gives me from his Col H and paste it into my col H of the WU-Staging-FBME sheet. - continued....
0
 
JaseStAuthor Commented:
What needs to happen with the amounts I paste in Col H is as follows (and I will submit a question about this) in parts.  When Col H in WU-Staging-FBME of a batch is filled:

1. In Col i of the top row of the batch, sum up the batches total amount. (see attached image) A number of months ago someone already created that functionality for me. It also puts in values in Cols T through Z.  Here's what it does:
- col U copies the value in Col i.
- puts 1.4 as an arbitrary conversion rate value, converting the USD amount (from Col i) to convert to Euros in Col V
- Col W has this calculation: =$V$1561*5% (for row 1561) which pads the conversion rate by that amount
- then adds the 1.4 from Col V to the value in Col W and puts the new conversion rate (for USD to Euros) in Col X
- then in Col Y it uses this formula: =$U$1561/$X$1561 to finally convert the Value in Col U from USD to Dollars and puts that amount in Col Y
- Col Z uses the formula: =$Y$1561*7.5% to calculate the loading fee
- and finally, in Col T uses this formula: =IF(Y1561*7.5%<75, Y1561-75, Y1561-(Y1561*7.5%)) to put the final Euro amount that gets loaded to the cardholders Visa card.

If you have this sheet if you manually put a value in Col i, you should see this all outplay.

Let me know if you have questions so far and if this is clear.
0
 
gowflowCommented:
Couple of questions:

1) but the end result is that I copy and paste the amounts he gives me from his Col H and paste it into my col H of the WU-Staging-FBME sheet.
>> Do you want this to be automated ? if yes I need to see in attachment the file he sends.

2) In Col i of the top row of the batch, sum up the batches total amount. (see attached image) A number of months ago someone already created that functionality for me. It also puts in values in Cols T through Z.  Here's what it does:
>> No image attached. What is the name of the Sub created ? is it in the workbook I have copy of ?

3) I input amounts in H and I in WU-Staging-FBME and nothing happened !

gowflow
0
 
JaseStAuthor Commented:
1. Well, yes, that would be great if you can do that - didn't know it was possible - but you have to match MTCN numbers and another field just to make sure they are the same row. Sometimes MTCN#'s and sender name and address are duplicated. I'll attach an altered copy of what he sends in just a minute.

2. Sorry, yes you have the Visa workbook. Not sure the name of the sub. I could try to figure that out.

3. Maybe you first have to sum a batch from Col H values and put that sum in Col i. Then copy all those values in Col H and paste them right back where you copied them from. Weird that I have to do that to make it work.
0
 
JaseStAuthor Commented:
an altered spreadsheet of what Joseph sends back to me
Wk-WK42.xls
0
 
gowflowCommented:
ok the way I see it you need to break this in 2 questions
1) automate worksheet form joseph (provided he only change column H) and he doesn't play with col A,B,C,D,E,F (logically I don't see any reason why he should change these columns .. unless ??? u tell me)

2) Question regarding the rest of what you discribed. Yes there is some code there but when u post I will look at it.

gowflow

0
 
JaseStAuthor Commented:
1. He doesn't - as much as I can recall - alter Cols A - G other than sometimes pointing out an error by changing the font color to red for a particular row. When that is the case, or if there is a discrepancy, or if he can't pick something up, he'll change the font color and put a note in Col H. Example attached.

2. Ok, I will submit a question asking point 1 to be done first.

Please keep in mind that once the amounts are inserted in Col H of my sheet, then other things happen and more needs to happen - maybe in stages - which will be further parts to this overall question. wu spreadsheet
0
 
gowflowCommented:
ok no problem for me post the question and give detail and then will go one after the othr !!!
I hv no problem even if it is 100 questions !!!
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!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now