Link to home
Start Free TrialLog in
Avatar of JaseSt
JaseStFlag for United States of America

asked on

Import specific emails from Outlook (2007) to an Excel spreadsheet (2007)

I get emails with the subject heading: Payment Received.
The email body always has this format:

Dear Client,
You have received a Payment.
From: 4381013603123123 JACQUELINE Jones
Amount: EUR 3000.00
Please log in and check your EUR-account statement for more details.

(This is an automatic notification.)
Kind regards
Customer Support

Here's what I need to have happen:

1. When the first digit after From: is 4 then
2. Import the email to the Wire-Staging-FBME sheet of the Visa.xls workbook.
3. Specifically, import the credit card number (the number starting  with 4) into Col G, the 'Card Number' column.
4. Import the name that immediately follows the card number into Col F, the 'Cardholder' column
5. Import the value: EUR into Col H
6. Import the amount (the number only) into Col M
7. Import the date email received into Col B

Attached is a copy of the spreadsheet that I need these emails to be imported into when I click on a button within the Visa workbook. That's it.
 Visa-Test.xls
Avatar of sncameron
sncameron

Use the Items.Restrict method to restrict the returned messages to just those containing the required subject line. Once you have processed the mail in the folder I recommend moving them to a "processed" folder.

For example:

Sub MoveItems()
	Dim myOlApp As New Outlook.Application
	Dim myNamespace As Outlook.NameSpace
	Dim myFolder As Outlook.Folder
	Dim myItems As Outlook.Items
	Dim myRestrictItems As Outlook.Items
	Dim myItem As Outlook.MailItem
	
	Set myNamespace = myOlApp.GetNamespace("MAPI")
	Set myFolder = _
	myNamespace.GetDefaultFolder(olFolderInbox)
	Set myItems = myFolder.Items
	Set myRestrictItems = myItems.Restrict("[Subject] = 'Payment Received'")
	For i =  myRestrictItems.Count To 1 Step -1
		myRestrictItems(i).Move myFolder.Folders("Processed")
	Next
End Sub

Open in new window


You can also use the SetColumns method to improve performance when looping through the returned collection by caching the required properties for much faster access:

myRestrictItems.SetColumns ("Subject, Body")

Open in new window


The rest is simple string processing and writing data to a desired range in Excel. If you are not familiar with VBA then use the Macro record function to manually perform a task and then check the code generated. This is quite helpful as a quick way to determine which objects, collections, methods and properties you need to use. MSDN also has full reference for all of the Office applications: http://msdn.microsoft.com/en-us/library/bb149081(office.12).aspx


Avatar of JaseSt

ASKER

also, please be aware my Outlook folder structure is attached User generated image
And sncameron, thank you, but I prefer someone do the solution for me rather than telling me how to do it.
Avatar of JaseSt

ASKER

sncameron

Do you think you can do this for me rather than me having to figure it out?
I can certainly write it, it isn't particularly technically difficult, however unfortunately I don't have the spare time available to write it at the moment. If you would like it written (in VBA or as a Office plugin in C++, C# etc.) then I can do it for you on a commercial basis if you are interested and have some sort of budget available for development.
Avatar of JaseSt

ASKER

no, can't do that, thank you. so, guess I need to have another provide the solution for this quesiton
Avatar of Jacques Geday
You have not mentioned on what box of folder these emails land. I presume its in the folder support@soverigngoldcard.com in the Inbox ? if not pls mention where.
gowflow
Avatar of JaseSt

ASKER

Yes, they come into the Inbox under support@soverigngoldcard.com
Ok I will look into it but pls allow some time.  !!! :)
gowflow
Can you pls post 1 or 2 emails of this type so I can run tests to make sure it pulls data correctly. The more you can post the better. They need to be of tpye Outlook message format (*.msg).

1) Click on the message you have
2) Choose save as and select type Outlook Message Format (*.msg) save it to a folder
3) Do 1 and 2 for couple of messages (the more you have the better) to make sure all have same pattern and we are not missing anything as this is text trapping which usually can be very special as to trapping.
4) Zip the folder that contain the messages
5) Post it here.

1 question: When your messages land in the Inbox folder do you agree they are moved to an other folder when the program treats them ? so we always have this scenario:
a- if message still in Inbox --> Message not treated = message not recorded in Visa.xls
b- if message not in Inbox AND message in say VisaTransfered folder --> Mesaage imported in Visa.xls.

Pls advise
gowflow
Avatar of JaseSt

ASKER

1. Answer:
Yes, I would like them to be moved to this folder: 'EP Wires' - which is under the Inbox folder

Your logic looks good. File attached. Payment-Received-Emails.zip
2 small questions:

1)
You mention in your initial statement: When the first digit after From: is 4 then ... import the value in Col M (if it is EUR) Your file has USD in Col N shall I import USD in N ?

