incoming message rule to kick off vba with a variable

  • Thread starter Thread starter mwilliams4
  • Start date Start date
M

mwilliams4

I'm new to programming VBA for Oulook but not Access, but here's what
I am trying to do:

I do business with about 25 branches of a particular company.
The branches are identified like this: ABC01, ABC03, ABC23, ABC58,
etc, all 5 digits.

I 'd like to send a message to my "Worker machine" (a box that runs
Access and Outlook) that says in the subject "SendTo ABC03" and it
would start an application (access) with a macro named ABC03 which
would pull the recordset and e-mail the report. I have the back half
of that already done.
What I need is a way to pass the ABC03 from the subject line into the
start application variable to call the proper macro.

I could do this with 25 rules, but I thought there might be an easier
way to do it using VBA.
 
A rule can call a "script", which is a specially formatted Public Sub in the
Outlook VBA project. For mail items the Sub would look like this:

Public Sub whateverNameYouWant(Item As Outlook.MailItem)
' blah, blah
End Sub

That would let you access the Subject and call to other macros.
 
Here's how I automatically run an Excel macro on an attachment to an
email. This requires a reference to the Excel object library from
Outlook. (Tools>References in the VB Editor).

This goes at the top of the ThisOutlookSession module:

Private WithEvents SSINBOX As Outlook.Items

Here is the startup event code. If you already have a startup event,
just copy and paste the inner code into it:

Private Sub Application_Startup()
Dim objNS As Outlook.NameSpace
Set objNS = GetNamespace("MAPI")
' monitor specific inbox for incoming msgs
Set SSINBOX = objNS.Folders("Mailbox -
SS_Inbox").Folders("Inbox").Items
End Sub

Here is the event code that monitors the Inbox and passes the macro
name to my function which calls a macro by the same name.

Private Sub SSINBOX_ItemAdd(ByVal Item As Object)

If TypeOf Item Is Outlook.MailItem Then
Dim Msg As Outlook.MailItem
Set Msg = Item
If (Msg.SenderName = "Reporting Application") _
And (Msg.Subject = "Data You Needed") And _
(Msg.Attachments.Count = 1) Then
' run our macro on the attachment
Call ProcessFile("MyMacro", Msg)
Msg.UnRead = False
End If
End If

Set myAttachments = Nothing
Set XLApp = Nothing
Set Msg = Nothing
Set objNS = Nothing
End Sub

The macro name and message are passed to the function. The macro is
stored in my personal workbook:


Function ProcessFile(MacroName As String, Item As Outlook.MailItem)
Dim myAttachments As Outlook.Attachments
Dim XLApp As Excel.Application
Dim XlWK As Excel.Workbook
Dim Att As String
Const attPath As String = "C:\"

Set XLApp = New Excel.Application

' save attachment
Set myAttachments = Item.Attachments
Att = myAttachments.Item(1).DisplayName
myAttachments.Item(1).SaveAsFile attPath & Att

' open personal workbook, just in case
On Error Resume Next
XLApp.Workbooks.Open ("C:\Documents and Settings\jpena\Application Data
\Microsoft\Excel\XLSTART\PERSONAL.XLS")
On Error GoTo 0

' open workbook and run macro
XLApp.Workbooks.Open (attPath & Att)

XLApp.Run ("PERSONAL.XLS!" & MacroName)

XLApp.Workbooks.Close
' delete temp file
Kill attPath & Att
XLApp.Quit

End Function


The event code monitors the Inbox and passes the macro name to my
function which calls a macro by the same name. In your case, you could
substitute this by parsing the subject line, i.e.

Dim MacroToRun As String
MacroToRun = Left$(Msg.Subject, Worksheetfunction.Find("
",Msg.Subject) -1)

So if someone emailed you with the subject "SendTo ABC03", the
variable MacroToRun would contain the "ABC03" string from the Subject
line, which you would then pass to the other function like this:

Call ProcessFile(MacroToRun, Msg)

So ProcessFile would run the ABC03 macro on the email message.

This is all air code so please test first. Keep in mind this would all
happen automatically, without user intervention, so you might see the
screen flicker a bit, this is normal.

I have a sample on my site:
http://codeforexcelandoutlook.com/outlook.html
(check bottom of page)

HTH,
JP
 
Back
Top