Microsoft Office Tutorials and References
In Depth Information
Transferring Data from E-mail Attachments to Excel
Option Explicit¶
' * * * * *¶
'Variable declaration¶
Dim myFolderEventsClass As clsFolderEvents¶
' * * * * *¶
Public Sub Application_Startup()¶
'Create class object for handling received email items¶
Set myFolderEventsClass = New clsFolderEvents¶
End Sub¶
Place the following code in a new Class module (Insert | Class Module from the
menu bar). After creating it, rename it as clsFolderEvents by typing the name
in the name box of the Properties window (bottom left):
Option Explicit¶
' * * * * *¶
Public WithEvents myOlItems As Outlook.Items¶
'Expected Attachment file name¶
'Distributed file to collect information¶
Const strFileName As String = "O006-1.xls"¶
'Target file to write into¶
'Main workbook to collect received data in¶
Const strTargetFile As String = "C:\O006.xls"¶
' * * * * *¶
Private Sub Class_Initialize()¶
Set myOlItems = Outlook.Session _¶
.GetDefaultFolder(olFolderInbox).Items¶
End Sub¶
' * * * * *¶
Private Sub myOlItems_ItemAdd(ByVal Item As Object)¶
'Outlook objects declaration¶
Dim objMail As MailItem¶
Dim objAttachment As Attachment¶
'Verify if item is a MailItem¶
If TypeName(Item) = "MailItem" Then¶
'Set mail object¶
Set objMail = Item¶
'Loop in attachments to see if requested¶
'file exists¶
For Each objAttachment In objMail.Attachments¶
If objAttachment.FileName = strFileName Then¶
'Found requested attachment¶
'Start process¶
Call ExtractDataFile(objAttachment)¶
'No need to continue since required¶
'data has been transferred¶
Exit Sub¶
End If¶
Next objAttachment¶
End If¶
End Sub¶
Cmb
Search JabSto ::




Custom Search