Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We're running Server and Exchange 2003 here.

There's a shared calendar which HR keep up-to-date detailing staff who are on leave. I'm looking for a VB Script (or alternate) which will extract the "appointment" titles of each item for the current day and then email the detail to a mail group, in doing so notifying the group with regard to which staff are on leave for the day.

The resulting email body should be:


Staff on leave today: Mike Davis James Stead


@Paul Robichaux - ADO is the way I went for this in the end, here are the key component for those interested:

Dim Rs, Conn, Url, Username, Password, Recipient
Set Rs = CreateObject("ADODB.Recordset")
Set Conn = CreateObject("ADODB.Connection")

'Configurable variables
Username = "Domain\username" ' AD domain\username
Password = "password" ' AD password
Url = "file://./backofficestorage/domain.com/MBX/username/Calendar" 'path to user's mailbox and folder
Recipient = "email@address.com"

Conn.Provider = "ExOLEDB.DataSource"

Conn.Open Url, Username, Password
Set Rs.ActiveConnection = Conn


Rs.Source = "SELECT ""DAV:href"", " & _
" ""urn:schemas:httpmail:subject"", " & _
" ""urn:schemas:calendar:dtstart"", " & _
" ""urn:schemas:calendar:dtend"" " & _
"FROM scope('shallow traversal of """"') "
Rs.Open
Rs.MoveFirst

strOutput = ""
Do Until Rs.EOF

    If DateDiff("s", Rs.Fields("urn:schemas:calendar:dtstart"), date) >= 0 And DateDiff("s", Rs.Fields("urn:schemas:calendar:dtend"), date) < 0 Then
        strOutput = strOutput & "<p><font size='2' color='black' face='verdana'><b>" & Rs.Fields("urn:schemas:httpmail:subject") & "</b><br />" & vbCrLf
        strOutput = strOutput & "<b>From: </b>" & Rs.Fields("urn:schemas:calendar:dtstart") & vbCrLf
        strOutput = strOutput & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>To: </b>" & Rs.Fields("urn:schemas:calendar:dtend") & "<br /><br />" & vbCrLf
    End If

    Rs.MoveNext
Loop

Conn.Close

Set Conn = Nothing
Set Rec = Nothing

After that, you can do what you like with srtOutput, I happened to use CDO to send an email:

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Subject"
objMessage.From = "email@address.com"
objMessage.To = Recipient
objMessage.HTMLBody = strOutput
objMessage.Send

S

share|improve this question
    
Which version of Exchange? –  Sam Dec 11 '09 at 12:06

1 Answer 1

You can do this with the Collaboration Data Objects (CDO) libraries. They're not all that hard to use. Take a look at this sample. Essentially, you'll need to open the calendar folder by using a user with appropriate permissions, get the day's appointments, and iterate through them.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.