2)
In the sample email there is Visacom that does not have a visa number should I Import it leaving the credit card number blank or not import at all ?

gowflow
Avatar of JaseSt

ASKER

1. No, if the first digit after From: is 5 or if after Amount: it says USD, those emails are going to be imported into a different spreadsheet with a different macro.

2. If after the From: there is anything else besides a 4, just leave it. If it is a name such as Asiacom I will process it manually. If there is a 5 another script will handle it.
ok fine the version I have puts the EUR in col M and USD in Col N and ignore when there is no credit card. I will ignore also anything but 4.

Still finetuning couple of issues
gowflow
ok fine I forgot to ask you what do you want to do with the emails that were not processed ?
1- like the ones Asiacom (no card number)
2- like cards not starting with 5 ...

Should I transfer them to the requested folder or keep them there ?

Case you want to keep them there pls note that they will pop-up or be analysed each time you re-run the program ! just keep this in mind. You mentioned you have an other routine that takes care of card 5 maybe your running into difficulties (I am refering to the other question that is pending) due to several macros made by diffrent people that could mayb badly interact !!!!

Pls advise as ready to send you the version
gowflow
oopps typo in last thread
you should read
2- like cards not starting with 4 ...

and not
2- like cards not starting with 5 ...

gowflow
Avatar of JaseSt

ASKER

Just leave those not processed in the Inbox and I will handle them manually and move them to a separate folder.
I have another expert trying to get those Payment Received emails that have a card number beginning with 5 into another spreadsheet and into specific tabs. I don't think there will be a problem with conflicting codes as emails with a card number starting with 5 are going to be filtered in Outlook to a folder named 'to update' and then I will run his code.
with all due respect to others after seeing my code,I think you will end up using my code !!!
Enjoy !

I'll let you discover it ... pretty simple.
gowflow
Visa-Test.xls
Avatar of JaseSt

ASKER

how do I input the folder for both selections? When I click on the drop down box, nothing is listed. It's blank. Is it supposed to open up an Outlook file structure I am then to choose from?
Avatar of JaseSt

ASKER

are you there gowflow? It would be great to finish this off. It seems we are so close.
Am surprised, nothing is listed in the dropdown boxes ? it should list your outlook folders did you get some error ? are you sure that your macro security is allowing the macro to run ?
gowflow
Avatar of JaseSt

ASKER

I just redownloaded your Visa-Test.xls and saved it and still nothing in  the drop down boxes. They are blank. No error comes up at any time no matter what I click on. And yes, Macro security is enable all macros.
Yess sorry my mistake you need to add access to Outlook Library 12 so that the macro could run access to Outlook. Totally forgot about it. If your not familiar with adding library do the following:

1) Load Visa-Test.xls (presume your running Excel 2007)
2) locate the Developper menu on the main Menu (it should be the last menu to the right) if it is not there, then click on Office button on the Top Left corener and choose Excel Options
3) From the Tab Popular the third item is Show Developper Tab in the ribbon click on it. Press OK
4) Now Developper menu should be appearent click on it and press on Visual Basic the first icon to the left.
5) From the menu Tools select refrences
6) Locate in the List the item "Microsoft Outlook 12.0 Object Library" select it then press OK
7) Save the file Exit
8) Run the file again and it should be ok now.

Pls advise
gowflow
Avatar of JaseSt

ASKER

Wow! It worked perfectly.

To have this update my Visa.xlsm what do I do?
mmmmm grrr !!! what is Visa.xlm ????
Avatar of JaseSt

ASKER

that's the real spreadsheet where the emails get imported into. What you used was an example. I suppose I could just move my sheets over to your example and it would work?
Exactly .... but make sure the name of the spreadsheet is the same as the sample you gave ie
Wire-Staging-FBME

