Option Explicit
' Declare global Outlook Application and NameSpace variables.
' These are declared as global variables so that they need not
' be re-created for each procedure that uses them.
Public golApp As Outlook.Application
Public gnspNameSpace As Outlook.Namespace
Function InitializeOutlook() As Boolean
' This function is used to initialize the global Application and
' NameSpace variables.
On Error GoTo Init_Err
Set golApp = New Outlook.Application ' Application object.
Set gnspNameSpace = golApp.GetNamespace("MAPI") ' Namespace object.
InitializeOutlook = True
Init_End:
Exit Function
Init_Err:
InitializeOutlook = False
Resume Init_End
End Function
Function CreateMail(astrRecip As Variant, _
strSubject As String, _
strMessage As String, _
Optional astrAttachments As Variant) As Boolean
' This procedure illustrates how to create a new mail message
' and use the information passed as arguments to set message
' properties for the subject, text (Body property), attachments,
' and recipients.
Dim objNewMail As Outlook.MailItem
Dim varRecip As Variant
Dim varAttach As Variant
Dim blnResolveSuccess As Boolean
On Error GoTo CreateMail_Err
' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If
Set golApp = New Outlook.Application
Set objNewMail = golApp.createitem(olmailitem)
With objNewMail
For Each varRecip In astrRecip
.Recipients.Add varRecip
Next varRecip
blnResolveSuccess = .Recipients.ResolveAll
If Not IsMissing(astrAttachments) Then
For Each varAttach In astrAttachments
.Attachments.Add varAttach
Next varAttach
End If
.Subject = strSubject
.Body = strMessage
If blnResolveSuccess Then
.sEnd
Else
MsgBox "Unable to resolve all recipients. Please check " _
& "the names."
.display
End If
End With
CreateMail = True
CreateMail_End:
Exit Function
CreateMail_Err:
CreateMail = False
Resume CreateMail_End
End Function
Sub test()
funGetEmailData "_EBOM" '[b][highlight]assign your folder name here[/highlight][/b]
End Sub
Function funGetEmailData(strFolder As String)
'strfolder is the name of the folder you want to look in
'dont forget to refernce oulook libaray if using in another 'app.
Dim fld As Outlook.MAPIFolder
Dim itm As Object
Dim MyMail As Outlook.MailItem
Dim int_cnt As Integer, a, idx As Integer, b
'-----------
On Error Resume Next
' Use the InitializeOutlook procedure to initialize global
' Application and NameSpace object variables, if necessary.
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If
Set golApp = New Outlook.Application
Set itm = gnspNameSpace.GetDefaultFolder(olFolderInbox)
Set itm = itm.Folders(strFolder)
int_cnt = 1
For Each MyMail In itm.Items
a = Split(MyMail.Body, " ")
For idx = 0 To UBound(a)
If UBound(Split(a(idx), "-")) > 1 Then
With ActiveWorkbook.Sheet1
.Cells(.[A1].CurrentRegion.Rows.Count + 1, "A").Value = a(idx)
Exit For
End With
End If
Next
' MyMail.Attachments.Item(1).SaveAsFile "H:\Temp " & int_cnt & " "
int_cnt = int_cnt + 1
Next
' For Each MyMail In itm.Items
' MyMail.Delete
' Next
Set fld = Nothing
Set itm = Nothing
Set MyMail = Nothing
End Function