Solved

Export Outlook calendar to XML

Posted on 2008-10-09
7
1,866 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Find out what you should include to make the best professional email signature for your organization.
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

730 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