Need Help Extracting Email Content into Excel Specifically the Attachment Name

Status
Not open for further replies.

Johnny5sworld

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Hello All,

I am working on a project in Excel and need a list of all the attachments from my Sent Folder in my Outlook 2010. The attchments are all XLS. files so I am hoping this won't be to hard. thanks in advance for any help :)

Johnny
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Re: Need Help Extracting Email Content into Excel Specifically the Attachment

I don't have code handy, but this is a start - (taken from a macro another user posted in recent days) - it needs coded to work on a folder or selection of messages and get the attachment names. I won't have time before the week end to put anything together though.

Option Explicit
Sub CopyToExcel()
Dim olItem As Outlook.MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText As Variant
Dim sText As String
Dim vItem As Variant
Dim i As Long
Dim rCount As Long
Const strPath As String = "C:\Users\username\Documents\test.xlsx" 'the path of the workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Test")
' Process the message record
Set olItem = Application.ActiveExplorer().Selection(1)

'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
rCount = rCount + 1



' add code to get attachment names here

' assign to vText variable.
xlSheet.Range("B" & rCount) = vText
xlWB.Close 1
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
 

Johnny5sworld

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Re: Need Help Extracting Email Content into Excel Specifically the Attachment

I don't have code handy, but this is a start - (taken from a macro another user posted in recent days) - it needs coded to work on a folder or selection of messages and get the attachment names. I won't have time before the week end to put anything together though.

Option Explicit
Sub CopyToExcel()
Dim olItem As Outlook.MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText As Variant
Dim sText As String
Dim vItem As Variant
Dim i As Long
Dim rCount As Long
Const strPath As String = "C:\Users\username\Documents\test.xlsx" 'the path of the workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Test")
' Process the message record
Set olItem = Application.ActiveExplorer().Selection(1)

'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
rCount = rCount + 1



' add code to get attachment names here

' assign to vText variable.
xlSheet.Range("B" & rCount) = vText
xlWB.Close 1
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
This is definitely a good start for me! Hopefully someone might lend a hand in the meantime to finish this off for my needs. Thanks again!
 

MarkS

Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Status
Not open for further replies.
Top