Pls confirm all ok as anciously susspicious with you nothing is working !!!
:(
gowflow
Avatar of JaseSt

ASKER

I can't move or copy the sheet over as a sheet because it say I have too many rows and columns (see screenshot).
So, when I copied the data within my sheet and pasted in yours that worked however I noticed there is no vertical scroll bar in your spreadsheet that would enable me to scroll down the sheet. User generated image
Avatar of JaseSt

ASKER

Now, I'm sorry to report, for some reason, this code is not working.
Please see attached, but when I close out excel completely, download your Vista-test.xls and run it, I get the attached error message with the code highlighted in yellow:

objMail.Move objFolderToTransfer

Shutting it down, closing it out and trying it again, produces the same error.

It worked before so not sure what is now happening.
 User generated image
Avatar of JaseSt

ASKER

And, when I run the code again, I get another error, with this in yellow

Set VisaItems = objFolderToMonitor.Items.Restrict("[Subject] = 'Payment Received'")
Avatar of JaseSt

ASKER

User generated image User generated image
Ok I saw all the past threads and I think the major issue is that you are using Excel 2007 and the file I posted was Excel 2003. So pls use this version of the file and do the following:

1- To import your data to this new version
1) Disable macro before you start this workbook. go to the Office Icon on top let corner and choose Excel Options then Choose Trust center then Trust Center Settings Then click on Macros Settings and click on Disable All Macros with notifications (2nd option) press OK and save the workbook and exit.
2) Open the workbook again you will notice below the ribbon it says now that Macros are diabled.
3) Open from within this file you current data file that you need to import the sheet.
4) Rename the present sheet if it has the same name to 'Wire-Staging-FBME old'
5) Go to the sheet you need to import right click on the sheet and select move/copy tick make a copy and choose destination the macro workbook press ok
6) the sheet now should be imported asis to you macro workbook. close your data workbook keeping the original sheet there as a backup
7) press on save in your macroworkbook that now contains 3 sheets Main, Wire-Staging-FBME and Wire-Staging-FBME old
8) you need to enable macros again: go to the Office Icon on top let corner and choose Excel Options then Choose Trust center then Trust Center Settings Then click on Macros Settings and choose Enable all Macros plress ok and save the workbook and close.
9) Start the workbook and try to see if all is ok.

Hope this will work.
Rgds/gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

I carefully followed all directions and every step given above, and enabled Macros, saved, closed and reopened, clicked on Import Emails and got a runtime error, with:

 objMail.Move objFolderToTransfer

highlighted in yellow. Nothing was imported. I have two Payment Received emails

As far as I'm concerned you don't have to move the emails to another folder. I can do that manually. If that will help.

Then closing the debugger and running code again, I get:

Set VisaItems = objFolderToMonitor.Items.Restrict("[Subject] = 'Payment Received'")

highlighted in yellow. So, it is exactly the same errors as before, it appears.

Attached,as you can see, are the two Payment Received emails sitting in my Inbox with one selected. User generated image
Avatar of JaseSt

ASKER

Also, there still is no vertical scroll bar - which of course is needed.
1)could you pls post these 2 items ?
2)Can you pls specify what the error says (the whole text)
3)where you able to import successfully items or never was able ?
4)where items imported moved to correct folder ?
gowflow
Pls try this version of the file. Can't seem to be able to reproduce your error.
gowflow
Visa-Test.xlsm
Sorry forgot about the scroll bars !!!
here it is
gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

Downloaded, saved, closed excel processes, reopened your file I saved, ran code, got same error code in yellow:  objMail.Move objFolderToTransfer .

Closed excel again without saving, reopened, ran code - this time gave me notice that one email was not imported. However, when I looked at the receiving spreadsheet, nothing was imported, but one of the emails was moved to the EP Wires folder. And then it ran and came up with the objMail.Move objFolderToTransfer  error. Then running again, I get the Set VisaItems = objFolderToMonitor.Items.Restrict("[Subject] = 'Payment Received'") error with nothing imported or moved.

So, to try again, I close all excel - open the file (which I renamed to OutlookToVisa-Test2.xlsm - if it matters) and I get the attached error popup:  User generated image and  User generated image
Ran again, just to try and get this error:  User generated image with this code error: User generated image
What else can I give you to get to the bottom of this?
Lets take it one step at a time:
1) did you try with the version posted in ID: 36165501 ?
2) when you say: Downloaded, saved, closed excel processes, reopened your file I saved, ran code, got same error code in yellow:  objMail.Move objFolderToTransfer .
what do you mean by 'ran code' ? Do you run something ? the file should automatically run when you open it no need to activate a macro or start anything .. the only thing you need to do is to select a folder from the dropdown then press on Import Emails Correct ?
3) When you start the program do you have outlookopened or closed ?
pls advise
gowflow
Avatar of JaseSt

ASKER

1. yes
2. ran code = clicked on Import Emails button - nothing started automatically after I selected the Outlook folders for each location. So yes, that is what I do. Open file. Select Outlook folder locations, if needed, the click on button.
3. Outlook is opened before I open the excel file
ok fine that's the correct way of doing.

Pls try the following:
1) put some emails in your Monitor folder
2) check your present worksheet to see what line is updated before you proceed with import like color the last line in the folder
3) Run Import emails
4) Report all errors/results that happens in sequence like what you do what system replies and which errors you get (no need for snapshots)
5) Pls open the visual basic editor choose tools/refrences and take a snapshot of the items selected maybe the problem is there.

I am not getting the type of erros you have I tried to run the program and did tests with the emails you send me and ran it for at least 35 times with no errors whatsover !
gowflow
Avatar of JaseSt

ASKER

