Solved

Change the location of button and change function already created for spreadsheet - Excel 2007

Posted on 2011-09-24
26
283 Views
Last Modified: 2012-05-12
I have a spreadsheet with 5 tabs. There are a number of things I need to have done with it, but for this question, here's the first.

Currently there is a button on 'Import WU Emails' on the attached spreadsheet. When clicked it goes through my Inbox and inputs the data onto that page and does so fairly well. However, here's what I need changed:

1. Move the button currently on the 'Import WU Emails' sheet to the 'Main' sheet..
2. When clicked input the data to 'WU-Staging-FBME' and NOT to the 'Import WU Emails' sheet. When that is done I will delete sheet, 'Import WU Emails' and move on to the next question.
3. When importing if there is a leading zero for the MTCN number or an ending zero, they must stay when imported to the WU-Staging-FBME sheet. In the code currently used, the leading zero seems to be dropped.
4. Also, if there is any other characters besides a dollar sign, it needs to be removed and not imported.

Please look at the code behind Button 1: Sub Q_26900870() to see how it works. It needs to be improved as it sometimes misses importing if there is not a colon or if the label in the email body is misspelled. Improving how it functions will be a related question after this first part is solved. Thank you.
Example sheet with names and card numbers altered is attached. Visa-Aug2011-EE-Example.xlsm
0
Comment
Question by:JaseSt
  • 15
  • 11
26 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Is Sub Q_26900870() the altered routine put in Main and called Import Emails already worked on previously or it is a total diffeent routine ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
An other question you need to import the emails into 'WU-Staging-FBME' correct ?
1) Can we use the above Monitor folder for this purpose as the routine already there is strictly directed to the inbox. With monitor it gives you the choice to choose the location to monitor.
2) Do you want a separate button of the existing Import Email that import to wire-stagging-FBME ? or the same button ?
Rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
One last issue to make your life easier is it possible to move the requested sheets to your Mastercard file or it need to stay in a separate file as we used already couple of routines that are tested and working ! ??? If not then will most likely need to move maybe some Subs here.
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
I'm not sure what you're asking, gowflow, with your first question. As far as I know, there are two routines: one that imports emails to the Wire-Staging-FBME sheet and another that imports a different email to Import WU Emails. The Sub Q_26900870 is the routine dealing with this question.

Question 1: Yes if you want to do that.
Question 2: I would like two different buttons: 'Western Union Import' for this one

I'd rather keep them separate. Mastercard already has two many tabs anyhow.

I'm attaching example emails for this question. You will see that for the import function to import the data there has to be a colon following the label and the label has to meet one of the spelling options of the label. I'm continually adding spelling variations and if I have to continue to do that I will, but it would be nice if I didn't have to. Let me know if you have any more questions. WesternUnionEmailExamples.zip
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok capich / comprendo / understood ! I actually forgot to ask u to post some sample emails which you did quite smartly.

Agree that the other file is somwhat v crouded and no worry if I feel that some routine need revamping and getting from the other one I will let you know.
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
gowflow, not sure if you get notifications from previous questions solved, issue came up:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27301478.html#a36710605
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
JaseSt,

Here it is. In the attached file you can use it to test the 'Western Union Imports' button in sheet Main. The principle is the same as previous imports. You set the folder you wish to monitor emails for and activate the button it will go thru the choosen folder in outlook and look for emails and import them into 'WU-Staging-FBME' sheet as requested.

The routine has been altered to cater for your requirement in this question to satissfy your points 1, 2, 3 and 4. Obviously as you pointed out the routine is not optimal and need revamping as sometimes it misses not only card numbers but also it include lines that should not be there. As you mentioned if you are statissfied with the results as you requested it can be improved if you wishes so.

