Problems with Processing Emails

[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

 

[Norman Jones]

Hi Trefor,

See Ron de Bruin at:

How To Prevent displaying the dialog that
enables you Index to send or not send the message
http://www.rondebruin.nl/mail/prevent.htm

 

[Trefor]

Norman,

Thanks for your reply.

Ok as I read it on Ron's site there is three choices.

1. Express ClickYes. Third Party, while free is not easy to distribute by
the looks of it i.e. I can not embed in Excel. And appears to require the
user to manually turn it off/on.

2. Outlook Redemption. Third Party and not Free for commercial use.

3. CDO, but this is not going to help me with Outlook emails by the looks of
it.

Are these my only choices (or have I misunderstood any of the choices)?

 

[Norman Jones]

Hi Trefor,

I fear that I misread your question.

However, your similar post in the
microsoft.public.outlook.program_vba
NG, was anwered by the Outlook MVP
Michael Bauer as follows:

Problems with Processing Emails
http://tinyurl.com/5rsr9o

============
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.
============

Perhaps, therefore, you should proceed
with your Outlook thread, especially as
any relationship with Excel would appear
to be tenuous.

 

[Trefor]

Norman,

The link between my problem here and Excel is:

1. I am more familiar with using VBA in Excel than Outlook (i.e. ZERO in
Outlook).

2. Once I have found the email, I will be writting the content to a text
file that I will be reading back in to an Excel spreadsheet. So some of the
code will be the same. If I move this to Outlook VBA I will also have to
replicate a whole bunch of code.

3. I already have a system in place to distribute code updates for Excel,
but not for Outlook.

 

[Norman Jones]

Hi Trefor,

In your Outlook thread, you replied to Michael:

============
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 was referring to the fact that your
code creates a new version of Outlook
with the following instruction:

Set olApp = New Outlook.Application


---
Regards.
Norman

Norman,

The link between my problem here and Excel is:

1. I am more familiar with using VBA in Excel than Outlook (i.e. ZERO in
Outlook).

2. Once I have found the email, I will be writting the content to a text
file that I will be reading back in to an Excel spreadsheet. So some of
the
code will be the same. If I move this to Outlook VBA I will also have to
replicate a whole bunch of code.

3. I already have a system in place to distribute code updates for Excel,
but not for Outlook.

 

[Trefor]

Norman,

Thanks again, is this the only line that is wrong? What should I do instead?
Does this apply to Excel VBA as well?

I tried copying my entire code into Outlook and it look likes I mgiht have a
few other problems as well, so my short term fix may well be running from
Excel anyway.

 

[Trefor]

Norman,

Thanks I have worked it out between here and the Outlook Discussion Group.

 

[Ashok]

Hi Everyone,

In outlook 2007, the following code is not working properly. This is add-in
project build using Visual basic 6.0.

----------------------------------------

Set oOutbox = m_oOutlook.Session.GetDefaultFolder(olFolderOutbox)
oMovedMail.Item = oCurMail.Move(oOutbox)
oMovedMail.Send


Variable usage FYI...

Dim oCurMail as object
Set oCurMail = CreateObject("Test.USafeMailItem")

' Parameter
ByRef objMail As Outlook.MailItem

Dim oMovedMail As Object
Set oCurMail = CreateObject("Test.USafeMailItem")
' Get the current mail item.
oCurMail.Item = objMail

Dim oOutbox As Object

----------------------------------------

The followings are the observations;

Case 1 : Some time, the mail is going out to the receiver, but it stayed at
outbox itself.
Case 2 : Some time, the mail is not all going out, it stayed at outbox itself.



Could you please tell that what is the actual issues which cause the
functionality? Do I need to change the coding flow for outlook 2007?

Note : This is absolutely working fine in prior to 2007.
I pasted only the important code fyi. I ensure that there is no runtime err
or logical err.

Your adivse would be appreciated on this.

Thx in Advance,
Ashok