Create a macro that runs from a Rule

[Guest]

I have created a vba script that needs to be run on regular intervals... It
processes the messages for our SOX audit procedures. I think I need to
create it as a class module, and some other things, but did not understand
the directions... I am not normally a vba programmer

 

[Michael Bauer]

Am Tue, 10 Jan 2006 12:50:02 -0800 schrieb CF_business_analyst:

In what intervals do you need it to run?

 

[Guest]

A couple of times a day, or at least once a day. I should also state that
this is for an alternate profile which is used for only the SOX audit
messages. Theoretically, the profile / outlook would run on a server and
process the messages as they come thru. We're talking only up to 20 messages
per day (guesstimate)

 

[Michael Bauer]

Am Tue, 10 Jan 2006 14:06:02 -0800 schrieb CF_business_analyst:

Do you stick with VBA and Outlook or are we now talking about an Exchange
Server (wich is out of my head)?

 

[Guest]

Yes, I stuck with VBA. Do you know how to make a VBA procedure into a macro
or a "script" settable as run from a Rule?

 

[Michael Bauer]

Am Wed, 11 Jan 2006 06:24:02 -0800 schrieb CF_business_analyst:

Write the method into ThisOutlookSession like this:

Public Method(oMail as Outlook.MailItem)
....
End Sub

That can be called from a rule.

 

[Guest]

OK, I'm a VBA dunce. Not familiar with method concept. Here is the code, if
you could tell me what to do, i'd appreciate it.

Public Sub Check_Validation()

Dim oApp As Application 'set up the variables
Dim oNS As NameSpace
Dim oMessage As Object
Dim oAttachments As Outlook.Attachments
Dim oSave_outbox As Outlook.MAPIFolder
Dim my_savebox As Outlook.MAPIFolder
Dim NbrMsgs, f, fs
Dim val_string
Const OLTXT = 0
' Create new Outlook Application
Set oApp = New Outlook.Application
Set oNS = oApp.GetNamespace("MAPI")
Set oFolder = oNS.GetDefaultFolder(olFolderInbox)
Set fs = CreateObject("Scripting.FileSystemObject")

cc_dir = "\\<directory-path>"
Other_dir = "\<directory-path>\"
REL_DIR = "\\<directory-path>\"
'will need some code to ensure file and directory Structure are not too long
val_string = "VALID"
compare_string = " "
NbrMsgs = 0
'Cycle through the messages

For Each oMessage In oFolder.Items

With oMessage
Set myItem = oMessage
strname = myItem.Subject
'Need to check both upper and lower case, so transform subject to
upper case for comparison
compare_string = UCase(oMessage.Subject)

str_len = Len(compare_string)

'Process message if the subject includes the word validation
If InStrRev(compare_string, val_string) > 0 Then

If InStrRev(compare_string, "CC") > 0 Then
FileName = cc_dir & strname
ElseIf InStrRev(compare_string, "REL") > 0 Then
FileName = REL_DIR & strname
Else: FileName = Other_dir & strname
End If
dir_len = Len(FileName) - str_len

Set file = fs.CreateTextFile(FileName, True)

NbrMsgs = NbrMsgs + 1

file.WriteLine ("Validation File sent using Subjectline: " &
strname)
file.Close

End If
.Delete
End With
Next
End Sub

 

[Michael Bauer]

Am Thu, 12 Jan 2006 09:12:02 -0800 schrieb CF_business_analyst:

The run-a-script rule can be used to handle one item detected by the rule.

Regarding your function you can write this into ThisOutlookSession:

Public Sub Check_Validation(oMessage as Outlook.MailItem)
... all the stuff here
...
Delete the "For Each" line ...
...
... and delete line starting with "Next"
End Sub

If you can´t create a rule with all the needed criteria then simply create
one that fires for every incoming message and run the script.

 

[Guest]

THANK YOU. I did not understand how use the parameter. This works completely.