This time I ran it and it displayed the below boxes and DID move the correct emails to the EP Wires folder, but it did NOT import the emails to the spreadsheet. User generated image User generated image User generated image
I am attaching a code that I use that does work, importing specific emails to the same spreadsheet but to a different tab. It works and maybe it can help you see something with what works with this code that is done differently than yours. I activate it by clicking a button and it never fails. Maybe it will help.
Sub Q_26900870()
Dim mai As Object
Dim olkApp As Object
Dim folderItems As Variant
Dim sortedItems As Variant
Dim rng As Range
Dim arrRows() As String
Dim arrRow() As String
Dim elem As Variant
Const olFolderinbox As Integer = 6
Const olMail As Integer = 43
    
    Set olkApp = CreateObject("outlook.application")
    Set folderItems = olkApp.Session.Folders("support@sovereigngoldcard.com").Folders("inbox").items
    folderItems.Sort "receivedtime", False
    Set sortedItems = folderItems
    
    ThisWorkbook.Sheets(1).Range("A1:F1") = Array("Receiver", "Date Received", "MCTN", "Sender", "Sender Location", "Amount")
'Receiver - date email received - MTCN - Sender - Sender Location - Amount
    For Each mai In sortedItems
        If mai.Class = olMail Then
            If InStr(1, mai.body, "MTCN", vbTextCompare) > 0 Then
                Set rng = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                arrRows = Split(mai.body, vbCrLf, , vbTextCompare)
                For Each elem In arrRows
                    If Left(elem, 1) = "-" Then
                        Set rng = rng.Offset(1, 0)
                    Else
                        If InStr(elem, ":") > 0 Then
                            arrRow = Split(elem, ":")
                            Select Case Trim(UCase(CStr(arrRow(0))))
                                Case "MTCN", "MTCN#", "MCTN", "MCTN#", "mtcn #", "MTCN #"
                                    rng.Offset(0, 2) = 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"
                                    rng.Offset(0, 4) = arrRow(1)
                                Case "AMOUNT", "AMT", "Amount sent", "TOTAL", "amount sent"
                                    rng.Offset(0, 5) = arrRow(1)
                                Case Else
                            End Select
                            rng.Offset(0, 1) = Format(mai.receivedtime, "dd mmm yyyy")
                        End If
                    End If
                Next
            End If
        End If
    Next
    rng.Parent.Range("A1:E1").EntireColumn.AutoFit
End Sub

Open in new window

Well if you hv your answer why opening this question then ?
gowflow
Avatar of JaseSt

ASKER

I'm not much into coding or altering code. Just not a coder.

So, if you wanted to adjust this code to work for what I need, that should work and I will give you the points.
Sorry the code works perfectly here and tried it several times if not over 30 times with no fail. I think 43 thread is way too much to spend for a question. You may delete this question if it does not suit you.
gowflow
I hate to leave any stone unturned. While trying to troubleshoot your issue that shows no sign of trouble on my pc I cam accross by coincidence on an other question a similar problem where a user was able to run successfully a routine on a workbook but when attached to other workbook it ran unsuccessfully and the reason was that maybe on other macors some programers used
Application.Enableevents = False
...
but forgot to use Application.EnableEvents = True at a later stage keeping other routine to accomplish unsuccessfully.

