Hello,

I'm working on a VBA application to log email correspondances in a
spreadsheet. I would like to be able to export email conversations
along with dates, and other ppl involved in the conversation in a
spreadsheet. Anytime an email is sent to or from select address
Outlook will update this spreadsheet.

What is the best way to go about this? Is this possible and what
API's, libraries etc should I look at.


Any help would be greatly appreciated. I think this could be a very
useful Macro for anybody working in an Admin.

Thank you,

duadinam laid this down on his screen :
Hello,

I'm working on a VBA application to log email correspondances in a
spreadsheet. I would like to be able to export email conversations
along with dates, and other ppl involved in the conversation in a
spreadsheet. Anytime an email is sent to or from select address
Outlook will update this spreadsheet.

What is the best way to go about this? Is this possible and what
API's, libraries etc should I look at.


Any help would be greatly appreciated. I think this could be a very
useful Macro for anybody working in an Admin.

Thank you,

I'd ask this in an Outlook group since it's Outlook that will be
writing the workbook.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

duadinam explained :
Hello,

I'm working on a VBA application to log email correspondances in a
spreadsheet. I would like to be able to export email conversations
along with dates, and other ppl involved in the conversation in a
spreadsheet. Anytime an email is sent to or from select address
Outlook will update this spreadsheet.

What is the best way to go about this? Is this possible and what
API's, libraries etc should I look at.


Any help would be greatly appreciated. I think this could be a very
useful Macro for anybody working in an Admin.

Thank you,

From Outlook, these work with Office 2010/64bit on Win7/64bit:

======================================
Public Sub MailSent_ExportToExcel()
Dim xlWorkbook As Excel.Workbook
Dim xlTargetRange As Excel.Range, Mail As Outlook.folder
Dim xlFileName As String, i As Object, j As Long, k As Integer

xlFileName = "D:\BC\Book1.xlsm"
Set xlWorkbook = Workbooks.Open(xlFileName)
Set xlTargetRange = [Sheet11!A30]

Set Mail = Session.GetDefaultFolder(olFolderSentMail)

For Each i In Mail.Items
j = j + 1
xlTargetRange(j, 1) = i.To
xlTargetRange(j, 2) = i.Subject
xlTargetRange(j, 3) = i.Body
xlTargetRange(j, 4) = i.SentOn
xlTargetRange(j, 5) = i.SenderName
For k = 1 To i.Attachments.Count
xlTargetRange(j, 5 + k) = i.Attachments(k)
Next
Next
xlWorkbook.Close (True)

End Sub
======================================


======================================
Public Sub MailReceived_ExportToExcel()
Dim xlWorkbook As Excel.Workbook
Dim xlTargetRange As Excel.Range, MailReceived As Outlook.folder
Dim xlFileName As String, i As Object, j As Long, k As Integer

xlFileName = "D:\BC\Book1.xlsm"
Set xlWorkbook = Workbooks.Open(xlFileName)
Set xlTargetRange = [Sheet11!A30]

Set MailReceived = ("Inbox ")

j = 1
xlTargetRange(j, 1) = "To"
xlTargetRange(j, 2) = "Subject"
xlTargetRange(j, 3) = "SentOn"
xlTargetRange(j, 4) = "SenderName"
xlTargetRange(j, 5) = "SenderEmailAddress"
xlTargetRange(j, 6) = "Body"

For Each i In MailReceived.Items
j = j + 1
xlTargetRange(j, 1) = i.To
xlTargetRange(j, 2) = i.Subject
xlTargetRange(j, 3) = i.SentOn
xlTargetRange(j, 4) = i.SenderName
xlTargetRange(j, 5) = i.SenderEmailAddress
'xlTargetRange(j, 6) = i.Body
For k = 1 To i.Attachments.Count
xlTargetRange(1, 6 + k) = "Attach-" & k
xlTargetRange(j, 6 + k) = i.Attachments(k)
Next
Next
xlWorkbook.Close (True)

End Sub
========================================

Bruno

On Oct 8, 8:02*am, Bruno Campanini wrote:
duadinam explained :









Hello,

I'm working on a VBA application to log email correspondances in a
spreadsheet. *I would like to be able to export email conversations
along with dates, and other ppl involved in the conversation in a
spreadsheet. *Anytime an email is sent to or from select address
Outlook will update this spreadsheet.

What is the best way to go about this? *Is this possible and what
API's, libraries etc should I look at.

Any help would be greatly appreciated. *I think this could be a very
useful Macro for anybody working in an Admin.

Thank you,

From Outlook, these work with Office 2010/64bit on Win7/64bit:

======================================
Public Sub MailSent_ExportToExcel()
Dim xlWorkbook As Excel.Workbook
Dim xlTargetRange As Excel.Range, Mail As Outlook.folder
Dim xlFileName As String, i As Object, j As Long, k As Integer

xlFileName = "D:\BC\Book1.xlsm"
Set xlWorkbook = Workbooks.Open(xlFileName)
Set xlTargetRange = [Sheet11!A30]

Set Mail = Session.GetDefaultFolder(olFolderSentMail)

For Each i In Mail.Items
* * j = j + 1
* * xlTargetRange(j, 1) = i.To
* * xlTargetRange(j, 2) = i.Subject
* * xlTargetRange(j, 3) = i.Body
* * xlTargetRange(j, 4) = i.SentOn
* * xlTargetRange(j, 5) = i.SenderName
* * For k = 1 To i.Attachments.Count
* * * * xlTargetRange(j, 5 + k) = i.Attachments(k)
* * Next
Next
xlWorkbook.Close (True)

End Sub
======================================

======================================
Public Sub MailReceived_ExportToExcel()
Dim xlWorkbook As Excel.Workbook
Dim xlTargetRange As Excel.Range, MailReceived As Outlook.folder
Dim xlFileName As String, i As Object, j As Long, k As Integer

xlFileName = "D:\BC\Book1.xlsm"
Set xlWorkbook = Workbooks.Open(xlFileName)
Set xlTargetRange = [Sheet11!A30]

Set MailReceived = ("Inbox ")

j = 1
xlTargetRange(j, 1) = "To"
xlTargetRange(j, 2) = "Subject"
xlTargetRange(j, 3) = "SentOn"
xlTargetRange(j, 4) = "SenderName"
xlTargetRange(j, 5) = "SenderEmailAddress"
xlTargetRange(j, 6) = "Body"

For Each i In MailReceived.Items
* * j = j + 1
* * xlTargetRange(j, 1) = i.To
* * xlTargetRange(j, 2) = i.Subject
* * xlTargetRange(j, 3) = i.SentOn
* * xlTargetRange(j, 4) = i.SenderName
* * xlTargetRange(j, 5) = i.SenderEmailAddress
* * 'xlTargetRange(j, 6) = i.Body
* * For k = 1 To i.Attachments.Count
* * * * xlTargetRange(1, 6 + k) = "Attach-" & k
* * * * xlTargetRange(j, 6 + k) = i.Attachments(k)
* * Next
Next
xlWorkbook.Close (True)

End Sub
========================================

Bruno

Thanks Bruno,
I'll give both a shot and let you know how it goes,