Extract emails sent by me to a sheet

[kin]

Hello everyone

I d like to ask you how I could capture the emails I have sent based on date, subjects, addressed to et.c and organize them in a sheet . Is it possible to use some VBA to extract the email I send and export it to that sheet at once?

Is it possible anyway?

Thanks
__________________

 

[DonkeyOte]

Is this the same q as in Access Forum albeit now writing to XL rather than MS Access ?

Key issue is -- are you wanting to store the content of the email (ie message body) ?
If that's your plan it may be an issue pending size of message body.

Controlling one MS Application from another is not such a big deal though OL Object Model is a little cumbersome compared to XL (from my perspective at least) ...

 

[kin]

Hi

Is exactly the same question. I dont want to save the message body. Just the fields of the emails.

thanks

 

[DonkeyOte]

OK just for clarity -- could you outline the exact fields you want to store, where and how you want to store them -- ie on one sheet, multiple sheets etc etc...

Also, need to clarify where the messages are stored within Outlook, what the criteria are for retrieving the above data etc etc...

This is not a trivial exercise so the clearer you can be the better the chance of getting a good result.

Thanks,

 

[kin]

The non trivial issues are the ones that matter. Ok I have the fields header status, importance,icon,attachment,from,to,cc,subject,sent,size,flag status.

The emails are sent from me and saved in Personal folders, Outbox, 1 SENT OK

The emails will be extartced to a sheet but once the sheet is complete I have 1000s of emails I ll create a new sheet and save the older ones

 

[DonkeyOte]

Not totally finished article but should point you in the right direction...

NOTE: this is an OL VBA module not XL (shoudl go into Outlook VBA Editor as a new module)

Obviously you will need to change the workbook path, sheet name, row, column etc according to your own requirements.

Row, Column specify where the data is intially pasted to and will increment row thereafter pending number of messages.

Also this is desinged as a "one-off" -- ie does not anticipate any "prior" data being visble on sheet "LOG" in your XL File etc....

Hope it helps.

Sub LOG_MAIL()
'-----------------------------------
'DEFINE VARIABLES
'-----------------------------------
Dim oNS As NameSpace
Dim oMItems As Items
Dim oMI As MailItem
Dim oParentFolder As Outlook.MAPIFolder
Dim oSubFolder As Outlook.MAPIFolder
Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xls1 As String: xls1 = "LOG"
Dim xlrw1 As Long: xlrw1 = 1
Dim xlco1 As Integer: xlco1 = 1
'-----------------------------------
'OPEN XL
'-----------------------------------
Set xl = New Excel.Application
Set xlwb = xl.Workbooks.Open("C:\MAIL_LOG.xls")
xl.Visible = True
xl.Sheets(xls1).Select
xl.Cells(xlrw1, xlco1).Select
'-----------------------------------
'ITERATE OL FOLDER ITEMS
'-----------------------------------
Set oNS = Outlook.GetNamespace("MAPI")
Set oParentFolder = oNS.GetDefaultFolder(olFolderOutbox)
For Each oSubFolder In oParentFolder.Folders
    Select Case UCase(oSubFolder.Name)
        Case "1 SENT OK"
            For Each oMI In oSubFolder.Items
                xl.Cells(xlrw1, xlco1) = oMI.SenderName
                xl.Cells(xlrw1, xlco1 + 1) = oMI.To
                xl.Cells(xlrw1, xlco1 + 2) = oMI.CC
                xl.Cells(xlrw1, xlco1 + 3) = oMI.Subject
                xl.Cells(xlrw1, xlco1 + 4) = oMI.Sent
                xl.Cells(xlrw1, xlco1 + 5) = oMI.Importance
            xlrw1 = xlrw1 + 1
            Next oMI
        Case Else
    End Select
Next oSubFolder
'-----------------------------------
'SAVE & CLOSE XL FILE (QUIT APP)
'-----------------------------------
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close
xl.Quit
'-----------------------------------
'CLEAR
'-----------------------------------
Set xlwb = Nothing
Set xl = Nothing
Set oParentFolder = Nothing
Set oNS = Nothing
'-----------------------------------
'END
'-----------------------------------
End Sub

 

[kin]

you mean that I have to insert a new module in Outlook. I hope it does not erase any other emails I have from other people.

Code in excel is not needed?

 

[DonkeyOte]

correct, no vba in Xl is required

and no, nothing will be deleted in OL.

 

[kin]

thanks I will try it

 

[kin]

I tried to run it but the error

Compile error user defined type not defined appears.

Then it highlights Dim xl As Excel.Application