Solved

Export Outlook calendar to XML

Posted on 2008-10-09
7
1,831 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 250 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
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: …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

823 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