Capture Emails Heading in Excel when it arrives

[aravindhan_3]

Hi,

Is it possible to capture email details like, From, ReceivedDate, Subject etc ( only the heading and not the body of the email) to an excel as soon as the new email arrives?

Arvind

[nuttycongo]

Hello,
Try this,

'Coded by jeskit (http://www.excelforum.com/members/jeskit.html)

Option Explicit
Sub EmailExtracter()

Dim strFldr As String
Dim OEM, Nrow As String
Dim SuggestOEM As Integer
Dim OutMail As Object
Dim xlApp, xlbook, xlbookSht As Object

Set OutMail = ActiveInspector.CurrentItem
'You Willhave to modify this after you creat folder
strFldr = "C:\Users\you name\Desktop\Tasks"

Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
xlApp.Workbooks.Open strFldr & "\EmailTest"
Set xlbook = xlApp.Workbooks.Open(strFldr & "\EmailTest")
Set xlbookSht = xlbook.Sheets("EmailData")

Nrow = xlApp.WorksheetFunction.CountA(xlbookSht.Range("A:A"))
OEM = xlApp.Application.InputBox("Please enter the OEM name of the email", "OEM Entry Box", SuggestOEM)

If OEM = "" Or OEM = 0 Then
MsgBox "Please enter a name or enter Not Applicable, Thank you"
OEM = xlApp.Application.InputBox("Please enter the OEM name of the email", "OEM Entry Box", SuggestOEM)
End If

Nrow = xlApp.WorksheetFunction.CountA(xlbook.Sheets("EmailData").Range("A:A"))

xlbookSht.Range("A1" & Nrow + 1).Value = OEM

xlbookSht.Range("B1" & Nrow + 1).Value = OutMail.SenderEmailAddress

xlbookSht.Range("C1" & Nrow + 1).Value = OutMail.To

xlbookSht.Range("D1" & Nrow + 1).Value = OutMail.CC

xlbookSht.Range("E1" & Nrow + 1).Value = OutMail.SentOn

xlbookSht.Range("F1" & Nrow + 1).Value = OutMail.ReceivedTime

xlbookSht.Range("G1" & Nrow + 1).Value = OutMail.Subject

xlbookSht.Columns("A:H").EntireColumn.AutoFit

xlbookSht.SaveAs strFldr & "\" & "EmailTest "

End Sub
Regards

[aravindhan_3]

Thanks nutty,

but I am not sure where to paste this, I pasted this in Thistoulooksession in my outlook 2007. but nothing is happening.. I changed the Folder names, after creating an excel file called EmailTest
strFldr = "C:\Documents and Settings\MT45\Desktop"

Also when I tried running step by step in outlook ( F8) its stops in this line
Set OutMail = ActiveInspector.CurrentItem with the error message " Runtime Error 91: Object Variable or With Block variable not set"

Can you help me on this

[aravindhan_3]

Hi Nutty,

Can you help me on this?