VBA API for Outlook and Email managment
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, |
VBA API for Outlook and Email managment
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 |
VBA API for Outlook and Email managment
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 |
VBA API for Outlook and Email managment
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, |
All times are GMT +1. The time now is 05:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com