Part 11 to: Import more data into spreadsheet
This is an update in functionality to question: Part 7 to: Import more data into spreadsheet -
https://www.experts-exchange.com/questions/28156647/Part-7-to-Import-more-data-into-spreadsheet.html
In addition to what is done with the macro created in Part 7 I need the function to do the following, if possible:
1. Currently it creates an email (WHEN a value is inserted in Col N AND there is a value in Col K) but it would be best if it could find the original email, open it and forward it with its contents, (including its attachments) to the same email address with same subject and body the macro created before.
2. When sent, insert today's date into Col P of Applicant Status
Is it possible to do #1 without knowing the subfolder the original email is stored?
If not, the original email would not be in the Inbox but in a subfolder named: USD Mastercard. The hierarchy goes like this: Inbox > Applicants> USD Mastercard
https://www.experts-exchange.com/questions/28156647/Part-7-to-Import-more-data-into-spreadsheet.html
In addition to what is done with the macro created in Part 7 I need the function to do the following, if possible:
1. Currently it creates an email (WHEN a value is inserted in Col N AND there is a value in Col K) but it would be best if it could find the original email, open it and forward it with its contents, (including its attachments) to the same email address with same subject and body the macro created before.
2. When sent, insert today's date into Col P of Applicant Status
Is it possible to do #1 without knowing the subfolder the original email is stored?
If not, the original email would not be in the Inbox but in a subfolder named: USD Mastercard. The hierarchy goes like this: Inbox > Applicants> USD Mastercard
ASKER
Well we could search for the email address from Col D AND the Date from Col A. The two would have to match. Or/And you could search for a match in an attachment file name.
Would any of those work?
Would any of those work?
attachment file Name !!!! mmmm brilliant BRAVO !!!
let me work on it.
gowflow
let me work on it.
gowflow
ASKER
just hope there aren't two attachments with the same file name. That's why I also suggest tying it to the email address and date - but I'll leave that to the master, or I mean, sage. :)
I just read the second part of the question:
and for sure need to know where it is located and will assume as you mentioned
Inbox > Applicants> USD MasterCard
If it is not there the macro will not find it.
gowflow
Is it possible to do #1 without knowing the subfolder the original email is stored?
If not, the original email would not be in the Inbox but in a subfolder named: USD Mastercard. The hierarchy goes like this: Inbox > Applicants> USD Mastercard
and for sure need to know where it is located and will assume as you mentioned
Inbox > Applicants> USD MasterCard
If it is not there the macro will not find it.
gowflow
ASKER
Well, I guess then that's where the email will have to be: Inbox > Applicants> USD MasterCard
anyway let me work on that it may take sometime ull need to be patient
gowflow
gowflow
ASKER
any update with this gowflow?
Sorry was off for couple of days will look at it.
gowflow
gowflow
ASKER
any updates, gowflow?
ASKER
you there gowflow?
Yes sorry again was overloaded with different issues. back to this issue, let me recap so I take it correctly.
Basically you click on a row in your excel then when an email is highlighted in outlook and you press on update elinks it import the data in corresponding columns from the email etc ... now you requested the reverse when you fill in certain columns instead of creating a new message to be sent like it was done in the past (part 7) you want the macro to find the original email and reply to it
Correct ???
gowflow
Basically you click on a row in your excel then when an email is highlighted in outlook and you press on update elinks it import the data in corresponding columns from the email etc ... now you requested the reverse when you fill in certain columns instead of creating a new message to be sent like it was done in the past (part 7) you want the macro to find the original email and reply to it
Correct ???
gowflow
ASKER
that is correct. I need it to find the original email so that the history of the order is in the body of the reply email which this function would allow for.
ASKER
I realized I need to add something to this:
The location of the original email for a row that has a value in Col K is: Inbox > Applicants > USD Mastercard
The location of the original email for a row that has a value in Col J is: Inbox > Applicants > Euro Mastercard
The location of the original email for a row that has a value in Col K is: Inbox > Applicants > USD Mastercard
The location of the original email for a row that has a value in Col J is: Inbox > Applicants > Euro Mastercard
what is the full path of the folders in outlook ?
like Personal Folders\Inbox ....
I need the full path.
Didn't the macro trigger the mail sending on Col K there was no mentioning of Col J at any time. Is this new now ?
gowflow
like Personal Folders\Inbox ....
I need the full path.
Didn't the macro trigger the mail sending on Col K there was no mentioning of Col J at any time. Is this new now ?
gowflow
ASKER
Attached you can see the folder structure and there you see Euro Mastercard and USD Mastercard folders. Does this explain it well enough?
Yes, the Col J is new.
Yes, the Col J is new.
Not enough this snapshot.
Do this:
Open your former Visa or MasterCard excel file previously developed then once you activate macros in the sheet Main you have the 2 dropdowns green and blue Folder to Monitor etc ...
Click on any of the 2 dropdown then it will list all your outlook folders
Select the USD Mastercard folder and then copy paste the full path that is shown in the dropdown
something like \\....\Inbox\USD Mastercard
I need this full path for both the USD and the EURO folders.
after that just close the excel file without saving no harm will be done.
tks/gowflow
Do this:
Open your former Visa or MasterCard excel file previously developed then once you activate macros in the sheet Main you have the 2 dropdowns green and blue Folder to Monitor etc ...
Click on any of the 2 dropdown then it will list all your outlook folders
Select the USD Mastercard folder and then copy paste the full path that is shown in the dropdown
something like \\....\Inbox\USD Mastercard
I need this full path for both the USD and the EURO folders.
after that just close the excel file without saving no harm will be done.
tks/gowflow
ASKER
\\support@sovereigngoldcar
and
\\support@sovereigngoldcar
and
\\support@sovereigngoldcar
ASKER
how goes it with this one, gowflow?
ASKER
any news gowflow?
ASKER
gow flow are you okay? Are you on vacation? Is everything ok?
Hi there
Sorry for this loooong vacation and left you with no news will look at this asap and good you mentioned both full path
\\support@sovereigngoldcar
and
\\support@sovereigngoldcar
as feel will need to hard code this. You will need to remember that if you change the location of these emails one day and have them in a different folder then your macro will not find the original emails you will need to modify the code accordingly to point in the new location.
As you must hv noticed I usually do not like this approach but in this case I do not see how we can go around this without too much coding and to get you to the result immediately.
If all this is fine with you then I will go ahead and build the macro accordingly.
Waiting for your feedback and sorry for leaving you with no news that long.
gowflow
Sorry for this loooong vacation and left you with no news will look at this asap and good you mentioned both full path
\\support@sovereigngoldcar
and
\\support@sovereigngoldcar
as feel will need to hard code this. You will need to remember that if you change the location of these emails one day and have them in a different folder then your macro will not find the original emails you will need to modify the code accordingly to point in the new location.
As you must hv noticed I usually do not like this approach but in this case I do not see how we can go around this without too much coding and to get you to the result immediately.
If all this is fine with you then I will go ahead and build the macro accordingly.
Waiting for your feedback and sorry for leaving you with no news that long.
gowflow
Let me get more clarifications here.
Looking at the routine, when value in Col N is added and there is something on Col K (you want to also add now Col J for EURO but it goes in the same principle) then what the macro is currently doing is that:
1) It create a new xls file called [Last Name space First Name - New Card Load.xls] with data filled in Col D, H, I
2) It create an email with a specific script and attached the previous file created and send it to a specific email address.
So now I need to know specifically what you want to do as we are depending on K or J locating the initial email received then what do we do ??
1) Do we still create the file as in 1) above and added to that email found and send it with all the details already given in item 2) above
OR
2) We just find the original email and .... do what ??? still not clear to me
Pls advise.
gowflow
Looking at the routine, when value in Col N is added and there is something on Col K (you want to also add now Col J for EURO but it goes in the same principle) then what the macro is currently doing is that:
1) It create a new xls file called [Last Name space First Name - New Card Load.xls] with data filled in Col D, H, I
2) It create an email with a specific script and attached the previous file created and send it to a specific email address.
So now I need to know specifically what you want to do as we are depending on K or J locating the initial email received then what do we do ??
1) Do we still create the file as in 1) above and added to that email found and send it with all the details already given in item 2) above
OR
2) We just find the original email and .... do what ??? still not clear to me
Pls advise.
gowflow
ASKER
Hi gowflow. Welcome back and thanks for addressing this question again. Hope you had a restful and fulfilling vacation.
In regards to your questions:
"1) It create a new xls file called [Last Name space First Name - New Card Load.xls] with data filled in Col D, H, I"
Would prefer [First Name space Last Name - New Card.xls] but otherwise correct
"So now I need to know specifically what you want to do as we are depending on K or J locating the initial email received then what do we do ??"
Yes, we still create the file as in 1) and add the script to the email found and send it with all the attachments originally sent which is probably easier than relocating those files saved and inserted into cols E, F, G and H.
Any clearer?
In regards to your questions:
"1) It create a new xls file called [Last Name space First Name - New Card Load.xls] with data filled in Col D, H, I"
Would prefer [First Name space Last Name - New Card.xls] but otherwise correct
"So now I need to know specifically what you want to do as we are depending on K or J locating the initial email received then what do we do ??"
Yes, we still create the file as in 1) and add the script to the email found and send it with all the attachments originally sent which is probably easier than relocating those files saved and inserted into cols E, F, G and H.
Any clearer?
yes just to rap up and say it in my own technical words.
once Col N has a value and Col K or J then
1) create a new xls file called [Last Name space First Name - New Card.xls] with data filled in Col D, H, I
2) Find the original Email and forward to same email received from (and not Reply to as in this case you would loose attachments)
3) Put in this new email the text as outlined in the previous macro
4) Attach this new file created on top of the existing ones already in the original email
5) Display this new email for you to send manually as already in previous macro.
Please confirm or amend where you deem necessary.
gowflow
once Col N has a value and Col K or J then
1) create a new xls file called [Last Name space First Name - New Card.xls] with data filled in Col D, H, I
2) Find the original Email and forward to same email received from (and not Reply to as in this case you would loose attachments)
3) Put in this new email the text as outlined in the previous macro
4) Attach this new file created on top of the existing ones already in the original email
5) Display this new email for you to send manually as already in previous macro.
Please confirm or amend where you deem necessary.
gowflow
ASKER
comments and changes below:
1) CORRECT BUT NO NEED TO CREATE THE NEW XLS FILE IF VALUE IN COL J - ONLY IF IN COL K - AND PLEASE CHANGE THIS:
create a new xls file called [Last Name space First Name - New Card.xls]
TO THIS:
create a new xls file called [FIRST Name space LAST Name - New Card.xls]
2) Find the original Email and forward to same email received from (and not Reply to as in this case you would loose attachments) - CORRECT, BUT YOU ARE NOT FORWARDING TO SAME EMAIL RECEIVED FROM - YOU ARE FORWARDING WITH THE BELOW PARAMETERS:
IF VALUE IN COL J THEN FORWARD EMAIL TO: david@offshorelawcenter.co
IF VALUE IN COL K THEN FORWARD EMAIL TO: nmai@banking.bz
3) CORRECT FOR VALUE IN COL K - BUT IF VALUE IN COL J PUT THIS SCRIPT:
"Funds received. Euro MC order."
4) CORRECT - BUT IF VALUE IN COL J NO NEED TO CREATE NEW XLS FILE
5) CORRECT
6) PLEASE ALSO HAVE FUNCTION DO:
- PUT TODAY'S DATE (OR X) IN COL P (it automatically inserts today's date now when x is inserted)
thank you!!
1) CORRECT BUT NO NEED TO CREATE THE NEW XLS FILE IF VALUE IN COL J - ONLY IF IN COL K - AND PLEASE CHANGE THIS:
create a new xls file called [Last Name space First Name - New Card.xls]
TO THIS:
create a new xls file called [FIRST Name space LAST Name - New Card.xls]
2) Find the original Email and forward to same email received from (and not Reply to as in this case you would loose attachments) - CORRECT, BUT YOU ARE NOT FORWARDING TO SAME EMAIL RECEIVED FROM - YOU ARE FORWARDING WITH THE BELOW PARAMETERS:
IF VALUE IN COL J THEN FORWARD EMAIL TO: david@offshorelawcenter.co
IF VALUE IN COL K THEN FORWARD EMAIL TO: nmai@banking.bz
3) CORRECT FOR VALUE IN COL K - BUT IF VALUE IN COL J PUT THIS SCRIPT:
"Funds received. Euro MC order."
4) CORRECT - BUT IF VALUE IN COL J NO NEED TO CREATE NEW XLS FILE
5) CORRECT
6) PLEASE ALSO HAVE FUNCTION DO:
- PUT TODAY'S DATE (OR X) IN COL P (it automatically inserts today's date now when x is inserted)
thank you!!
ASKER
Hi gowflow. Any update with this?
ASKER
Gowflow are you on another vacation?
ASKER
Heloooooo... gowflow you there?
ASKER
gowflow... you gone?
well had tough times lately and didn't hv a minute to look in here. And so busy I forgot about qualifying and now in deep sh.... as need to qualify to continue in here.
I know it is not your problem
I will see what I can do
gowflow
I know it is not your problem
I will see what I can do
gowflow
ASKER
Hi gowflow. Hope everything is ok with you. Have you had any available time to work on this?
ASKER
Hi gowflow. Just checking in
ASKER
gowflow. are you okay?
ASKER
gowflow... what's happening?
well sorry to say u hv been overlooked but looking at this question it is rather long reason why I am not giving it time. Too much work on it. Sorry
gowflow
gowflow
ASKER
that's okay. I can do without it but I do have others if you're interested.
ASKER
I actually am having a problem with Importing WU emails into the Visa spreadsheet.
It is not working with some emails for some reason.
It is not working with some emails for some reason.
ok fine post the question and I will look at it but do not guarantee I am ready to put the amount of 'FREE time' I used to put.
gowflow
gowflow
ASKER
I've requested that this question be deleted for the following reason:
don't really need the answer anymore. changed how I'm doing things
don't really need the answer anymore. changed how I'm doing things
ASKER
it's okay, go ahead and delete
ASKER
pls chk my reply on the new question
gowflow
gowflow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I would like to award gowflow points for this question as he put a lot of time in it so I am asking to NOT have this question deleted.
Thank you for the full points which is more than what I deserve as although worked on this question extensively, still did not provide a solution for you. Hope to be able to compensate you better in future questions should you need my help.
Rgds/gowflow
Rgds/gowflow
Well you need to provide items to look for that specific email like Subject date sender etc ... what are the items that exist in the file that if we use them could match the email and find it ?? You cannot use fields like first/last name as these are not in the email but rather in the attachment
In other words how you do the job manually to look for the email, this could be hints for us to build a search criteria
gowflow