So bearing this in mind (actually in this workbook I did not use it, I here introduced it to force a possible missing done by other workbook/macros

I also modified the display of the results so they are more meaningful on what was porcessed.

Pls excuse my earlier anger as resulting from a care to deliver an excellent product that I was not able to do with you. Hopefully will reach a successful ending.

PS Pls always use the .xlm version and not the .xls as you are using Excel 2007

Rgds/gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

Thank you gowflow for your willingness to make this thing work. I understand your frustration. I would feel exactly the same. It is vexing and very difficult trying to fix something you can't have your hands on - however, we could remedy that if you want. You could work remotely on my computer by using this application: http://www.mikogo.com/

Let me know. We'd just have to agree on a time.

Ok, so I used your latest version, clicked on Import Emails and it DID import two emails out of five emails that met the criteria. Curiously, it imported the first and the last email but not the middle three that were in my inbox.

It did NOT move any of them over to the EP wires folder - however I'd rather that once imported the emails STAY in the Inbox. It makes it easier for me to make sure every email that was supposed to be imported was.

So, if you could take out that part of the code I think that is best and it seems that is where the error is because I still got the attached errors. User generated image User generated image
Avatar of JaseSt

ASKER

Then trying again I closed the debugger, saved the spreadsheet to my Visa folder, closed it.  reopened it and clicked on Import emails again and came up with the same error as before with this line from the code below highlighted in yellow:

Set VisaItems = objFolderToMonitor.Items.Restrict("[Subject] = 'Payment Received'")

FTransfer = Split(Mid(gstFolderToTransfer, 2), "\")
If Not SetTransferFolder(FTransfer) Then Exit Sub


Set VisaItems = objFolderToMonitor.Items.Restrict("[Subject] = 'Payment Received'")
'cnt = VisaItems.Count

For I = VisaItems.Count To 1 Step -1
    Set objMail = VisaItems(I)
    ' use Instr here to check subject or body
    'MsgBox objMail.Subject
    Body = objMail.Body
    ETime = objMail.ReceivedTime
    MaxRow = wsVisa.Range("B1").End(xlDown).Row
    st = ImportData(Body, ETime, MaxRow + 1)
    If st <> "" Then
        MsgBox ("Email From: [" & st & "] not imported")
        EmailNotMoved = EmailNotMoved + 1
    Else
        objMail.Move objFolderToTransfer
        EmailMoved = EmailMoved + 1
    End If
    
    'MoveEmail objMail
Next I

Open in new window

before we try a remote could you pls send me the 3 emails that were not imported
gowflow
Avatar of JaseSt

ASKER

how do you want them sent? in what type of format?
Well like you did before .msg in a folder and ziped
gowflow
ok here it is !!!

Revewing the code you posted I have a funny feeleing that the emails you have are from diffrent pattern and becoz I relied soley on the emails you sent me and covered all the possibilities didn't think it was necessary to have an error handling routine. I think I was wrong !! So I introduced an error handling routine to help us troubleshooot this.

Pls load this version and for now don't worry about the moving what I suggest is you create a destination folder new like a blank destination folder that you assign to the green area (Transfer) and put several emails in the Monitor folder and run it.

Now you will have errors in the format of msgbox I need you to take note of them so we can better troubleshoot this. Limit your emails to 3 or 4 where you feel that are causing the trouble like when you get the past error ... try to see which email he is reading !

We will get to it !
gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

won't show any of my oulook folders User generated image
Also, I have the emails in my Inbox. Is that where you want them to be for this test? Or, are you wanting me to create other folders for this purpose.
maybe the macro did not run. close all excel start again I didn't change anything in this routine.
You may keep them in the inbox pls send me the kulprit emails
gowflow
Avatar of JaseSt

ASKER

in what format do you want the emails?
well I already told you !!!! .msg in a folder then zipped like before
gowflow
Avatar of JaseSt

ASKER

Gives me the following message popping up 5 times: User generated image User generated imagethen gives me this message:
 User generated image
Checking the Wire-Staging-FBME tab I see it DID import all of them correctly, but did NOT move them to any folder (which is what I want anyhow)

Now, further testing, I put other emails in their with Payment Received but not Visa and it showed the following for those it did not import (which is good): User generated image User generated imageand then gave me the following message: User generated image
AND it DID import correctly the ones that needed to be and did NOT move the emails to another folder.

So, it seems to work? Dare we hope????
Avatar of JaseSt

ASKER

not sure why it is giving us the pop up : 'An object cannot be found', however.
yes actually it was here !!! I guess what happened is we released the first 5 pop up and I need to see where this is happeneing so do the following:

re-run the program and when you get the error:
The operation failed. An object cannot be found
do the following:
before you press ok press FN and break if you have a notebook or CTRL Break nothing will happen until you click on OK then the code will popup at the line in the Errorhandler called
Resume Next (in yellow) so here press F8 you will see an other line of code in yellow then pls give me a snapshot of the code when you are there.
gowflow
pls try this version but first report on the last thread
gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

Curious. Pressing Fn and F12 (pauseBreak key) does nothing even after clicking on OK. Tried Ctrl F12 and still nothing. It still goes through the object cannot be found about four times and then comes up with this, which I haven't seen before: User generated image
Also strange, it moved two emails to the EP Wires folder but did NOT import either of them to the spreadsheet.  Five others it did correctly import and left them in the Inbox. Now will try the new version you have posted.
Avatar of JaseSt

ASKER

This one came up with a couple object could not be found boxes then a series of notifications that certain emails were not imported (which is good as they weren't supposed to) then more object not found popups (which I tried Fn Break and nothing happened), then a summary: User generated imageand all the emails were properly imported and none moved.
ok you have not send me the emails !!! pls do and when you run this pgm do you have an other excel opened ? like an other macro other than this workbook ? one more try runnning this program while outlook is closed !
gowflow
Avatar of JaseSt

ASKER

Payment-Received7.zip

Okay, ran the program making sure all excel workbooks are closed. Gave exact same results.

Closed outlook and ran again. When clicking Import Emails it gave similar results, but had this pop up more than once: User generated image but did import six of the seven emails correctly, and the one it did not import it moved to the EP wires folder. None others were moved
I imported all the emails successfully !

2 questions:
1) what are the refrences you have for this file take a snapshot of visual basic choose menu tools then refrences and post it here.
2) Please check that you have all windows update for office installed on your pc

You seem to be running issues with outlook that could be due to lack of updates or wrong refrences or at the end maybe will need to run diagnostics on your office but will leave this till the end.
gowflow
Avatar of JaseSt

ASKER

User generated image
I use IMAP to connect to our Gmail account. Not sure if that causes a problem.

I do update my Vista (which I can't stand) when I get update notices.
OK pls run this version and it will put a trace on the screen once done save it and post the whole file here so I can take a look
Tks gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

Ran, giving a lot of 'object could not be found' popups. And it brought in emails from I don't know where as they weren't in the Inbox and also seems like it duplicated a number of entries. Visa-Test.xlsm
Well I have a feeling that we have a problem !!!!!
pls send me the following emails that I see trace of in the file sent (save them as .msg and put in a folder and zip them) these we Moved but should not !!!!

Fwd: Fw: Proof of payments_21810Q to load card
RE: missing funds
RE: missing funds - Steven Lamb
EP EUR
Fwd: EP USD
more updated USD deposit list from EP for today

gowflow
Avatar of JaseSt

ASKER

with RE: missing funds - Steven Lamb there were seven different emails attached the one of that was in the EP Wires folder.
With EP EUR there are over fifty. I attached one of them.
With Fwd: EP USD there are fify. I attach one.

And with all these, they were NOT located in my Inbox, All of them were in the EP Wires folder. Emails.zip
what do you mean they were not located in your inbox they were in EP Wire ?
They were not previously in Inbox and the program moved them to EP Wire ?

The program will not 'read' from EP Wire it will read from Inbox ! pls clarify your statement
gowflow
Pls load this version and try some mails it has more traces
rgds/gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

to answer your first questions:

Yes, the emails I sent you, that you asked for, were NOT in my Inbox and WERE in my EP Wires folder. The program did NOT move them to EP Wires. They were already there long ago.
ok fine pls run this new version put 10 emails of all kind in inbox and run it save it and post it here
and once done pls tell me what are the remaining problems exactly.
gowflow
Avatar of JaseSt

ASKER

hold on - that may not be correct - let me check with your new version
Avatar of JaseSt

ASKER

so very odd. Ran it the first time and it correctly brought in the right emails, but it duplicated one of them and moved the copy to the EP Wires folder.

Removed what it imported, saved it, closed it down, reopened and ran again and this time it brought in a whole slew of emails - some as far back as May - getting them from who knows where.

Attached is a screenshot of what is in my inbox so you can see there are only nine in my inbox: User generated image
Shot of EP Wires - three were moved over but they are DUPLICATES: User generated image
You can see what they brought in from the attached file. Visa-Test.xlsm
duplicates !! rings a bell I got also duplicates but thought the mails were actually duplicates. I will look closer to it and will revert

Is it working or do u still get errors ?
gowflow
Well !!! You were absolutely right and it was not an easy one to pull off !!! I think you will be happily satissfied (I truely hope so)

Enjoy !!
gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

Ran it and although it still gave me some object could not be found message popups, it imported the correct wires perfectly.

However, we really need to turn off the moving the emails to EP Wires. It is creating duplicates and sending the copies to EP Wires while leaving the originals in the Inbox. This cannot happen as it
could cause duplication on my part.

Please turn off moving of the emails. Just leave them in the Inbox.Other than the few messages that popup, which I suppose is not indicating anything serious, and my request to NOT have the emails moved to any other folder - it works perfectly.

Attached is the file after it ran.
Visa-Test.xlsm
These error are happeneing becoz you run the file I send you rightaway after changing the inbox ! What you need to do is the following:

1) Take the last file I send you (run it)
2) You will notice once it run that both folders are wrong (\\gowflow\Inbox\ImportInbox ...
3) change the boxes to the one you want  \\support@sovereigngoldcard.com\Inbox ....
4) SAVE THE FILE AND CLOSE IT (DO NOT RUN THE FILE AT THIS TIME)
5) Open the file again, it shows now the correct folders
6) Import Emails
7) Check if you still get the errors and if files moved correctly.


