Outlook Calender Events using Excel

[ccoleman]

I've got an excel file that uses ShellExecute to send out an email to everyone I list when a new order arrives. What I really want is for that email, or even a seperate function I can link, to also place an all day event on a public calender. It would take the date from a cell on the current active page and take a Subject from the current active page to create an all day reminder on that date with that subject line.

The shellexecute I'm using is as follows
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
pretty much I'm opening outlook because thats the default email program so I don't know if that will work in any way for adding something more specific to outlook. Any help would be much appreciated.

 

[Norie]

What you would need to do would create an instance of Outlook.

You could then manipulate that to create the event (do you mean appointment?).

This is how you do that.

Set myOlapp = CreateObject("Outlook.Application")

I'm not 100% sure about the rest of the code but I'll take a look into it.

 

[ccoleman]

I was guessing that was what I was gonna have to do but hoping there was a way around it lol. Its been awhile since I've worked with opening my own instance of an application so I have no idea where to go about from here. (well ok I have an idea of what I can research at least).

Its called an all day event in outlook, its more or less an apointment that doesn't pop up reminders. Its just so somebody can look and say we did this on this day etc.

 

[Norie]

I can't seem to find anything called an event.

Do you just mean an appointment where All day event has been selected?

 

[Norie]

Here's what I came up with.

Sub Test()
Dim myOlApp As Outlook.Application
Dim myItem As Outlook.AppointmentItem

    Set myOlApp = CreateObject("Outlook.Application")
    Set myItem = myOlApp.CreateItem(olAppointmentItem)
            
    myItem.Subject = "My subject"
    myItem.Body = "My new all day appointment"
    myItem.AllDayEvent = True
    myItem.Start = Date
    myItem.Save
End Sub

Obviously all the text/dates etc can be changed to cell values.

By the way as it is the code needs a reference to the Outlook Object Library.(Tools>References... in the VBA Editor)

 

[ccoleman]

yah you're right I'm just not all with it apparently, the only reason it didn't set a reminder is cause its a public calender. This works perfectly thank you! one last question though, Currently this updates my calender without talking to the admin can you think of an easy way to make this change a public calender?