Outlook Automation

Outlook Automation 


To export emails to ExcelSheet by using Outlook macro and Excel macro. Basically, the logic of the codes of the two are similar. The only major differences is using different references. When you run macro in outlook, you need to Excel application references. On the other hand, if you run macro in Excel, then you need to outlook references.

1. Make sure macro security setting is set properly.
File -> Options -> Trust Center -> Click “Trust Center Settings” -> macro setting -> Check “Enable all macros”

2. Add Developer tab on outlook menu
File -> Options -> customize ribbon -> check “Developer” mini tabs on the right.

3. Install macro on Outlook
a. Download “ExportToExcel.txt” or copy the code from the code window below, paste and save it as “ExportToExcel.bas”
b. Go to macro editor mode by pressing alt + F11 and import the “ExportToExcel.bas”.  File -> import file -> Browse and select the file you just download

4. Add reference: Tools-> References
->“Microsoft Excel 16.0 Object Library”
-> “Microsoft Scripting Runtime”

5. Add macro icon to outlook toolbar
File -> Options -> customize ribbon -> click “New Tab”
Once New Tab (Custom) created, click “Rename” to rename new tab to meaningful name. Example: Macro.
Rename tab group to whatever group name you want and choose whatever icon you want. Example: Custom Macro Group
Insert macro to the Custom Macro Group. Under “Choose commands from”, select “Macros”, select “Project1.ExportToExcel” and click Add button
Rename Macro to any meaningful name and choose the icon.  Select macro and then click “Rename” button

6. Run macro, click “ExportToExcel” icon on outlook toolbar


Outlook Macro


Excel Macro

To run this from Excel. Add reference: Tools-> References ->“Microsoft outlook 16.0 Object Library”



Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)