Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export Outlook calendar to XML

Posted on 2008-10-09
7
Medium Priority
?
1,984 Views
Last Modified: 2013-11-27
I would like to do an automated export from Outlook's calendar to XML.  The export would hold 3 months calendar entries. would like the updates to run once a month from the beginning of the current month

I found some example codes but I really don't know where to start.  My knowledge of VB is null.

    * http://www.codeproject.com/KB/aspnet/teamcalendar.aspx

    * http://www.tsbradley.net/Samples/VSTO/Xml.Export.Sample.aspx

Can you please guide me on how to do this.  Where is Outlook's programming interface. Are the example links useful or should I be looking somewhere else? Where?

The XML format I would like is:
<?xml version="1.0" encoding="iso-8859-1"?>
<calendar>
    <cal>            
        <p>
            <duration>1-5.9.2008</duration>
            <memo_title>Travel</memo_title>
            <memo_details>Meeting with Hønefoss</memo_details>
        </p>
        <p>
            <duration>10-12.9.2008</duration>
            <memo_title>Travel</memo_title>
            <memo_details>Dental appointment</memo_details>
        </p>
        <p>
            <duration>18-18.9.2008</duration>
            <memo_title>Travel</memo_title>
            <memo_details>Anniversary celebration</memo_details>
        </p>
     </cal>
</calendar>

Open in new window

0
Comment
Question by:mbjorge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22695480
Hi, mbjorge.

Does this have to be done using VB.net or will VBA in Outlook be okay?
0
 

Author Comment

by:mbjorge
ID: 22696903
Hi,

VBA in Outlook would be okay.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22698344
The code below will create the XML as described.  Follow these instructions to use this.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on Module1
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
5.  Edit the code as needed.  I included comments wherever something needs to or can change
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Set the Security Level to Medium
10.  Close Outlook
11.  Start Outlook

It's a little unclear to me what values you want to use for "memo_title" and "memo_details".  I used the appointment's subject and body for those fields.  You'll need to change them if those are the values you want.  I'm also unclear on how you want to handle the value for "duration".  The format given in the example works fine for appointments that occur within a month, but I don't know how you want to format those that cross a month boundary.  So, for the moment I set duration to export the staring date of the appointment.  You can edit that as needed.
Sub ExportCal2XML()
    Const NODE_PROCESSING_INSTRUCTION = 7
    Const NODE_ELEMENT = 1
    
    Dim objDOM As Object, _
        objCalendar As Object, _
        objCal As Object, _
        objP As Object, _
        objData As Object, _
        olkItems As Outlook.Items, _
        olkAppt As Outlook.AppointmentItem, _
        datStart As Date, _
        datEnd As Date, _
        intCount As Integer
   
    ' Create the main xml node '
    Set objDOM = CreateObject("MSXML2.DOMDocument")
    Set objCalendar = objDOM.createNode(NODE_PROCESSING_INSTRUCTION, "xml", "")
    objDOM.appendChild objCalendar
   
    ' Create the Parent Node - "calendar" '
    Set objCalendar = objDOM.createNode(NODE_ELEMENT, "calendar", "")
   
    ' Create a child node - "cal" '
    Set objCal = objDOM.createNode(NODE_ELEMENT, "cal", "")
   
    ' Get the Outlook calendar items '
    datStart = Month(Date) & "/1/" & Year(Date)
    datEnd = DateAdd("m", 3, datStart)
    Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items.Restrict("[Start] > '" & Format(datStart & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datEnd & " 23:59pm", "ddddd h:nn AMPM") & "'")
    For Each olkAppt In olkItems
        Set objP = objDOM.createNode(NODE_ELEMENT, "p", "")
        objCal.appendChild objP
        ' Add Duration '
        Set objData = objDOM.createNode(NODE_ELEMENT, "duration", "")
        objData.Text = olkAppt.Start       '<- Change the data as needed'
        objP.appendChild objData
        ' Add Memo_Title '
        Set objData = objDOM.createNode(NODE_ELEMENT, "memo_title", "")
        objData.Text = olkAppt.Subject     '<- Change the data as needed'
        objP.appendChild objData
        ' Add Memo_Details '
        Set objData = objDOM.createNode(NODE_ELEMENT, "memo_details", "")
        objData.Text = olkAppt.Body        '<- Change the data as needed'
        objP.appendChild objData
        ' Add the data to the Cal node '
        objCal.appendChild objP
        Set objP = Nothing
        intCount = intCount + 1
    Next
   
    ' Append "Cal" to "Calendar" '
    objCalendar.appendChild objCal
    Set objCal = Nothing
   
    ' Append "Calendar" to the XML Dom Document '
    objDOM.appendChild objCalendar
    Set objCalendar = Nothing
   
    ' Change the name and path of the output file.'
    objDOM.Save "C:\eeTesting\Testing.xml"
    
    ' Cleanup '
    Set objDOM = Nothing
    Set objCalendar = Nothing
    Set objCal = Nothing
    Set objP = Nothing
    Set objData = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
    MsgBox "Process complete.  Exported " & intCount & " items.", vbInformation + vbOKOnly, "Export Calendar to XML"