To implement it in your production workbook:
==================================
1) Make a new copy of your latest Visa file and save it to a new name.
2) Download the attached file and save it to a name that is diffrent from your newly created Visa file.
3) Start the new created Visa file and goto VBA and doubleclik on Module1 select to view 1 sub at a time by clicking on the left icon in the bottom window.
4) Press on SELECT ALL in the below code and right click choose copy and then paste it after any end sub in module1.
5) SAVE the Visa workbook.
6) Goto file/open in your Visa file and open the attached downloaded file.
7) Goto Developper menu and make sure the icon Design in the middle of the ribbon is pressed if your not sure if it is pressed get your mouse over the button Western Union Imports if it shows as an arrow this means design is not pressed it it shows as 2 square arrows this means your in design mode. click on the western Union Import button and right click choose copy
8) Goto your Visa workbook and display Main and paste the button near the Import Emails button
9) Doubleclick on the Western Union button and type after Sub Command2_click ImportWesternUnion so that you end up with something like this:
-----------------
Private Sub CommandButton2_Click()
ImportWesternUnion
End Sub
-----------------
10) SAVE your Visa workbook
11) Goto sheet Import WU Emails and click on the button (also make sure your in design mode the cross arrow) and delete the button.
12) SAVE the workbook Visa and Exit it.
13) Exit the attached workbook that you had opened but without saving
14) Start your newly created Visa and test it.

Pls let me know.
PS I found why we used to have object not found and the reason is a folder is selected wether in Monitor or Transfer that is not in outlook you would get this error !!! I can fix this and if you want we can treat this whole issue on an other question so it is user set like when you want and feel good about it you turn the moving emails on. If you want you can turn it off.
Rgds/gowflow
Sub ImportWesternUnion()
Dim WS As Worksheet
Dim objOutlook As Object
Dim rng As Range
Dim arrRows() As String
Dim arrRow() As String
Dim elem As Variant
Dim FMonitor, FTransfer

    
Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set WS = Sheets("WU-Staging-FBME")

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

'Disabled in this procedure as user do not want to move emails.
'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

I = 1
Application.EnableEvents = False

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

    If InStr(1, UCase(Body), "MTCN", vbTextCompare) > 0 Then
        Set rng = WS.Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        arrRows = Split(Body, vbCrLf, , vbTextCompare)
        For Each elem In arrRows
            If Left(elem, 1) = "-" Then
                Set rng = rng.Offset(1, 0)
                I = I + 1
                rng.Offset(0, 1) = Format(ETime, "dd mmm yyyy")
                rng.Offset(0, -1) = I
                wsMain.Range("L" & CRow) = "    Item: " & I & " " & VItem.SenderEmailAddress
                CRow = CRow + 1
            Else
                If InStr(elem, ":") > 0 Then
                    arrRow = Split(elem, ":")
                    Select Case Trim(UCase(CStr(arrRow(0))))
                        Case "MTCN", "MTCN#", "MCTN", "MCTN#", "mtcn #", "MTCN #"
                            'Fix to importing MCTN with all characters including leading and trailing zeros
                            rng.Offset(0, 2) = Trim(Format(arrRow(1), "@"))
                        Case "RECEIVER", "Reciever info", "RECEIVER NAME", "reciever", "Reciever", "RECIEVER", "RECEVIER"
                            rng = arrRow(1)
                        Case "SENDER"
                            rng.Offset(0, 3) = arrRow(1)
                        Case "SENDER LOCATION", "LOCATION", "SENDER'S W.U. LOCATION", "SENDER'S W.U. LOCATION(CITY & STATE)", "Senders W.U. Location", "SENDER'S W.U. LOCATION (city and state)", "SENDER'S LOCATION", "Sender info", "SENDER LOC ", "W u location", "Sender WU Location", "SEND LOC ", "Senders loc", "SENDER LOC", "Senders WU Locations", "Senders W.U's location", "sent from", "SENDERS W/U LOCATION", "ADDRESS", "Senders W.U. location", "senders Location", "Sender WU Location", "Sender location sent", "Sender W/U location", "Sender W.U. Location", "W.U. LOCATION", "W/U LOCATION", "W.U. LOCTION", "sender loc.", "AMT SENT"
                            rng.Offset(0, 4) = arrRow(1)
                        Case "AMOUNT", "AMT", "Amount sent", "TOTAL", "amount sent", "Total Amount"
                            'Fix to importing amount as a number formated as $currency with double digits and
                            'Red if negatives
                            rng.Offset(0, 5) = CDbl(arrRow(1))
                            rng.Offset(0, 5).NumberFormat = "$#,##0.00;[Red]($#,##0.00)"
                        Case Else
                    End Select
                End If
            End If
        Next elem
    End If

Next VItem

Application.EnableEvents = True

WS.UsedRange.EntireColumn.AutoFit
MsgBox ("Total of " & I & " Western Union detailed transfer imported successfully.")
End Sub

