Saturday, April 6, 2013

Export or Save Outlook Email as word document using VBA

If you want to loop through all emails in your outlook inbox and save them as word document in a folder . Try this macro:- 

Option Explicit

Sub sample_macro()
    'reference -> microsoft outlook
    Dim oitem As Outlook.MailItem
    Dim ol As Outlook.Application
    Dim olns As Outlook.Namespace
    Dim oinbox As Outlook.Folder
    Dim j As Long
    ThisWorkbook.Sheets(1).Range("a2:d" & ThisWorkbook.Sheets(1).Range("a1048576").End(xlUp).Row + 1).Clear 'clear existing data if any
    Set ol = New Outlook.Application
    Set olns = ol.GetNamespace("MAPI")
    Set oinbox = olns.GetDefaultFolder(olFolderInbox) 'select the inbox
    Set oinbox = oinbox.Folders("Ashish Koul") ' select if you want to choose any specific folder
    oinbox.Items.Sort "[ReceivedTime]", True
    j = 2
    For Each oitem In oinbox.Items ' loop outlook emails
        ThisWorkbook.Sheets(1).Range("a" & j).Value = oitem.SenderName
        ThisWorkbook.Sheets(1).Range("b" & j).Value = oitem.Subject
        ThisWorkbook.Sheets(1).Range("c" & j).Value = oitem.ReceivedTime
        oitem.SaveAs "C:\Documents and Settings\user\Desktop\emails\" & "Email_" & j - 1 & ".doc", OlSaveAsType.olDoc ' save emails as word document
        ThisWorkbook.Sheets(1).Range("d" & j).Value = "C:\Documents and Settings\user\Desktop\emails\" & "Email_" & j - 1
        j = j + 1
    Set oinbox = Nothing
    Set olns = Nothing
    Set ol = Nothing
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...