Search outlook from excel vba and extract email info to excel

de_keda

New Member
Joined
May 22, 2015
Messages
8
I am working on an auditing project for work, and am an excel VBA newbie (fairly new, anyway). I am using Office 2013. With my project, I import a list of items to be audited, and have that part all figured out. I already have a userform that uses macros and can scroll through each new item one at a time, display the results, and extract the account number.
Now I need to search my outlook inbox for any email related to that account number, and display the results in the excel file. I can create another userform to display results, but I cannot find the code I need to search outlook and extract info from emails. I have several problems that need to be addressed.
1) There are two inboxes associated with my Outlook. The default personal business inbox (ex. email myemail@company.com), and an added team inbox (ex. xyz@company.com). I need the vba code to search one of the folders in the team inbox, let's call it "Audits" for simplicity.
2) The account number is already extracted to a specific cell (Sheet1 cell A2), but changes with each "new" item I audit. The vba code needs to search the subject line for the account number value shown in (Sheet1 cell A2) within the specified folder, and it will more than likely have multiple results. I need the code to extract the information from the email with the account in the subject, but then also show me how many emails it found.
3) The information I need to extract from each email, and to copy individually to separate cells in my excel workbook, is:
a) Sender
b) Full subject line
c) body of the email
d) how many emails have the account number in the subject line
e) what "number" email this is
I then need to be able to "scroll" or "go through" each of those emails (still in excel) to visually search the content and audit it. I don't know if I can extract all info from all found emails at the same time into different columns, and then use a separate macro to display them one at a time? Or if it would be easier to use a macro to find the 1st email, then use another macro for the "next" email or "previous" email and extract and overwrite the information in excel each time? Either way would work for me.
I don't need help with the userforms (at least yet, lol) or scrolling through information once it's in excel. It's the searching through Outlook, the right inbox, and extracting info from emails to excel that has me completely stumped. I've found a lot of code and examples of code to search through Outlook while googling it, some of it is intended for outlook's vba, and I can't get any of it to work. Please help, and many thanks in advance!!!!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,652
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top