I am stuck at a problem where I want to export emails with specific "TO"
( can be a Distribution List). And I expect the excel to have the email
address of the sender and the time at which the email was received. Is there
a way to do this? I have looked on several forums and sites, but unable to
find something like this.

My ultimate goal is to track the emails coming in my Microsoft outlook
sent to me Distribution List and export them in an excel sheet. The objective
is to look for the number of emails recieved and the time/date at which it
was received.. I am looking for the email sender and the time of the email
received to be exported in an excel format. I am aware about creating rules
and making sure that all the emails from the sender/DL to be placed in a
specific folder.

Note: I am using an IMAP account on my outlook and there is no exchange
server.

Any help is great appreciated!

Yes you can do it. You need to use an advance search command in
outlook email vba. This requires using a class module to creatte an
evvent to let you know when the search is completed. The body of the
email messages are html so to extract the data I saved the body of each
message as an html file and then opened the file using an Internet
explorer application.

This is code from Excel Class module Class1

Public WithEvents olApp As Outlook.Application
Private m_sch As Outlook.Search
Public Sub AdvSearch(MyScope As String, MyFilter As String, _
ByRef m_sch)
Set m_sch = olApp.AdvancedSearch(MyScope, MyFilter)
End Sub
Private Sub Class_Initialize()
Set Me.olApp = CreateObject("Outlook.Application")
Set myNameSpace = Me.olApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
End Sub
Private Sub Class_Terminate()
Set Me.olApp = Nothing
End Sub
Private Sub olApp_AdvancedSearchComplete(ByVal SearchObject As
Outlook.Search)
blnSearchComp = True
End Sub


This is module code in excel
Public blnSearchComp As Boolean
Public g_clsTest As Class1
Sub GetMail()

Const strS As String = "Inbox"
Dim strF As String
Dim sch As Outlook.Search
Dim rsts As Outlook.Results
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Dim TBL As Object

TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"


strF = "urn:schemas:httpmail:" & _
"subject LIKE '%Lock%' AND" & _
"%today(urn:schemas:httpmail:datereceived)%"


blnSearchComp = False
Set g_clsTest = New Class1
g_clsTest.AdvSearch strS, strF, sch


While blnSearchComp = False
DoEvents
Wend

Set rsts = sch.Results
If rsts.Count = 0 Then
MsgBox ("No messages found - Exiting Sub")
Exit Sub
End If
rsts.Sort "ReceivedTime", Descending:=True
Set LatestMess = rsts.Item(1)


Set fs = CreateObject("Scripting.FileSystemObject")
Set fout = fs.CreateTextFile _
(FName, True)
fout.Write LatestMess.HTMLBody
fout.Close

'desroy class object
Set g_clsTest = Nothing

'Set IEObj = GetObject(FName)
Set IE = CreateObject("INternetExplorer.Application")
IE.Application.Visible = True
URL = FName
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Set TBL = IE.document.getelementsbytagname("Table")

'find Net and Gross
'Set statement below causes errors

Set TBLRows = TBL.Item(0).Rows


Set RowOne = TBLRows.Item(0)
Set RowTwo = TBLRows.Item(1)
For i = 0 To (RowOne.Children.Length - 1)
If UCase(RowOne.Children.Item(i).innertext) = "NET" Then
NetCol = i
End If
If UCase(RowOne.Children.Item(i).innertext) = "GROSS" Then
GrossCol = i
End If

Next i
Net = Val(RowTwo.Children.Item(NetCol).innertext)
Gross = Val(RowTwo.Children.Item(GrossCol).innertext)
Total = Net + Gross
ActiveCell.Value = Total

IE.Application.Quit
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193926

http://www.thecodecage.com/forumz

Hi Joel

this looked fascinating, so I loaded the code to try out for myself.
However, when attempting to run the macro, it stops immediately on the line
Private m_sch As Outlook.Search

with the error message

Compile error
User defined type not defined

Have you any idea as to what I may be doing wrong?

--
Regards
Roger Govier

joel wrote:
Yes you can do it. You need to use an advance search command in
outlook email vba. This requires using a class module to creatte an
evvent to let you know when the search is completed. The body of the
email messages are html so to extract the data I saved the body of each
message as an html file and then opened the file using an Internet
explorer application.

This is code from Excel Class module Class1

Public WithEvents olApp As Outlook.Application
Private m_sch As Outlook.Search
Public Sub AdvSearch(MyScope As String, MyFilter As String, _
ByRef m_sch)
Set m_sch = olApp.AdvancedSearch(MyScope, MyFilter)
End Sub
Private Sub Class_Initialize()
Set Me.olApp = CreateObject("Outlook.Application")
Set myNameSpace = Me.olApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
End Sub
Private Sub Class_Terminate()
Set Me.olApp = Nothing
End Sub
Private Sub olApp_AdvancedSearchComplete(ByVal SearchObject As
Outlook.Search)
blnSearchComp = True
End Sub


