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
Else
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
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: Emails_XXXXXXXXXXXX_filename.zip.pgp is being moved to HOLD,
The # of X’s can change as it is a customer generated message serial number. The _filename.zip.pgp 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
and
REPORT: decrypt EMAILS_20041122_2844_20041122121948359.zip.pgp 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.
3)Capture:
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.
Mike