Solved

Outlook search for items with certain category and copy items to new excel

Posted on 2013-01-04
20
258 Views
Last Modified: 2013-02-02
Hi, Can anyone help me with this one: I want to make a macro in outlook to search the inbox for emails classified with a specific category and then create a new spreadsheet in excel and copy some atributes of the matching emails (recepient, subject, etc).
regards !
0
Comment
Question by:mayamp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
20 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 38746543
yes sure. why would you do the macro in Outlook ? We can do it in Excel to read emails in outlook would that be fine with you ? and second we need to know what is the mail folder you want to search for the Inbox ? or an other ? if an other I need the full path of your folder or a snapshot of the folders from outlook. Then I need to know what is the category or maybe is it easier to find the email thru a specific subject ?
Will wait for your reply to ellaborate more. Don't give up there is a solution for you ... see this msg was left out as neglegted.
gowflow
0
 

Author Comment

by:mayamp
ID: 38750656
Hello, thanks for the help.
I was thinking in outlook because that report would be more direct (for example a button in the tools ribbon)  when I'm checking the inbox for new mails. The macro in excel directly also would be great !. The folder could be the default Inbox or a Personal folder: "Jan 13 Personal"
The Category is "To-do". I prefer to assign categories instead of modifying the subject.


Thanks
regards,
mayamp
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38750769
So what is it now Outlook or Excel ?
I personally perfer Excel as when we are in Outlook will need to know what file to save info in and where to be located and we add more info to remeber the file and updated it is a bit more than what this question could hold. If you insist on Outlook I do not hv a slight problem in the countrary I hv developped solutions in outlook but then lets agree in splitting this into 3 parts:

1) first being the mechanics behind selecting folders to monitor
2) second being the setup of Excel file to save data to and hv it remebered by outlook so everytime you run outlook it has the settings for that file and will open it and append data to it after last record there (all this done thru registry)
3) thrid the engine which is finding the emails in in the specific folders that belong to specific categories and saving the required fields.

So your choice Excel or Outlook in both cases I suggest a 3 questions approach this way you have a solution that is quite flexible to you and allows you to change any time you want the folder to monitor and also hv the posiblility to choose the Excel file to update data to and change it whenever you want.

Let me know your though on this approach.
gowflow
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:mayamp
ID: 38753142
Thanks gowflow,
I would prefer outlook. I agree to separate the problem in 3 parts.

1. The folders to make the search are the Inbox and a personal folder named "Jan 13 Personal". This because some emails with the "to-do" category are in the default inbox and others are in the personal folder.
2. I don't need to keep record of last "queries" in the excel so its not necessary to use the same excel each time. When the report is run, I want the macro to create a new excel book and copy the email info to it. Then I can save it or delete the book after use.

regards
mayamp
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38753766
ok fine so you will need to remember the 3 parts as will use this question to deal with part 1 and you can once it is finished as a related question and put part2 and give it the coresponding exlanation and put this question as related. etc...
Will revert with first part later on
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38777910
Sorry for this laps due to being caught in several issues.

I am looking at the threads here and ready to provide you with something to start with but hv a small question first:

You say in your last post:
I want the macro to create a new excel book and copy the email info to it.

What is the 'info' you want to copy to the Excel ??? the body of the email ? the whole email ? certain fields can you be specific so I build it in hte first macro as it is ready but missing the specifics ??

Await for your answer.
gowflow
0
 

Author Comment

by:mayamp
ID: 38779592
Hello gowflow,
the fields I want to include are: sender name; date; subject

regards
mayamp.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38779617
ok great will back shortly and sorry again for this looong laps.
gowflow
0
 
LVL 30

Assisted Solution

by:gowflow
gowflow earned 500 total points
ID: 38780602
Ok here it is, I hope it meet your requirement. Open the file and follow hte instructions in the file.
You need to add a reference to Microsoft Outlook Object Library in vba tools menu select references and find this item tick it and close and save the workbook then try it enabling macroes.

Let me know results.
gowflow
Email-Extract.xlsm
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38787309
Any chance to hv tried the proposed solution ?
gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38797274
Again any chance to hv tried the solution ?
gowflow
0
 

Author Comment

by:mayamp
ID: 38812385
goflow, sorry for the delay in the response.
when running the macro, excel crashes at the following line code: "Set oApp = New Excel.Application"

regards
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38812483
replace that line
Set oApp = New Excel.Application

BY this line
Set oApp = CreateObject("Excel.application")

save the workbook and try again.
gowflow
0
 

Accepted Solution

by:
mayamp earned 0 total points
ID: 38826463
thanks goflow. It didn't work that way, but I made a little change that work. I replace
Set oApp = CreateObject("Excel.application") with just
Workbooks.Add
Set WBResult = ActiveWorkbook

thans a Lot !
0
 

Author Comment

by:mayamp
ID: 38827129
I've requested that this question be closed as follows:

Accepted answer: 0 points for mayamp's comment #a38826463

for the following reason:

The solution is from the expert, I made a little change in the solution that adapted better to my problem.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38826477
Sorry don't know if this was your intention or you simply did a mistake.
You closed that question awarding yourself the answer meaning 0 point for giving you a file that works with a full solution. Is that what you wanted or it was simply a mistake ?

gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38826590
mayamp
I would appreciate your reply to my comment above as feel something is not correct here in the closing of this question.

gowflow
0
 
LVL 30

Expert Comment

by:gowflow
ID: 38827130
Sorry to ask for Moderator's intervention in here as feel somehow that the asker did not make the correct choice in closing this question. I provided a full working solution and somehow something did not work for him and substituted 1 line by an other and closed that question as  you can see with Zero points.

Would appreciate you ask asker if this was his real intention as have put 2 comments and he did not reply.

Tks your attention.
gowflow
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video teaches viewers about errors in exception handling.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

724 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