Solved

Export Outlook calendar to XML

Posted on 2008-10-09
7
1,818 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now