	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.5

Script/Rule error handling XML form data from a PDF

Asked by kuridem in Outlook Groupware Software, Adobe Acrobat, VB Script

Tags: Microsoft, Outlook, 2003, Part of agency licenced full Office 2003 install, Rules in Error. Operation Failed.

I am currently trying to set up a system to handle XML form data from an Adobe PDF. This form was created using Adobe Acrobat 8 Professional. I have the rule and script duplicated on my desktop as well as the machine that will be doing the script handling and I am getting the same error.

I have a rule set up to catch emails with a specific subject line (the subject line from the form) and move them into a folder, mark them as read, then run the attached script.

When I submit form data, the script moves the email and marks it as read then throws up a 'Rules in Error' dialogue box referencing the rule i just made with the message 'Operation failed.' It doesn't give any further details. When this happens Outlook also disables the rule.

If I create a rule that does nothing but run the script and manually run this rule on the exact same email that caused the error it works perfectly.

If forward the email to the inbox after enabling the rule again it also works perfectly.

Also I have forwarded the email to my gmail account and then back to the account that should be processing form data. Again the rule and script work without a hitch.

The only time this combination fails is when the email comes from the form itself, which kind of shoots the whole automation process in the foot.

I am looking for any insight as to why this problem might be occurring or any workaround that i may be able to use to resolve it.

Thanks in advance.

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:

           
           
             ''''
'' RegistrationProcessor.vbs
''    Contains two functions SaveAttachmentsToDiskRule and ProcessTrainerTrainingFormXML
''  These functions are intended to be used to compile the XML form data attachements
''   recieved from the Train-the-Trainer Event form.  This form data is merged into a
''   single CSV file that can be opened in Excel.  Info on how each function works
''   is below.
''
''  This script is intended to be a macro for Outlook 2003 however it wouldn't take much
''   to modify it to function as a standalone drag-n-drop application.
''
''  Setup Instructions for Outlook 2003: (may work in other versions)
''    1. If you havn't already created a digital certificate for yourself on
''       the machine that the macro will run on do this first.
''      Go to Start->Programs->Microsoft Office->Microsoft Office Tools->Digital Certificates
''      Give the Certificate a name and click OK
''
''    2. Create and sign the macro in outlook.
''      Click Tools->Macro->VisualBasic Editor
''      Under Module (top left pane) double click Module1
''      Select the entire contents of this file and paste it into the right hand pane of
''          the VisualBasic editor.
''      Click Tools->Digital Signature
''          Click Choose->Select your certificate->OK->OK
''      Click the save button and exit the editor.
''
''    3. Verify your security settings in Outlook.
''      Click Tools->Macro->Security
''      Select High and click OK
''
''    4. Create a rule to run the macro when an email is recieved.
''      Click Tools->Rules and Alerts
''      Click New Rule
''      Select 'Start from a blank rule' & 'Check messages when they arrive' then click next.
''      Check 'with specific words in the subject or body' then in the bottom pane click
''        'specific words' and write the subject of one of the emails that the form has
''        submitted.  Should be 'Train the Trainer Registration'.  Click Next.
''      Check 'run a script' then in the bottom pane click 'a script' and select
''        SaveAttachmentToDiskRule.
''      Add any other actions you wish, such as 'mark it as read'
''      If you would like to add exceptions click next otherwise click finish.
''
''''
 
 
''''
'' SaveAttachementsToDiskRule
''   When it is passed an email (by Outlook) saves the attachement to the folder in
''  strRootFolderPath. Then calles the ProcessTrainerTrainingFormXML passing the
''  saved attachments in as arguements.
''
''  Input:
''    E-mail with form data attachements
''
''  Output:
''    XML Files passed to ProcessTrainerTrainingFormXML
''''
Sub SaveAttachmentsToDiskRule(olkMessage As Outlook.MailItem)
    Dim olkAttachment As Outlook.Attachment, _
        objFSO As Object, _
        objShell As Object, _
        strRootFolderPath As String, _
        strFileName As String
    
    ' Initialize the file system and set the root folder for the attachments to be saved to
    '   and the CSV file to be created in.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    strRootFolderPath = "C:\tools\XML\"
 
 
    ' Verify that there are attachments then for each one
    If olkMessage.Attachments.Count > 0 Then
        For Each olkAttachment In olkMessage.Attachments
        
            ' Get the file name of the attachment and verify that the name is unique
            '   If a file with that name exists in strRootFolderPath then the script
            '   will add (#) to the name.  This process repeats until a unique
            '   number has been found.
            strFileName = olkAttachment.FileName
            intCount = 0
            
            ' Check for uniqueness at the start of every loop.  If a unique name
            '   is found then FileExists will return false and the loop is
            '   broken or skipped altogther.
            Do While objFSO.FileExists(strRootFolderPath & strFileName)
    
                'Increment count and update the file name with the count
                intCount = intCount + 1
                strFileName = "Copy (" & intCount & ") of " & olkAttachment.FileName
            Loop
            'Check to see if file is an XML file.
            If InStr(olkAttachment.FileName, ".xml") > 0 Then
                ' Save the attachment to the strRootFolderPath
                olkAttachment.SaveAsFile strRootFolderPath & strFileName
            
                ' Call the function to process the XML data
                If Not ProcessTrainerTrainingFormXML(strRootFolderPath & strFileName) Then
                    ' This xml file failed to process correctly.
                    MsgBox "This XML file couldn't be processed: strRootFolderPath & strFileName"
                End If
            Else
                ' This message has an attachemnt that isn't an XML file.
                MsgBox "This message has an attachment that isn't an XML File."
            End If
        Next
    End If
    Set objFSO = Nothing
    Set olkAttachment = Nothing
    Set olkMessage = Nothing