Open in new window

0
 

Author Comment

by:JaseSt
Comment Utility
There is no attachment to download, gowflow.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
OOPS !!! SORRY HERE IT IS
GOWFLOW
Visa-Aug2011-EE-Example.xlsm
0
 

Author Comment

by:JaseSt
Comment Utility
After downloading your file, renaming it and saving it, then inserting the above code as you mention, then trying to save, I get the attached error. Visa error
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Why you have Mastercard-EP-Outlook ... and SaveCardApplication.xlam there opened  as well as your visa file. Try only opening the visa file close all the other and see if you get this errror. Try rebooting your pc and opening only the visa file..
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I read your post again and see:
After downloading your file, renaming it and saving it, then inserting the above code as you mention, then trying to save, I get the attached error.

What on earth did you do ??????????????????????????????
You downloaded the attached file then renamed it then inserted the sub ???????????????????????????????????????????????? is this what you did???? You are not supposed to do this !!! Read my instructions carefully.
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
Well, you are a bit confusing here:

2) Download the attached file and save it to a name that is diffrent from your newly created Visa file.
3) Start the new created Visa file and goto VBA and doubleclik on Module1 select to view 1 sub at a time by clicking on the left icon in the bottom window.
4) Press on SELECT ALL in the below code and right click choose copy and then paste it after any end sub in module1.
5) SAVE the Visa workbook.
6) Goto file/open in your Visa file and open the attached downloaded file.

For #6 do you mean to open the file I downloaded and renamed as you suggest in #3? Or open the file you sent? They are both the same file just with different names. Please clarify.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
the file I sent and the file downloaded and renamed are exactly the same and 1. I am talking about the Visa file that you have npw that contain your live data with real names and credit card that you need to save as and in this file input all what I told you about and save it and then start this new workbook Visa how cleraer can this be ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Pls post the workbook that is giving you the problem so I check it out. If you want change names and change CC numbers.
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility

Got it to work, thank you. Needs some improvements if that is acceptable to you in this question:

1. Create a colored filled in row between separate imported emails - make it yellow I suppose
2. The empty colored row cannot have a number in Col A.
3. It is expanding the column widths - this page has a lot going on with it (as you will see in future questions) and need the column widths to be as narrow as possible.
4. It is still missing the import of a few - I'll address that

Curious, just ran again and got error with this line highlighted in yellow:

wsMain.Range("L" & CRow) = "Locate Emails - FMonitor: " & objFolderToMonitor

Gotta go, be back in an hour or so.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi JaseSt,
This  version will skip a blank yellow line between each email. the columns are no more fitwidth so you can adjust them manually once to the width of your choice and they remain asis.

As far as the error is concerned this happens if the program was halted then re-run again. If the program is not interrupted if you ran the macro several times in a row it should work correctly.
gowflow
Sub ImportWesternUnion()
Dim WS As Worksheet
Dim objOutlook As Object
Dim rng As Range
Dim arrRows() As String
Dim arrRow() As String
Dim elem As Variant
Dim FMonitor, FTransfer
Dim FoundDivider As Boolean
    
Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set WS = Sheets("WU-Staging-FBME")

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

'Disabled in this procedure as user do not want to move emails.
'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