End Sub

Open in new window

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:mbjorge
ID: 22702138
Many thanks.  

Your assumptions are correct for the "memo_title" and "memo_details".  You've got a good point there with the value for duration.  I guess if there are values that cross a month boundary, the values could be split.  For example, an event that runs from 27.09.2008 to 04.10.2008 could be written as (using the start and end dates):

<p>
     <duration>27-30.9.2008</duration>
     <memo_title>Travel</memo_title>
     <memo_details>Meeting with Hønefoss</memo_details>
</p>
<p>
     <duration>01-04.10.2008</duration>
     <memo_title>Travel</memo_title>
     <memo_details>Meeting with Hønefoss</memo_details>
</p>

Is this possible?

I tried your code as is and it "nearly" worked.  The xml document was empty.  There are events in my calandar

<?xml version="1.0"?>
<calendar><cal/></calendar>

Any insight into what I have missed out?

A million thanks again.

0
 
LVL 76

Expert Comment

by:David Lee
ID: 22702348
You want two entries for each item?  Wouldn't it be better to use "start" and "end", two properties, instead of "duration"?

I can't explain why the XML file would be empty, unless it's a date problem.  Try the revised version of the code below.
Sub ExportCal2XML()
    Const NODE_PROCESSING_INSTRUCTION = 7
    Const NODE_ELEMENT = 1
    
    Dim objDOM As Object, _
        objCalendar As Object, _
        objCal As Object, _
        objP As Object, _
        objData As Object, _
        olkItems As Outlook.Items, _
        olkAppt As Outlook.AppointmentItem, _
        datStart As Date, _
        datEnd As Date, _
        intCount As Integer
   
    ' Create the main xml node '
    Set objDOM = CreateObject("MSXML2.DOMDocument")
    Set objCalendar = objDOM.createNode(NODE_PROCESSING_INSTRUCTION, "xml", "")
    objDOM.appendChild objCalendar
   
    ' Create the Parent Node - "calendar" '
    Set objCalendar = objDOM.createNode(NODE_ELEMENT, "calendar", "")
   
    ' Create a child node - "cal" '
    Set objCal = objDOM.createNode(NODE_ELEMENT, "cal", "")
   
    ' Get the Outlook calendar items '
    intCount = Day(Date) - 1
    datStart = DateAdd("d", -intCount, Date)
    datEnd = DateAdd("m", 3, datStart)
    Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items.Restrict("[Start] > '" & Format(datStart & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datEnd & " 23:59pm", "ddddd h:nn AMPM") & "'")
    For Each olkAppt In olkItems
        Set objP = objDOM.createNode(NODE_ELEMENT, "p", "")
        objCal.appendChild objP
        ' Add Duration '
        Set objData = objDOM.createNode(NODE_ELEMENT, "duration", "")
        objData.Text = olkAppt.Start       '<- Change the data as needed'
        objP.appendChild objData
        ' Add Memo_Title '
        Set objData = objDOM.createNode(NODE_ELEMENT, "memo_title", "")
        objData.Text = olkAppt.Subject     '<- Change the data as needed'
        objP.appendChild objData
        ' Add Memo_Details '
        Set objData = objDOM.createNode(NODE_ELEMENT, "memo_details", "")
        objData.Text = olkAppt.Body        '<- Change the data as needed'
        objP.appendChild objData
        ' Add the data to the Cal node '
        objCal.appendChild objP
        Set objP = Nothing
        intCount = intCount + 1
    Next
   
    ' Append "Cal" to "Calendar" '
    objCalendar.appendChild objCal
    Set objCal = Nothing
   
    ' Append "Calendar" to the XML Dom Document '
    objDOM.appendChild objCalendar
    Set objCalendar = Nothing
   
    ' Change the name and path of the output file.'
    objDOM.Save "C:\eeTesting\Testing.xml"
    
    ' Cleanup '
    Set objDOM = Nothing
    Set objCalendar = Nothing
    Set objCal = Nothing
    Set objP = Nothing
    Set objData = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
    MsgBox "Process complete.  Exported " & intCount & " items.", vbInformation + vbOKOnly, "Export Calendar to XML"
