Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Code to export calendar appointments with link to SQL Server for field substitution

Posted on 2010-11-23
Medium Priority
Last Modified: 2012-05-10
I have a public calendar in Exchange 2003.
I want to export as .ics files and modify the Subject: and possibly the description and filter by type
and rename the output filename with part of the description field.
These files will reside on a web server and when customers wish to have an entry placed in their outlook calendar there will be a link on the product page fromm our web site to this file.

As the entries are  processed there may be substitution of data from tables in a SQL Server database.

Has anyone else done this?
Question by:Jeff_Kingston
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
  • 2
LVL 15

Expert Comment

ID: 34244978


Exchange 2003 is outdated by Exchange 2007 and Exchange 2010.  There is better programming from what I can tell against users/mailboxes rather then public folders.

.ics files can be modified and downloaded, you would even be able to add email addresses for meeting invites.

this web site has lots of pointers for solutions for MS Exchange

I have seen websites that create an ics file for the visitor to accept / import. I guess you have a web form that will create an ics then verify fields on before posting to a public folder / mailbox?

Hope that helps some.

Accepted Solution

Jeff_Kingston earned 0 total points
ID: 34369452
Here is code I found for vbscript and I have modified to allow selection of records by date range.
Sub SaveCalendarToExcel()
'Created by Helen Feddema 9-17-2004
'Last modified 9-17-2004
'Demonstrates pushing Calendar data to rows in an Excel worksheet

On Error GoTo ErrorHandler

   Dim appWord As Word.Application
   Dim appExcel As Excel.Application
   Dim wkb As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim rng As Excel.Range
   Dim strSheet As String
   Dim strTemplatePath As String
   Dim i As Integer
   Dim j As Integer
   Dim lngCount As Long
   Dim nms As Outlook.Namespace
   Dim fld As Outlook.MAPIFolder
   'Must declare as Object because folders may contain different
   'types of items
   Dim itm As Object
   Dim strTitle As String
   Dim strPrompt As String
   'Pick up Template path from the Word Options dialog
   Set appWord = GetObject(, "Word.Application")
   strTemplatePath = appWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"
   Debug.Print "Templates folder: " & strTemplatePath
   strSheet = "Calendar.xls"
   strSheet = strTemplatePath & strSheet
   Debug.Print "Excel workbook: " & strSheet

   'Test for file in the Templates folder
   If TestFileExists(strSheet) = False Then
      strTitle = "Worksheet file not found"
      strPrompt = strSheet & _
         " not found; please copy Calendar.xls to this folder and try again"
      MsgBox strPrompt, vbCritical + vbOKOnly, strTitle
      GoTo ErrorHandlerExit
   End If
   Set appExcel = GetObject(, "Excel.Application")
   appExcel.Workbooks.Open (strSheet)
   Set wkb = appExcel.ActiveWorkbook
   Set wks = wkb.Sheets(1)
   appExcel.Application.Visible = True

   'Let user select a folder to export
   Set nms = Outlook.GetNamespace("MAPI")
   'Set nms = Application.GetNamespace("MAPI")
   Set fld = nms.PickFolder
   If fld Is Nothing Then
      GoTo ErrorHandlerExit
   End If
   'Test whether selected folder contains contact items
   If fld.DefaultItemType <> olAppointmentItem Then
      MsgBox "Folder is not a calendar folder"
      GoTo ErrorHandlerExit
   End If
   lngCount = fld.Items.Count
   If lngCount = 0 Then
      MsgBox "No appointments to export"
      GoTo ErrorHandlerExit
      Debug.Print lngCount & " appointments to export"
   End If

   'Adjust i (row number) to be 1 less than the number of the first body row
   i = 1
  'Iterate through contact items in Calendar folder, and export a few fields
   'from each item to a row in the Calendar worksheet
   For Each itm In fld.Items
      If itm.Class = olAppointment Then
         'Process item only if it is an appointment item
         'j is the column number
         j = 1
         If DateValue(itm.Start) >= DateValue(StartDate.Text) And DateValue(itm.Start) <= DateValue(EndDate.Text) Then
            i = i + 1
            Set rng = wks.Cells(i, j)
            If itm.Start <> "" Then rng.Value = itm.Start
            j = j + 1
            Set rng = wks.Cells(i, j)
            If itm.End <> "" Then rng.Value = itm.End
            j = j + 1
            Set rng = wks.Cells(i, j)
            If itm.CreationTime <> "" Then rng.Value = itm.CreationTime
            j = j + 1
            Set rng = wks.Cells(i, j)
            If itm.Subject <> "" Then rng.Value = itm.Subject
            j = j + 1
            Set rng = wks.Cells(i, j)
            If itm.Location <> "" Then rng.Value = itm.Location
            j = j + 1
            Set rng = wks.Cells(i, j)
            If itm.Categories <> "" Then rng.Value = itm.Categories
            j = j + 1
            Set rng = wks.Cells(i, j)
            If itm.IsRecurring <> "" Then rng.Value = itm.IsRecurring
            j = j + 1
            Set rng = wks.Cells(i, j)
            On Error Resume Next
         'The next line illustrates the syntax for referencing
         'a custom Outlook field
            'If itm.UserProperties("CustomField") <> "" Then
            'rng.Value = itm.UserProperties("CustomField")
            'End If
            j = j + 1
          End If
        End If
      'i = i + 1
   Next itm

   Exit Sub

   If Err.Number = 429 Then
      'Application object is not set by GetObject; use CreateObject instead
      If appWord Is Nothing Then
         Set appWord = CreateObject("Word.Application")
         Resume Next
      ElseIf appExcel Is Nothing Then
         Set appExcel = CreateObject("Excel.Application")
         Resume Next
      End If
      MsgBox "Error No: " & Err.Number & "; Description: "
      Resume ErrorHandlerExit
   End If

End Sub

Public Function TestFileExists(strFile As String) As Boolean
'Created by Helen Feddema 9-1-2004
'Last modified 9-1-2004
'Tests for existing of a file, using the FileSystemObject
   Dim fso As New Scripting.FileSystemObject
   Dim fil As Scripting.File
On Error Resume Next

   Set fil = fso.GetFile(strFile)
   If fil Is Nothing Then
      TestFileExists = False
      TestFileExists = True
   End If
End Function

I am loading into an excel spreadsheet and importing to a table in SQL modifying some of the data and createing on the fly .ics files that will be linked to fro an email sent to all attendees of the meeting/seminar so they can add to their own exchange server calendar for reminder.


Author Closing Comment

ID: 34399102
A tip of the hat to Helen.

I searched for a resolution to this for 3 weeks and somehow stumbled into this code which was clear,simple to modify stick into a vb app then modifications were simple to do.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I don't pretend to be an expert at this, but I have found a few things that are useful. I hope that sharing them here will help others, so they will not have to face some rather hard choices. Since I felt this to be a topic of enough importance and…
On September 18, Experts Exchange launched the first installment of the Help Bell, a new feature for Premium Members, Team Accounts, and Qualified Experts. The Help Bell will serve as an additional tool to help teams increase question visibility.
how to add IIS SMTP to handle application/Scanner relays into office 365.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

715 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