This is module code in excel
Public blnSearchComp As Boolean
Public g_clsTest As Class1
Sub GetMail()

Const strS As String = "Inbox"
Dim strF As String
Dim sch As Outlook.Search
Dim rsts As Outlook.Results
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Dim TBL As Object

TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"


strF = "urn:schemas:httpmail:" & _
"subject LIKE '%Lock%' AND" & _
"%today(urn:schemas:httpmail:datereceived)%"


blnSearchComp = False
Set g_clsTest = New Class1
g_clsTest.AdvSearch strS, strF, sch


While blnSearchComp = False
DoEvents
Wend

Set rsts = sch.Results
If rsts.Count = 0 Then
MsgBox ("No messages found - Exiting Sub")
Exit Sub
End If
rsts.Sort "ReceivedTime", Descending:=True
Set LatestMess = rsts.Item(1)


Set fs = CreateObject("Scripting.FileSystemObject")
Set fout = fs.CreateTextFile _
(FName, True)
fout.Write LatestMess.HTMLBody
fout.Close

'desroy class object
Set g_clsTest = Nothing

'Set IEObj = GetObject(FName)
Set IE = CreateObject("INternetExplorer.Application")
IE.Application.Visible = True
URL = FName
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Set TBL = IE.document.getelementsbytagname("Table")

'find Net and Gross
'Set statement below causes errors

Set TBLRows = TBL.Item(0).Rows


Set RowOne = TBLRows.Item(0)
Set RowTwo = TBLRows.Item(1)
For i = 0 To (RowOne.Children.Length - 1)
If UCase(RowOne.Children.Item(i).innertext) = "NET" Then
NetCol = i
End If
If UCase(RowOne.Children.Item(i).innertext) = "GROSS" Then
GrossCol = i
End If

Next i
Net = Val(RowTwo.Children.Item(NetCol).innertext)
Gross = Val(RowTwo.Children.Item(GrossCol).innertext)
Total = Net + Gross
ActiveCell.Value = Total

IE.Application.Quit
End Sub

Hi Roger,

Did you set a reference to the Outlook library, via VBE menu Tools
References?

Cheers
Andy

On 08/04/2010 13:05, Roger Govier wrote:
Hi Joel

this looked fascinating, so I loaded the code to try out for myself.
However, when attempting to run the macro, it stops immediately on the line
Private m_sch As Outlook.Search

with the error message

Compile error
User defined type not defined

Have you any idea as to what I may be doing wrong?

--
Regards
Roger Govier

joel wrote:
Yes you can do it. You need to use an advance search command in
outlook email vba. This requires using a class module to creatte an
evvent to let you know when the search is completed. The body of the
email messages are html so to extract the data I saved the body of each
message as an html file and then opened the file using an Internet
explorer application.

This is code from Excel Class module Class1

Public WithEvents olApp As Outlook.Application
Private m_sch As Outlook.Search
Public Sub AdvSearch(MyScope As String, MyFilter As String, _
ByRef m_sch)
Set m_sch = olApp.AdvancedSearch(MyScope, MyFilter)
End Sub
Private Sub Class_Initialize()
Set Me.olApp = CreateObject("Outlook.Application")
Set myNameSpace = Me.olApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
End Sub
Private Sub Class_Terminate()
Set Me.olApp = Nothing
End Sub
Private Sub olApp_AdvancedSearchComplete(ByVal SearchObject As
Outlook.Search)
blnSearchComp = True
End Sub


This is module code in excel
Public blnSearchComp As Boolean
Public g_clsTest As Class1
Sub GetMail()

Const strS As String = "Inbox"
Dim strF As String
Dim sch As Outlook.Search
Dim rsts As Outlook.Results
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Dim TBL As Object

TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"


strF = "urn:schemas:httpmail:" & _
"subject LIKE '%Lock%' AND" & _
"%today(urn:schemas:httpmail:datereceived)%"


blnSearchComp = False
Set g_clsTest = New Class1
g_clsTest.AdvSearch strS, strF, sch


While blnSearchComp = False
DoEvents
Wend

Set rsts = sch.Results
If rsts.Count = 0 Then
MsgBox ("No messages found - Exiting Sub")
Exit Sub
End If
rsts.Sort "ReceivedTime", Descending:=True
Set LatestMess = rsts.Item(1)


Set fs = CreateObject("Scripting.FileSystemObject")
Set fout = fs.CreateTextFile _
(FName, True)
fout.Write LatestMess.HTMLBody
fout.Close

'desroy class object
Set g_clsTest = Nothing

'Set IEObj = GetObject(FName)
Set IE = CreateObject("INternetExplorer.Application")
IE.Application.Visible = True
URL = FName
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Set TBL = IE.document.getelementsbytagname("Table")

'find Net and Gross
'Set statement below causes errors

Set TBLRows = TBL.Item(0).Rows