I = 1
Application.EnableEvents = False

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

    If InStr(1, UCase(Body), "MTCN", vbTextCompare) > 0 Then
        If rng Is Nothing Then
            Set rng = WS.Range("B" & Rows.Count).End(xlUp).Offset(0, 0)
            rng.Offset(1, 0).EntireRow.Insert
            WS.Range(rng.Offset(1, -1), rng.Offset(1, 33)).Interior.ColorIndex = 6
        Else
            rng.Offset(1, 0).EntireRow.Insert
            WS.Range(rng.Offset(1, -1), rng.Offset(1, 33)).Interior.ColorIndex = 6
            Set rng = rng.Offset(1, 0)
        End If
        arrRows = Split(Body, vbCrLf, , vbTextCompare)
        For Each elem In arrRows
            If Left(elem, 1) = "-" Or Left(elem, 1) = "=" Then
                FoundDivider = True
            End If
                
            If InStr(elem, ":") > 0 Then
                arrRow = Split(elem, ":")
                If FoundDivider Then
                    Set rng = rng.Offset(1, 0)
                    rng.Offset(0, 1) = Format(ETime, "dd mmm yyyy")
                    rng.Offset(0, -1) = I
                    wsMain.Range("L" & CRow) = "    Item: " & I & " " & VItem.SenderEmailAddress
                    CRow = CRow + 1
                    I = I + 1
                    FoundDivider = False
                End If
            
                Select Case Trim(UCase(CStr(arrRow(0))))
                    Case "MTCN", "MTCN#", "MCTN", "MCTN#", "mtcn #", "MTCN #"
                        'Fix to importing MCTN with all characters including leading and trailing zeros
                        rng.Offset(0, 2) = Trim(Format(arrRow(1), "@"))
                    Case "RECEIVER", "Reciever info", "RECEIVER NAME", "reciever", "Reciever", "RECIEVER", "RECEVIER"
                        rng = arrRow(1)
                    Case "SENDER"
                        rng.Offset(0, 3) = arrRow(1)
                    Case "SENDER LOCATION", "LOCATION", "SENDER'S W.U. LOCATION", "SENDER'S W.U. LOCATION(CITY & STATE)", "Senders W.U. Location", "SENDER'S W.U. LOCATION (city and state)", "SENDER'S LOCATION", "Sender info", "SENDER LOC ", "W u location", "Sender WU Location", "SEND LOC ", "Senders loc", "SENDER LOC", "Senders WU Locations", "Senders W.U's location", "sent from", "SENDERS W/U LOCATION", "ADDRESS", "Senders W.U. location", "senders Location", "Sender WU Location", "Sender location sent", "Sender W/U location", "Sender W.U. Location", "W.U. LOCATION", "W/U LOCATION", "W.U. LOCTION", "sender loc.", "AMT SENT"
                        rng.Offset(0, 4) = arrRow(1)
                    Case "AMOUNT", "AMT", "Amount sent", "TOTAL", "amount sent", "Total Amount"
                        'Fix to importing amount as a number formated as $currency with double digits and
                        'Red if negatives
                        rng.Offset(0, 5) = CDbl(arrRow(1))
                        rng.Offset(0, 5).NumberFormat = "$#,##0.00;[Red]($#,##0.00)"
                    Case Else
                End Select
            End If
            
        Next elem
        
    End If

Next VItem

'rng.Offset(1, 0).EntireRow.Insert
'WS.Range(rng.Offset(1, -1), rng.Offset(1, 33)).Interior.ColorIndex = 6
Application.EnableEvents = True

'WS.UsedRange.EntireColumn.AutoFit
MsgBox ("Total of " & I & " Western Union detailed transfer imported successfully.")
End Sub

Open in new window

0
 

Author Comment

by:JaseSt
Comment Utility
Deleted the old code, inserted the above. Saved. Closed down Excel. Restarted and opened the Visa file. Ran the Western Union Import and got the following error. Visa Error 2
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
press debug what line ?
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
highlighted in yellow:

wsMain.Range("L" & CRow) = "Locate Emails - FMonitor: " & objFolderToMonitor
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
make sure workbook is closed and saved the opened run macro ensure both Monitor and Transfer email have both a VALID mailbox link if not then select a valid link from the drop down save and exit workbook and the open again.
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
did all that and ran again with same type mismatch error 13, but this time, highlighted in yellow:

  rng.Offset(0, 5) = CDbl(arrRow(1))

It seems to run for awhile, importing a few then comes up with this error when it hits this particular batch in the email. Note from the screenshot it does not import the amount into col G. Could it be due to the characters in the Amount?

MTCN#: 006-857-1234
RECEIVER: BEATA PWOER
 SENDER: SEAN Drave
LOCATION : SUWANEE, GEORGIA,
AMOUNT: $615 USD

Also, it is still expanding the columns, even after I re-narrowed them before import. Really can't have that if at all possible. Vis Error 3
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Try this new procedure
gowflow
Sub ImportWesternUnion()
Dim WS As Worksheet
Dim objOutlook As Object
Dim rng As Range
Dim arrRows() As String
Dim arrRow() As String
Dim elem As Variant
Dim FMonitor, FTransfer
Dim FoundDivider As Boolean

    
Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set WS = Sheets("WU-Staging-FBME")

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

'Disabled in this procedure as user do not want to move emails.
'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

I = 1
Application.EnableEvents = False