End Sub

Open in new window

0
 

Author Comment

by:mbjorge
ID: 22702556
Fantastic!  You're a life saver. It worked this time.  Just wondered why the start dates were not ordered. See xml output below.

<?xml version="1.0"?>
<calendar>
   <cal>
     <p>
         <duration>07.10.2008 13:00:00</duration>
         <memo_title>Reise</memo_title>
         <memo_details>Møte på HF</memo_details>
    </p>

    <p>
          <duration>03.10.2008 11:00:00</duration>
          <memo_title>POP</memo_title>
          <memo_details>bla bla</memo_details>
     </p>
     <p>
           <duration>15.10.2008 12:30:00</duration>
           <memo_title>POP</memo_title>
           <memo_details>Som avtalt. Har satt av to timer</memo_details>
      </p>
</cal>
</calendar>


I'll take on your suggestion for the duration.  Tthis mean having 2 nodes. I guess this looks tidier.
<p>
  <start>27.09.2008</start>
  <end>04.10.2008</end>
  <memo_title>Travel</memo_title>
  <memo_details>Meeting with Hønefoss</memo_details>
</p>
0
 
LVL 76

Accepted Solution

by:
David Lee earned 1000 total points
ID: 22703435
I split duration into start and end and sorted the date into sequence by start date/time.
Sub ExportCal2XML()
    Const NODE_PROCESSING_INSTRUCTION = 7
    Const NODE_ELEMENT = 1
    
    Dim objDOM As Object, _
        objCalendar As Object, _
        objCal As Object, _
        objP As Object, _
        objData As Object, _
        olkItems As Outlook.Items, _
        olkAppt As Outlook.AppointmentItem, _
        datStart As Date, _
        datEnd As Date, _
        intCount As Integer
   
    ' Create the main xml node '
    Set objDOM = CreateObject("MSXML2.DOMDocument")
    Set objCalendar = objDOM.createNode(NODE_PROCESSING_INSTRUCTION, "xml", "")
    objDOM.appendChild objCalendar
   
    ' Create the Parent Node - "calendar" '
    Set objCalendar = objDOM.createNode(NODE_ELEMENT, "calendar", "")
   
    ' Create a child node - "cal" '
    Set objCal = objDOM.createNode(NODE_ELEMENT, "cal", "")
   
    ' Get the Outlook calendar items '
    intCount = Day(Date) - 1
    datStart = DateAdd("d", -intCount, Date)
    datEnd = DateAdd("m", 3, datStart)
    Set olkItems = Session.GetDefaultFolder(olFolderCalendar).Items.Restrict("[Start] > '" & Format(datStart & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datEnd & " 23:59pm", "ddddd h:nn AMPM") & "'")
    olkItems.Sort "[Start]"
    For Each olkAppt In olkItems
        Set objP = objDOM.createNode(NODE_ELEMENT, "p", "")
        objCal.appendChild objP
        ' Add Start '
        Set objData = objDOM.createNode(NODE_ELEMENT, "start", "")
        objData.Text = olkAppt.Start       '<- Change the data as needed'
        objP.appendChild objData
        ' Add End '
        Set objData = objDOM.createNode(NODE_ELEMENT, "end", "")
        objData.Text = olkAppt.End         '<- Change the data as needed'
        objP.appendChild objData
        Set objData = objDOM.createNode(NODE_ELEMENT, "memo_title", "")
        objData.Text = olkAppt.Subject     '<- Change the data as needed'
        objP.appendChild objData
        ' Add Memo_Details '
        Set objData = objDOM.createNode(NODE_ELEMENT, "memo_details", "")
        objData.Text = olkAppt.Body        '<- Change the data as needed'
        objP.appendChild objData
        ' Add the data to the Cal node '
        objCal.appendChild objP
        Set objP = Nothing
        intCount = intCount + 1
    Next
   
    ' Append "Cal" to "Calendar" '
    objCalendar.appendChild objCal
    Set objCal = Nothing
   
    ' Append "Calendar" to the XML Dom Document '
    objDOM.appendChild objCalendar
    Set objCalendar = Nothing
   
    ' Change the name and path of the output file.'
    objDOM.Save "C:\eeTesting\Testing.xml"
    
    ' Cleanup '
    Set objDOM = Nothing
    Set objCalendar = Nothing
    Set objCal = Nothing
    Set objP = Nothing
    Set objData = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
    MsgBox "Process complete.  Exported " & intCount & " items.", vbInformation + vbOKOnly, "Export Calendar to XML"
End Sub

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question