Grab email - copy subject - download xls attachment rename attachment to subject line

I have done sending emails automatically in code - very easy - however now I need to do something i'm sure can be done but don't know how.
I will have numerous daily emails sent to a specific folder in outlook. I need to automate in a button to go thru each email in this specific outlook folder - grab the subject line (which is the "Real" file name) and then download that xls file to a folder on my c drive and rename that file with the subject line, then repeat this for every file in the folder until done.
If possible i would also like to move the email to another outlook folder so I know whats been processed and whats new but if that's not possible then i can live with that. Any ideas or help with that kind of code?
So you know the reason I have to rename the files is because every file that comes in has the same name to it and the subject line is really what the file name is and I dont want to overwrite the file as it downloads to the c drive folder.
Stephen RoesnerAnalysisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Do you mean to save the attachment if you talk about "downloading"?
Is there a single file for each mail only?

What you ask for is possible, if above is all true. However, I would perform the action triggered by adding mails to that specific folder.  But using a button isn't more difficult either.
Moving the processed mail to another folder is easy.

Why did you choose "MS Access" as additional topic area? Does that have a special meaning here? Because I would code anything in Outlook VBA only.
0
Stephen RoesnerAnalysisAuthor Commented:
yes I need to save the attachment to a folder and rename it the subject line.
yes there will only be one file to each email.
I will be using an access db therefore access vba to accomplish this.
so my process would look like this:
come in in the morning
open the access db
click the button and it will go to the incoming outlook folder
save the xls file to a c drive folder
rename it the subject name
move that email to a processed named folder
then go to the next email and repeat till the folder is empty.
Does that make sense to you? Can that be done? or are you saying you can code in outlook to do this ?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Doing in Outlook is easier. Using Access VBA or Word VBA or Excel VBA makes no difference, but we need Office Automation. Yes, it is feasible. I'll follow up with a code sample, but will need some time to compose it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
A very simple code fragment, using only late binding (so you do not need to add references, but have no object info in the compiler):
Option Explicit

Const olFolderInbox = 6
Const attDestination = "C:\Temp\EE"
Sub SaveAttach()
Dim olk, itm, att
Dim bMove
  Set olk = CreateObject("Outlook.Application")
  For Each itm In olk.Session.GetDefaultFolder(olFolderInbox).Items
    bMove = False
    For Each att In itm.Attachments
      If UCase(Right(att.Filename, 3)) = "XLS" Or UCase(Left(Right(att.Filename, 4), 3)) = "XLS" Then
        att.SaveAsFile (attDestination & "\" & itm.Subject)
        bMove = True
      End If
    Next
    If bMove Then itm.Move itm.Parent.Folders("Processed")
  Next
End Sub

Open in new window

The code assumes the Processed folder is located in Inbox, and the subject contains the complete file name (including XLS/XLSX/XLSM).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen RoesnerAnalysisAuthor Commented:
Thanks I will test this monday is this code meant to be behind a button in access or in outlook ?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Access. Excel, Outlook, ... - doesn't matter. I tested with a new and empty Excel workbook.
0
Stephen RoesnerAnalysisAuthor Commented:
Sorry everyone for ma slow response. Bosses have flown in for a 3 day conference and I havent had time to work the code. Please be patient.
0
Stephen RoesnerAnalysisAuthor Commented:
Please be patient I am trying to get to this I hope to test this this week
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.