T
Trefor
Office 2003
Excel Macro Security = Medium
I have created a rule in Outlook to move all emails from a certain person
and/or subject from the Inbox to a folder. This is a shared Mailbox, so many
people may access to the mailbox, but the rule will apply for all. Then I
thought I would “process†the emails with some VBA.
Essentially I want to see if the email contains certain text in the body, if
the result is positive I will Parse the text, pull out the data I want, write
it to a text file, mark the email read and move it to a “processed†folder.
If the test is negative I will simply delete the email.
I am planning to manually run this code from Excel, but would this be better
run from Outlook?
I am getting Security Dialogs, how do I avoid this?
The below started from some code on Dicks-Clicks, but has been filled out
with research from both the Excel and Outlook Discussion Groups.
Sub GetBodyFromInbox()
' http://www.dicks-clicks.com/excel/olRetrieving.htm
Dim olApp As Outlook.Application
Dim olNS As Namespace
Dim InputFolder As Outlook.MAPIFolder
Dim ProcessedFolder As Outlook.MAPIFolder
Dim olMail As Outlook.MailItem
Dim olMail2 As Outlook.MailItem
Dim OutlookMailID As String
Dim MessageText As String
Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set InputFolder = olNS.Folders("Mailbox - Test").Folders("Order
Notification")
Set ProcessedFolder = olNS.Folders("Mailbox - Test").Folders("Order
Notification Processed")
For Each olMail In InputFolder.Items
OutlookMailID = olMail.EntryID
Set olMail2 = olNS.GetItemFromID(OutlookMailID)
MessageText = olMail2.Body
If InStr(MessageText, "my text") > 0 Then
' Parse this email.
olMail2.UnRead = False
olMail2.Move ProcessedFolder
olMail2.UnRead = False ' Per suggestion from Ken Slovak re maybe
having to set this twice.
Else
' Mail is NOT Order Notification and should be deleted
olMail2.Delete
End If
Next olMail
Set InputFolder = Nothing
Set ProcessedFolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
Set olMail2 = Nothing
End Sub
Excel Macro Security = Medium
I have created a rule in Outlook to move all emails from a certain person
and/or subject from the Inbox to a folder. This is a shared Mailbox, so many
people may access to the mailbox, but the rule will apply for all. Then I
thought I would “process†the emails with some VBA.
Essentially I want to see if the email contains certain text in the body, if
the result is positive I will Parse the text, pull out the data I want, write
it to a text file, mark the email read and move it to a “processed†folder.
If the test is negative I will simply delete the email.
I am planning to manually run this code from Excel, but would this be better
run from Outlook?
I am getting Security Dialogs, how do I avoid this?
The below started from some code on Dicks-Clicks, but has been filled out
with research from both the Excel and Outlook Discussion Groups.
Sub GetBodyFromInbox()
' http://www.dicks-clicks.com/excel/olRetrieving.htm
Dim olApp As Outlook.Application
Dim olNS As Namespace
Dim InputFolder As Outlook.MAPIFolder
Dim ProcessedFolder As Outlook.MAPIFolder
Dim olMail As Outlook.MailItem
Dim olMail2 As Outlook.MailItem
Dim OutlookMailID As String
Dim MessageText As String
Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set InputFolder = olNS.Folders("Mailbox - Test").Folders("Order
Notification")
Set ProcessedFolder = olNS.Folders("Mailbox - Test").Folders("Order
Notification Processed")
For Each olMail In InputFolder.Items
OutlookMailID = olMail.EntryID
Set olMail2 = olNS.GetItemFromID(OutlookMailID)
MessageText = olMail2.Body
If InStr(MessageText, "my text") > 0 Then
' Parse this email.
olMail2.UnRead = False
olMail2.Move ProcessedFolder
olMail2.UnRead = False ' Per suggestion from Ken Slovak re maybe
having to set this twice.
Else
' Mail is NOT Order Notification and should be deleted
olMail2.Delete
End If
Next olMail
Set InputFolder = Nothing
Set ProcessedFolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
Set olMail2 = Nothing
End Sub