extract node values from an XML file and insert into body of an email

afppaul · #1 05-05-2014, 08:56 AM

So far I have put together some code that looks in a public folder, gets any files that have been received in the past X amount if time, moves .rar file to a folder on the desktop, extracts XML file into sub folder and deletes the .rar file. To take this a bit further i've been asked to extract certain node values from XML file and insert them into the body of a new email. I'm able to open a new email and insert the proper To: Cc: and Subject. I'm just not sure how to extract the xml values and insert them into body of email. The schema for the xml file i'm told will always stay the same. By using the following code i'm able to view the full node schema and values in the intermadiate window of the vb editor. I would like to insert four of these values into the body of an email.

Code:

Dim xmlDocument As MSXML2.DOMDocument60
Set xmlDocument = New DOMDocument60
xmlDocument.Load ("file path and name.xml")
Debug.Print xmlDocument.xml

msperry · #2 05-06-2014, 10:14 AM

Hi,

The fact that it is XML should make no difference in this case since it's just text like any other. So, the email.Body property can be loaded with your text.

As far as getting the XML out, I think you want to reference an article like http://stackoverflow.com/questions/3...l-files-in-vb6 or http://www.codeproject.com/Questions...ML-Lists-in-VB.

afppaul · #3 05-07-2014, 08:45 AM

Quote:

Originally Posted by msperry

Hi,

The fact that it is XML should make no difference in this case since it's just text like any other. So, the email.Body property can be loaded with your text.

As far as getting the XML out, I think you want to reference an article like http://stackoverflow.com/questions/3...l-files-in-vb6 or http://www.codeproject.com/Questions...ML-Lists-in-VB.

Thank you for the response. I've looked at the links provided and i am coming up with an error. I'm sure this is an easy fix but being new to vba I just can't get the syntax correct. Here is a sample of the XML file and the code I'm trying to use. I'm not sure how to post xml file with indents.

<?xml version="1.0" encoding="UTF-8"?>
<tns:updateVendorFileRepairAcknowledgementRespon se xmlns:ervmt="http://xmlschema.tmi.name.com/xsd/Resource/Resource/EquipmentRepairVendorManagement_v1" xmlns:tns="http://xmlschema.tmi.name.com/srv/RMO/ResourceMgmt/EquipmentRepairVendorManagementSvcRequestResponse_ v1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlschema.tmi.name.com/srv/RMO/ResourceMgmt/EquipmentRepairVendorManagementSvcRequestResponse_ v1 EquipmentRepairVendorManagementSvcRequestResponse_ v1_1.xsd
<tns:vendorFileRepairAckInfo>
<tns:equipmentRepairReturnMessageHeader>
<tns:repairOrderAck>
<ervmt:fileID>0</ervmt:fileID>
<ervmt:fileName>Name</ervmt:fileName>
<ervmt:messageType>ACKNOWLEDGEMENT</ervmt:messageType>
<ervmt:fileCreationDate>2014-04-02T18:46:19.428-07:00</ervmt:fileCreationDate>
<ervmt:numberOfRecords>1</ervmt:numberOfRecords>
<ervmt:vendorID>1234567</ervmt:vendorID>
<ervmt:vendorName>Name2</ervmt:vendorName>
</tns:repairOrderAck>
</tns:equipmentRepairReturnMessageHeader>
<tns:repairOrderAck>
<ervmt:repairID>11111111</ervmt:repairID>
<ervmt:rejectReasonInfoList>
<ervmt:rejectReasonInfo>
<ervmt:rejectReasonCode/>
<ervmt:rejectReasonComment>Cannot cancel order - order not found.</ervmt:rejectReasonComment>
<ervmt:rejectFieldName>RepairId</ervmt:rejectFieldName>
<ervmt:rejectFieldOriginalValue>8980997</ervmt:rejectFieldOriginalValue>
<ervmt:rejectFieldExpectedValue>N/A</ervmt:rejectFieldExpectedValue>
</ervmt:rejectReasonInfo>
</ervmt:rejectReasonInfoList>
</tns:repairOrderAck>
</tns:vendorFileRepairAckInfo>
</tns:updateVendorFileRepairAcknowledgementResponse>

Code:

Sub ParseXmlDocument()
   Dim doc As New MSXML2.DOMDocument
   Dim success As Boolean

   success = doc.Load("Path & File.xml")
   If success = False Then
      MsgBox doc.parseError.reason
   Else
      Dim nodeList As MSXML2.IXMLDOMNodeList

      Set nodeList = doc.SelectNodes("/tns:updateVendorFileRepairAcknowledgementResponse/tns:vendorFileRepairAckInfo/tns:equipmentRepairReturnMessageHeader/tns:repairOrderAck")

      If Not nodeList Is Nothing Then
         Dim node As MSXML2.IXMLDOMNode
         Dim name As String
         Dim value As String

         For Each node In nodeList
            ' Could also do
            'name = node.Attributes.getNamedItem("ervmt:fileID").Text
            name = node.SelectSingleNode("@ervmt").Text 'this is where the error is ???
            value = node.SelectSingleNode("@:fileID").Text ' I'm not sure what goes here ??
            Debug.Print name & ": " & value
         Next node
      End If
   End If
   
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Insert a hyperlink in body of email	ChuckDrago	Outlook	0	06-28-2013 06:51 AM
add hyperlink in body of Outlook email	ChuckDrago	Word	0	06-27-2013 01:13 PM
need to extract email addresses from excel files	dunndealpr	Excel	3	06-07-2013 08:29 AM
Insert a header name in the text (body)	bal-007	Word	3	11-25-2011 01:08 PM
Extract email address from field	zssteen	Excel	1	06-19-2009 02:32 AM