End Sub
 
 
 
 
 
'''''
'' ProcessTrainerTrainingFormXML
''   This function is designed to process the XML Train the Trainer form data files
''      saved by SaveAttachementToDiskRule.  That function will call this one once
''      the attachement has been saved.
''
''  Arguements:
''      XML file name to be processed
''
''  Returns:
''      True if the operation completes succesfully.  False otherwise.
''
''  Output:
''      A comma separated value file (spread sheet) named
''    Registrations.csv in the same directory as the file passed to it.
''      Also the XML file is renamed and moved into a subfolder
''    of the same directory it started in.  ( processed\ )
''
'''''
Function ProcessTrainerTrainingFormXML(strFileName As String)
    ' Load the file system and the XML parser
    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
 
    ' Ensures that the entire XML is loaded before errors are thrown.
    xmlDoc.async = "false"
    
    ' Ensure that a valid filename has been passed in and exit if not.
    If Not objFS.FileExists(strFileName) Then
        ProcessTrainerTrainingFormXML = False
    End If
    
 
    ' Get the current working directory
    Path = objFS.GetParentFolderName(strFileName)
 
 
 
    ''''
    '' Verify that the output file and folder exist.
    ''
    ''''
 
    ' Set the file name for the output file and declare the output file variable
    FileName = Path & "\Registrations.csv"
    Dim csvOutput
 
    ' Verify that the header row is in the spread sheet so we know what each column is.
    If Not objFS.FileExists(FileName) Then
 
        ' If it isn't there then we create the file and write the header line.
        Set csvOutput = objFS.OpenTextFile(FileName, 8, True)
        csvOutput.writeLine "Date,Employee Name,Business,Address,State/Prov/Zip,Work Phone,E-Mail Address,Bring Kit,Buy Kit"
    
    Else
        ' If the file exists we only need to open it.
        Set csvOutput = objFS.OpenTextFile(FileName, 8)
    End If
 
    If Not objFS.FolderExists(Path & "\processed\") Then
        objFS.CreateFolder (Path & "\processed\")
    End If
 
 
    ''''
    '' For each arguement, attempt to load it as XML.  If successful, load the data
    ''   we are looking for from the XML file.  Once that is done write the data to
    ''   the CSV file.  Finally rename the XML file to the following format:
    ''          employeeName-date-time.xml
    ''   and move the XML file into the \Processed folder.
    ''
    ''''
 
    'Ensure that the arguement is a valid XML file
    If xmlDoc.Load(strFileName) Then
            
        ' Zero all of the data points to make sure records don't overlap.
        appDate = Null
        employeeName = Null
        biz = Null
        Address = Null
        State = Null
        phone = Null
        Email = Null
        bring = 0
 
 
        ' look at every node within the XML docuemnt
        For Each node In xmlDoc.documentElement.childNodes
        
            ' Get the text for the node and remove commas
            '  Prevents conflicts with the CSV file output
            Text = Replace(node.Text, ",", ".")
        
            ' Pull out the name of the node and load the corresponding data point.
            Select Case node.nodename
            Case "Date"
                appDate = Text
            Case "EmployeeName"
                employeeName = Text
            Case "Business"
                biz = Text
            Case "Address"
                Address = Text
            Case "StateProvZip"
                State = Text
            Case "WorkPhone"
                phone = Text
            Case "Email"
                Email = Text
            Case "RadioButtonList"
                bring = Text
            End Select
        Next
 
        ' Write the data to the CSV file
        csvOutput.Write appDate & "," & employeeName & "," & biz & "," & Address & "," & State & "," & phone & "," & Email & ","
        
        ' Output from the radio button must be interpreted by the script.
        If bring = 1 Then
            'bring
            csvOutput.Write "X, " & vbCrLf
        ElseIf bring = 2 Then
            'buy
            csvOutput.Write " ,X" & vbCrLf
        Else
            'no input
            csvOutput.Write " , " & vbCrLf
        End If
        
        ' Get the original XML file so that it can be renamed.
        Set file = objFS.getFile(strFileName)
        
        ' Create a name based on the name of the applicant, date, and time.
        '  The date and time have to have the / and : characters filtered out since
        '  these are invalid characters in the windows file system.
        file.Name = employeeName & "-" & Replace(Date, "/", ".") & "-" & int(timer) & ".xml"
        
        ' move the renamed file to the \processed folder.
        objFS.MoveFile file.Path, Path & "\processed\"
        
    Else
        ' return false becasue the XML is invalid
        csvOutput.Close
        ProcessTrainerTrainingFormXML = False
    End If
 
    ' close our CSV file for saftey.
    csvOutput.Close
    ProcessTrainerTrainingFormXML = True
End Function

           
         

Attachments:

Registration-Test.pdf (313 KB) (File Type Details)

A version of the form that is set up to send email to my account rather than the one that is having the issue.

Script/Rule error handling XML form data from a PDF

Asked by kuridem in Outlook Groupware Software, Adobe Acrobat, VB Script

Tags: Microsoft, Outlook, 2003, Part of agency licenced full Office 2003 install, Rules in Error. Operation Failed.

About this solution