Set RowOne = TBLRows.Item(0)
Set RowTwo = TBLRows.Item(1)
For i = 0 To (RowOne.Children.Length - 1)
If UCase(RowOne.Children.Item(i).innertext) = "NET" Then
NetCol = i
End If
If UCase(RowOne.Children.Item(i).innertext) = "GROSS" Then
GrossCol = i
End If

Next i
Net = Val(RowTwo.Children.Item(NetCol).innertext)
Gross = Val(RowTwo.Children.Item(GrossCol).innertext)
Total = Net + Gross
ActiveCell.Value = Total

IE.Application.Quit
End Sub



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Prabhat,

you may visit the following link. probably it might prove to be of some help to you.

http://www.techrepublic.com/blog/mso...s-to-excel/744

Regards,
D

On Thursday, April 08, 2010 5:54 AM Prabhat Handoo wrote:

I am stuck at a problem where I want to export emails with specific "TO"
( can be a Distribution List). And I expect the excel to have the email
address of the sender and the time at which the email was received. Is there
a way to do this? I have looked on several forums and sites, but unable to
find something like this.

My ultimate goal is to track the emails coming in my Microsoft outlook
sent to me Distribution List and export them in an excel sheet. The objective
is to look for the number of emails recieved and the time/date at which it
was received.. I am looking for the email sender and the time of the email
received to be exported in an excel format. I am aware about creating rules
and making sure that all the emails from the sender/DL to be placed in a
specific folder.

Note: I am using an IMAP account on my outlook and there is no exchange
server.

Any help is great appreciated!


On Thursday, April 08, 2010 6:29 AM joel wrote:

Yes you can do it. You need to use an advance search command in
outlook email vba. This requires using a class module to creatte an
evvent to let you know when the search is completed. The body of the
email messages are html so to extract the data I saved the body of each
message as an html file and then opened the file using an Internet
explorer application.

This is code from Excel Class module Class1

Public WithEvents olApp As Outlook.Application
Private m_sch As Outlook.Search
Public Sub AdvSearch(MyScope As String, MyFilter As String, _
ByRef m_sch)
Set m_sch = olApp.AdvancedSearch(MyScope, MyFilter)
End Sub
Private Sub Class_Initialize()
Set Me.olApp = CreateObject("Outlook.Application")
Set myNameSpace = Me.olApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
End Sub
Private Sub Class_Terminate()
Set Me.olApp = Nothing
End Sub
Private Sub olApp_AdvancedSearchComplete(ByVal SearchObject As
Outlook.Search)
blnSearchComp = True
End Sub


This is module code in excel
Public blnSearchComp As Boolean
Public g_clsTest As Class1
Sub GetMail()

Const strS As String = "Inbox"
Dim strF As String
Dim sch As Outlook.Search
Dim rsts As Outlook.Results
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Dim TBL As Object

TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"


strF = "urn:schemas:httpmail:" & _
"subject LIKE '%Lock%' AND" & _
"%today(urn:schemas:httpmail:datereceived)%"


blnSearchComp = False
Set g_clsTest = New Class1
g_clsTest.AdvSearch strS, strF, sch


While blnSearchComp = False
DoEvents
Wend

Set rsts = sch.Results
If rsts.Count = 0 Then
MsgBox ("No messages found - Exiting Sub")
Exit Sub
End If
rsts.Sort "ReceivedTime", Descending:=True
Set LatestMess = rsts.Item(1)


Set fs = CreateObject("Scripting.FileSystemObject")
Set fout = fs.CreateTextFile _
(FName, True)
fout.Write LatestMess.HTMLBody
fout.Close

'desroy class object
Set g_clsTest = Nothing

'Set IEObj = GetObject(FName)
Set IE = CreateObject("INternetExplorer.Application")
IE.Application.Visible = True
URL = FName
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Set TBL = IE.document.getelementsbytagname("Table")

'find Net and Gross
'Set statement below causes errors

Set TBLRows = TBL.Item(0).Rows


Set RowOne = TBLRows.Item(0)
Set RowTwo = TBLRows.Item(1)
For i = 0 To (RowOne.Children.Length - 1)
If UCase(RowOne.Children.Item(i).innertext) = "NET" Then
NetCol = i
End If
If UCase(RowOne.Children.Item(i).innertext) = "GROSS" Then
GrossCol = i
End If

Next i


On Thursday, April 08, 2010 8:05 AM Roger Govier wrote:

Hi Joel

this looked fascinating, so I loaded the code to try out for myself.
However, when attempting to run the macro, it stops immediately on the line
Private m_sch As Outlook.Search

with the error message

Compile error
User defined type not defined

Have you any idea as to what I may be doing wrong?

--
Regards
Roger Govier

joel wrote:


On Thursday, April 08, 2010 8:56 AM Andy Pope wrote:

Hi Roger,

Did you set a reference to the Outlook library, via VBE menu Tools
References?

Cheers
Andy

On 08/04/2010 13:05, Roger Govier wrote:


Submitted via EggHeadCafe
WCF Generic DataContract object Serializer
http://www.eggheadcafe.com/tutorials...erializer.aspx