Function GetCreateAutomationObject(ByVal sClassName As String, _
    ByRef bCreate As Boolean, _
    Optional ByVal sServerName As String = “”) As Object

    ‘Returns an instance of an automation object.  Uses an already
    ‘running instance if available, creates an instance if not.
    ‘Passes a boolean variable (bCreate) back to the calling application
    ‘to test whether a new instance was created
    Dim oApp As Object
    On Error Resume Next
        Set oApp = GetObject(, sClassName)
    On Error GoTo 0
    If oApp Is Nothing Then
        Set oApp = CreateObject(sClassName, sServerName)
        bCreate = True
        bCreate = False
    End If
    Set GetCreateAutomationObject = oApp
End Function

Sub TestFunction()

    Dim oApp As Object
    Dim bCreate As Boolean
    Set oApp = GetCreateAutomationObject(“Outlook.Application”, bCreate)
    ‘subject of first email in inbox
    Debug.Print oApp.getnamespace(“MAPI”).getdefaultfolder(6).items(1).Subject
    If bCreate Then oApp.Quit
    Set oApp = Nothing
    Set oApp = GetCreateAutomationObject(“Word.Application”, bCreate)
    Debug.Print oApp.Caption
    If bCreate Then oApp.Quit
    Set oApp = Nothing
End Sub

Posted in Uncategorized

One thought on “GetCreateAutomationObject

  1. Hi, Not sure if this is the right place to ask this but I will anyway :-)

    In your automation code you use the line getnamespace(“MAPI”).getdefaultfolder(6).items(1).Subject

    I have a situation where what I want exists in a sub, sub folder.

    I am using Office 2003

    If i set Folder = subFolder it breaks.

    The complete problem is that I recieve 42 emails an hour that contain information that I have to act on, in case of a fail I need to know immediately.

    There is a set policy that these emails will exist in a certain email box for backing up and standardization.

    The structure is My Email->Inbox -> Clients -> ClientName ->Report for Client

    Note: there is a further subFolder called Processed that the files are stored in once I act on them.

    The email contains a standard file name with the date and time appended.

    The contents of the message contains two things
    First is a series of lines that contain the following:
    =_ _ REPORT: is being moved to HOLD,

    The # of X’s can change as it is a customer generated message serial number. The is constant as is the leading = _ _ REPORT: and work Emails.

    The second part of the message contains the lines like:
    – REPORT: confirms, , 0, 0, 0
    – REPORT: decrypt FAIL!

    In the first example (confirms) the commas seperate fields that contain information.

    When that particular file is processed if it is successfull the zero values are poplated with text and other numbers like file size in megabytes.

    What I am trying to automate is the creation of an excel spreadsheet that will do the following.

    1)Scan my Outlook to the Report for Client subFolder.
    2)Select the first message, and extract its contents to Excel.
    a)the datetime received stamp built into Outlook
    b)message subject
    c)sender (which is always the same but I need to
    capture if that changes)
    d)each line of the message body
    e)loop through the sheet for the NEXT row to insert this block of information
    F)create a new row in excel for each line from the message. Note:it would be nice to parse out the = _ _ characters before it hits excel.
    G) move back to outlook and move the first message to the processed folder
    H)move to the next message if it exists.

    each line in the email must live on a seperate seperate row with all the captured information about the email.

    Once I get it into excel I can then run a scan to look for which message reported the pass or fail of any specific message. The current way takes approximately 10 minutes per message and if you do the math I am WAY behind in an 8 hour day.

    thanks for any pointers that you might have about this. I am sure others would be interested in this type of automation.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.