VBA API for Outlook and Email managment

[duadinam]

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,

 

[GS]

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.

 

[duadinam]

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 = 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

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