Why don't you want to move the file was not this the main purpose ? We can still remove this its not a big deal but first pls report on above. When its all correct you need to trust that it will do the job correctly.

gowflow
Avatar of JaseSt

ASKER

Hi Gowflow,

I had done you suggested before, but did it again and still got the 'object now found' popups.
It perfectly imported the emails it should and didn't import the one it shouldn't have. File attached

If you can remove the code that has it move the emails to EP wires that would be best? Why, because I need to verify each email got imported in to the spreadsheet and then I will move them over manually. Besides, it's copying them, not moving them, so we can just avoid that whole problem by having your code leave them in the inbox.

Now, if you can finish this up no later than Sunday noon - the 17th (USA Mountain time) that is what I really need and the Tally Up values question you're working on as well by then. Thank you. I"m very appreciative of your efforts. Visa-Test.xlsm
One last tweak try this version
gowflow
Visa-Test.xlsm
Avatar of JaseSt

ASKER

nothing changed. I really need it to stop duplicating the emails. And, isn't the moving the emails to the EP Wires folder (or trying to - really it's just copying them) causing the 'object cannot be found' popups?
Avatar of JaseSt

ASKER

I guess we can keep it so they move the emails to the EP wires folder, but I can't have duplicates. If we can't get rid of the duplication, then we should dump that part of the function. Thank you.
Avatar of JaseSt

