Solved

Export Outlook calendar to XML

Posted on 2008-10-09
7
1,794 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
Comment Utility
Hi, mbjorge.

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

Author Comment

by:mbjorge
Comment Utility
Hi,

VBA in Outlook would be okay.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:mbjorge
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Email signatures have numerous marketing benefits. Here are 8 top reasons to turn your email signature into a marketing channel.
Set OWA language and time zone in Exchange for individuals, all users or per database.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

771 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

13 Experts available now in Live!

Get 1:1 Help Now