Adjusting a VB Script to Programmatically Create a Folder triggered by an E-mail

Question

This is my first time asking a question to y'all. I'm a SQL Developer by trade, and am very green when it comes to VB.

I manage a on-line database for my department, Quickbase, and with this website we manage report requisitions. I create a ticket for each one, and that ticket creates an e-mail notifying the dev. responsible for that assignment. We have folders set up for each request that comes in, and it is very laborious and frustrating to manually create said folders.

So I asked and looked around, coming across a script that was able to do what I needed, or so I am told. However, I'm not sure how to customize it to my needs, nor implement it correctly. This is where I need your assistance, fair programming gods of SO, please help me slay this dragon, and all the riches of the realm will be yours*!

Outlook VBA 

Sub MakeFile(MyMail As MailItem)
    myMailEntryID = MyMail.EntryID
    Set outlookNameSpace = Application.GetNamespace(“MAPI”)
    Set outlookMail = outlookNameSpace.GetItemFromID(myMailEntryID)
    MyArgument = OutlookMail.Subject
    Dim sMyCommand = “c:\makefile.bet ” & MyArgument
    Shell “cmd /c ” & sMyCommand, vbHide
End Sub
Makefile.bat
@echo off
cls
mkdir %1

The webtsite URL is: www.quickbase.com The root folder path: h:///ntsp/data/reports - criteria/quickbase docs/[Folder to be created]

*Riches are not monetary, but the feeling of goodness, and completeness only gained by helping a fellow nerd out, oh and it makes the e-peen grow might and strong!

Answer 1

being a fellow nerd I am going to get you started in the right direction. I think we can achieve what you want with VBA alone and do not need to use shell.

First we need to hook an event of when this all should happen. I imagine that is when your inbox gets an email. If I am right here is the start of this.

Please understand 2 important things.

1. This only works on items coming into your inbox. Thus if you already have a rule moving items to another folder it will not work.

2. You need to "Test" the email coming in - my example shows a test of the subject. It will only call you special routine IF and ONLY IF the subject has in it "My Test"

To enter the code in the Visual Basic Editor:

On the Tools menu, point to Macro, and then click Visual Basic Editor. In the Project pane, click to expand the folders, and then double-click the ThisOutlookSession icon. Type or paste the following code into the Code window.

Dim WithEvents objInboxItems As Outlook.Items


' Run this code to start your rule.
Sub StartRule()
   Dim objNameSpace As Outlook.NameSpace
   Dim objInboxFolder As Outlook.MAPIFolder

   Set objNameSpace = Application.Session
   Set objInboxFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
   Set objInboxItems = objInboxFolder.Items

End Sub

' Run this code to stop your rule.
Sub StopRule()
   Set objInboxItems = Nothing
End Sub

' This code is the actual rule.
Private Sub objInboxItems_ItemAdd(ByVal Item As Object)
   If Item.Subject = "My Test" Then
      Call checkForFolder
   End If
End Sub

Private Sub checkForFolder()



End Sub

1. On the File menu, click Save VbaProject.OTM.
2. You can now run the StartRule and StopRule macros to turn the rule on and off.
3. Quit the Visual Basic Editor.

(You might need to start and stop Outlook to get the variables to "Hook".

Once you get this working and understood, then you can remove the on off switches.

Then you have to decide your test for making a new folder so that we can then test the email and compare it to existing folders and then make a new one etc.