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


--
Trefor

If you run the code in Outlook 2003 or 2007 and don't create a new
Application object but use the instrinsic one, you wouldn't get the security
prompt.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool:
: http://www.vboffice.net/product.html?pub=6&lang=en

Am Tue, 27 May 2008 00:00:00 -0700 schrieb 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

Michael,

Many thanks for you reply.

Sorry but I am new to this, what does this "don't create a new Application
object but use the instrinsic one" mean? In my example code, have I done
something that breaks this rule? If so are you able to assist with what is
wrong?


--
Trefor


"Michael Bauer [MVP - Outlook]" wrote:


If you run the code in Outlook 2003 or 2007 and don't create a new
Application object but use the instrinsic one, you wouldn't get the security
prompt.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool:
: http://www.vboffice.net/product.html?pub=6&lang=en

Am Tue, 27 May 2008 00:00:00 -0700 schrieb 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

Remove this line:
Set olApp = New Outlook.Application

Then replace all remaining 'olApp' by 'Application'.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool:
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Tue, 27 May 2008 03:45:00 -0700 schrieb Trefor:

Michael,

Many thanks for you reply.

Sorry but I am new to this, what does this "don't create a new Application
object but use the instrinsic one" mean? In my example code, have I done
something that breaks this rule? If so are you able to assist with what is
wrong?

Michael,

I had to make a few other changes, but you certainly pointed me in the right
direction. Many thanks.

--
Trefor


"Michael Bauer [MVP - Outlook]" wrote:



Remove this line:
Set olApp = New Outlook.Application

Then replace all remaining 'olApp' by 'Application'.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool:
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Tue, 27 May 2008 03:45:00 -0700 schrieb Trefor:

Michael,

Many thanks for you reply.

Sorry but I am new to this, what does this "don't create a new Application
object but use the instrinsic one" mean? In my example code, have I done
something that breaks this rule? If so are you able to assist with what is
wrong?

Trefor,

Which changes, please ?

Best regards,

Ramon

"Trefor" wrote:

Michael,

I had to make a few other changes, but you certainly pointed me in the right
direction. Many thanks.

--
Trefor


"Michael Bauer [MVP - Outlook]" wrote:



Remove this line:
Set olApp = New Outlook.Application

Then replace all remaining 'olApp' by 'Application'.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool:
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Tue, 27 May 2008 03:45:00 -0700 schrieb Trefor:

Michael,

Many thanks for you reply.

Sorry but I am new to this, what does this "don't create a new Application
object but use the instrinsic one" mean? In my example code, have I done
something that breaks this rule? If so are you able to assist with what is
wrong?