For Each VItem In VisaItems
    wsMain.Range("L" & CRow) = "Import Western Union - Items: " & 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

    If InStr(1, UCase(Body), "MTCN", vbTextCompare) > 0 Then
        If rng Is Nothing Then
            Set rng = WS.Range("B" & Rows.Count).End(xlUp).Offset(0, 0)
            rng.Offset(1, 0).EntireRow.Insert
            WS.Range(rng.Offset(1, -1), rng.Offset(1, 33)).Interior.ColorIndex = 6
        Else
            rng.Offset(1, 0).EntireRow.Insert
            WS.Range(rng.Offset(1, -1), rng.Offset(1, 33)).Interior.ColorIndex = 6
            Set rng = rng.Offset(1, 0)
        End If
        arrRows = Split(Body, vbCrLf, , vbTextCompare)
        For Each elem In arrRows
            If Left(elem, 1) = "-" Or Left(elem, 1) = "=" Then
                FoundDivider = True
            End If
                
            If InStr(elem, ":") > 0 Then
                arrRow = Split(elem, ":")
                If FoundDivider Then
                    Set rng = rng.Offset(1, 0)
                    rng.Offset(0, 1) = Format(ETime, "dd mmm yyyy")
                    rng.Offset(0, -1) = I
                    wsMain.Range("L" & CRow) = "    Item: " & I & " " & VItem.SenderEmailAddress
                    CRow = CRow + 1
                    I = I + 1
                    FoundDivider = False
                End If
            
                Select Case Trim(UCase(CStr(arrRow(0))))
                    Case "MTCN", "MTCN#", "MCTN", "MCTN#", "mtcn #", "MTCN #"
                        'Fix to importing MCTN with all characters including leading and trailing zeros
                        rng.Offset(0, 2) = Trim(Format(arrRow(1), "@"))
                    Case "RECEIVER", "Reciever info", "RECEIVER NAME", "reciever", "Reciever", "RECIEVER", "RECEVIER"
                        rng = arrRow(1)
                    Case "SENDER"
                        rng.Offset(0, 3) = arrRow(1)
                    Case "SENDER LOCATION", "LOCATION", "SENDER'S W.U. LOCATION", "SENDER'S W.U. LOCATION(CITY & STATE)", "Senders W.U. Location", "SENDER'S W.U. LOCATION (city and state)", "SENDER'S LOCATION", "Sender info", "SENDER LOC ", "W u location", "Sender WU Location", "SEND LOC ", "Senders loc", "SENDER LOC", "Senders WU Locations", "Senders W.U's location", "sent from", "SENDERS W/U LOCATION", "ADDRESS", "Senders W.U. location", "senders Location", "Sender WU Location", "Sender location sent", "Sender W/U location", "Sender W.U. Location", "W.U. LOCATION", "W/U LOCATION", "W.U. LOCTION", "sender loc.", "AMT SENT"
                        rng.Offset(0, 4) = arrRow(1)
                    Case "AMOUNT", "AMT", "Amount sent", "TOTAL", "amount sent", "Total Amount"
                        'Fix to importing amount as a number formated as $currency with double digits and
                        'Red if negatives
                        If Not IsNumeric(arrRow(1)) Then arrRow = Split(elem, " ")
                        rng.Offset(0, 5) = CDbl(arrRow(1))
                        rng.Offset(0, 5).NumberFormat = "$#,##0.00;[Red]($#,##0.00)"
                    Case Else
                End Select
            End If
            
        Next elem
        
    End If

Next VItem

'rng.Offset(1, 0).EntireRow.Insert
'WS.Range(rng.Offset(1, -1), rng.Offset(1, 33)).Interior.ColorIndex = 6
Application.EnableEvents = True

'WS.UsedRange.EntireColumn.AutoFit
MsgBox ("Total of " & I & " Western Union detailed transfer imported successfully.")
End Sub

Open in new window

0
 

Author Closing Comment

by:JaseSt
Comment Utility
Works great! Thank you.

Next step and question is to work on improving and adding to what it brings in, but as I said initially, we'll go through this step by step - if you're willing. Let me know and I'll post a link here to the next step for this worksheet.

So, can I delete the Import WU Emails sheet now?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Yes pls gho ahead and post new question. You may surely delete WU Emails.
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

772 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

11 Experts available now in Live!

Get 1:1 Help Now