duadinam explained :
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.
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 = Session.Folders("(e-mail address removed)").Folders("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