ASKER

Hi gowflow. Any updates with this?
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JaseSt

ASKER

I see no difference. Same popups, still copied the emails and put them in the EP wires while leaving the originals in the Inbox, and imported perfectly those that needed to be.

Can you just get rid of the moving the emails to any folder and leave them in the Inbox?
I need to complete this today. Thank you.
Avatar of JaseSt

ASKER

Thank you gowflow for your untiring efforts to get this function to work. And although we didn't get it perfect, it works just fine for what I need.

To keep the duplication from happening, I decided to have the folder where the emails are moved be the Inbox. When I run it like that it says 'unable to move...' but that is okay. Main thing is that I got the emails to the spreadsheet which saves me a lot of time and decreases the potential for error.

Thanks again, gowflow. You showed tremendous determination, professionalism and an un-quitting attitude to see this project done and done right. My hats off to you. It wasn't at all easy and you did a great job! Thank you!
Tks for the nice comments and the grade. It is so frustrating to troubleshoot an error you don't see. Anyhow this version here attached does not move the emails so you should not get errors. Pls kindly try it and revert if indeed it does the job without moving and without errors.
Rgds/gowflow
Visa-Test-nomove.xlsm
Avatar of JaseSt

ASKER

Excellent, gowflow! Works perfectly.

However, when I copy my original sheet, Wire-Staging-FBME, to Visa_test-nonmove.xlsm (first renaming the test version to Wire-Staging-FBME-old) it imports correctly, but to the Wire-Staging-FBME-old sheet and NOT to my Wire-Staging-FBME sheet.

How do I correct it so that it imports to my sheet, Wire-Staging-FBME ?

Does it have something to do with the sheet number? I noticed the test Wire-Staging-FBME page is sheet 9 in the VB code window and my imported sheet is numbered Sheet 10.
Avatar of JaseSt

ASKER

You there, gowflow? Can you give a tip here on how to fix this?
pls post the workbook here and I will check it out
gowflow
Avatar of JaseSt

ASKER

Just tried it again and it imported to the old sheet.
 Visa-Test.xlsm
Yes ok I fixed that actually it was writing in the correct file but not at the end of the file.
I noticed that the layout of the old file and the new file are not the same

           OLD file         NEW FILE
A  Date Submited     Date Submited
B  Date Wire            Date Wire
C  Hidden                 Cardholder
D  Hidden                 Card Number
E  Hidden                 Currency
F  Cardholder Name Load Euro
G  Card number        Button
H   Currency             Individual Transfers
I    Load Euros           Deposit
J   nil                         Remited Euros
K  nil
L   Deposit
M  Remited Euros


So I guess you need to decide what format you want

gowflow
Avatar of JaseSt

ASKER

Yes, I had three columns hidden (c, d and e) - forgot about that - and I don't need them so I'd rather delete them and go with the New File format. So because of that it wrote the email entries to the Wire-Staging-FBME-old sheet rather than the Wire-Staging-FBME sheet?
Ok here it is. Pls chk it and let me know if its ok.
By the way there was a problem in locating the last row in the file that is now fixed. The data that you imported was imported in Wire-Staging-FBME  but not at the end of the file in the middle somewhere and this is due to the fact that your data is not in 'table format' it has blank cells in there which is confusing when locating last row. Anyway this version has addressed this problem. !!!
gowflow
Visa-Test-110727.xlsm
Avatar of JaseSt

ASKER

Works perfectly, gowflow. Thanks for doing that.

Would you like to try your hand at a very similar project (a question another expert abandoned) where an almost exact email (only difference is that in the body it says: Amount: USD  instead of Amount: EUR) needs to be imported into another spreadsheet.

The difference is that the email gets pasted to a specific tab (EXCEPT TABS THAT ARE PROCEEDED BY 'MC' IN THE TAB LABEL) in the workbook where in the 'From:' section (From: 5116830000255555 Sherwin Tuyor Berongoy) the card number = the card number in cell D2. I have over a hundred tabs that have a credit card number in cell D2.

A problem the other expert was having is that when I paste the amount in a sheet there is another function that is kicked off, populating other cells and that was conflicting with his code. Truth be told, that function that is kicked off doesn't work sometimes and needs to be rewritten, so I'd like that to be worked on as another question I could give you - and I'd throw in another aspect to this that would give you at least 3 questions in one. Let me know. The question is here: https://www.experts-exchange.com/questions/27106871/Excel-2007-Need-to-extract-content-from-Outlook-to-specific-spreadsheets.html -

I could start off fresh and ask this as a related question if you are interested and would like me to do that. Please let me know. It's a project even more important than this last one you did for me.
ok willing to help you but the thread is soo long in that question. And  the Expert has already worked on it so it would not be fair/ethical  to come and cut him off. don't really know how to go about this coz I don't have a clear idea on what you want and afraid that the trouble I went thru with this question (emails not being moved) is actually being caused by some macros that other have developped that are cutting us in between.

I am willing to work on a question but need to know all files that interveen with the macro I developp so I / you don't waste precious time troubleshooting ghosts !!

gowflow
Avatar of JaseSt

ASKER

I've already submitted that question as abandoned and EE therefore changed zones to see if I would get any more help with it. So far, nothing, but the previous expert last posted to this question over 20 days ago, so I wouldn't worry about cutting him off. It's obvious he's just left it.

Again, with this one, emails don't need to be moved to another Outlook folder. I will do that manually.

What I can do is upload a stripped down version of the workbook I'm needing this work done for. I have over 100 tabs/sheets in the original, but maybe I can send you one with less so you have something to look at and work with. Yes, there is other code. I'll post the spreadsheet here, with all credit card numbers removed and names modified.

Just read my question and I think you'll understand what I want: https://www.experts-exchange.com/questions/27106871/Excel-2007-Need-to-extract-content-from-Outlook-to-specific-spreadsheets.html#firstComment  . That combined with what I described above.

It's similar to the one you just worked on, but as stated the email data needs to be put into separate sheets based on the card number. Let me know if and how you'd like to proceed.

MasterCard-Test.xls
ok no problem but seems you hv a circular error in the file can you locate where this comes from ? Pls post the final workbook you need me to work on (as it may be tricky for you to copy the modules as if I find errors I may correct them and cannot keep trace of all corrections) in here and I will answer in the original question. You seem to hv plenty of modules there which ok.

1 question
You need to only fill col F the amount ? what about other columns ??? pls state all you need as the original question states to fill Col F after the last row the amount. Is that only ?

gowflow
Avatar of JaseSt

ASKER

I don't know where the circular error comes from. It seems to be generated by another function, but have yet to isolate the cause.

I don't see how I can post the final (original) workbook without having to delete all credit card numbers and doing so makes it unusable for me. Must be another option.

answer:
Yes, F is all that is filled out and then when it is filled out another code kicks in - which I have to say sometimes works and sometimes doesn't and therefore needs work. If you could make them both work seamlessly, I would just add a related question and award you the solution.

The columns that get automatically filled in after a value is pasted into F are: G, I, K and L. If you paste a value in F it should do its magic and populate those columns, but as I say, it is problematic. However, that is what I need to work:

- pasting USD amount in Col F in the NEXT BLANK ROW following the last entry in Col J or K.
- When amount is pasted in Col F, it adds the text 'EP and today's date) to Col G
- then uses this formula to fill out Col I: =IF(F9*1.85%<25,F9-30,F9-(F9*1.85%)-5)
- adds today's date to Col K
- subtracts value in Col I from the value in Col F and enters that difference in Col L

Let me know if you need anything else. Thank you.
Pls chk original question
Avatar of JaseSt

ASKER

Experts Exchange Support:

For the ORIGINAL POST AND QUESTION AND the following comments replace ALL the spreadsheets uploaded for this question with the attached scrubbed excel file. Please do this for the following comments:

07/06/11 03:36 PM, ID: 36141594
07/10/11 03:54 AM, ID: 36161146
07/11/11 02:34 AM, ID: 36164757
07/11/11 05:56 AM, ID: 36165501
07/13/11 02:12 AM, ID: 36178672
07/13/11 10:06 AM, ID: 36181728
07/13/11 12:03 PM, ID: 36182607
07/13/11 03:29 PM, ID: 36184273
07/13/11 04:00 PM, ID: 36184457
07/14/11 10:56 AM, ID: 36189585
07/14/11 11:44 AM, ID: 36189976
07/14/11 05:03 PM, ID: 36192010
07/15/11 10:34 AM, ID: 36196407
07/16/11 07:37 AM, ID: 36200326
07/16/11 08:04 AM, ID: 36200387
07/17/11 02:26 PM, ID: 36204511
07/18/11 01:28 AM, ID: 36205884
07/21/11 01:49 PM, ID: 36232738
07/30/11 03:18 AM, ID: 36281087
07/31/11 12:01 PM, ID: 36285236 EE-V-Test.xls
Avatar of JaseSt

ASKER

Experts Exchange Support:

Please replace the zip files for this question with the attached for the following comments:

07/04/11 09:31 AM, ID: 36125384
07/13/11 02:00 PM, ID: 36183623
07/14/11 06:38 AM, ID: 36187499 Payment-Received-Emails.zip