Excel interactions with Outlook Calendar - is it possible to... ?

[diberlee]

Hi,

I'm creating a sheet for work, and hoping I can get a function to work to make life a little easier for us. I'd just like to check if something is actually possible before I spend too much time on it.

I would like to be able to run a macro that will use some info entered by the user to create a calendar appointment that reminds them to do some follow up work on the 5th of the following month. Since there will often be up to 50 of these to follow up on I would like for Excel to behave as following:

1/ If there is no entry already made for the 5th create a new entry in the calendar called "Reminders" or something similar. I would like specific details to be entered in the desciption area. This would only ever be a reference number and a few words.

2/ If the calendar entry already exists I would like it to be amended with details of the latest case to be added on a new line in the description area. Therefore if there were 20 reminders for November then there would be 1 entry in the calendar with 20 lines of text giving the client's details and action needed.

I know that the first part is possible and easy to achieve (sample code I will be adapting below), but I haven't been able to find anything relevant when searching for the 2nd part. Does anybody know if this is possible, and if so how I would go about amending the description as decribed?

Thanks in advance for your help

Duane

Please Login or Register  to view this content.

[Chippy]

See if you can adapt some of the code in the following thread. It includes code I wrote to create an Outlook appointment and check if an appointment with a specific subject and start time exists.

http://www.excelforum.com/excel-prog...cel-dates.html

[diberlee]

Hi Chippy,

Will take a look and post back here. Thanks

[diberlee]

Hi Chippy,

Have had a play with the code you linked to, and it looks promising but doesn't quite work. Hoping you can give me some pointers. Here's the code with my amendments so far:

Please Login or Register  to view this content.

C104 is the reminder text, C112 is the reference number, C111 is the date specified by user.

It mostly works... It can create an entry in the calendar on the day specified and add the client number + reminder text in the body. The problem comes when trying to amend to an existing entry. It completes without any errors, and when I step through the code the currentText variable fills with the current body text, adds a new line and the text to be added as expected. Then it gets interesting.

If while stepping through the code I check the calendar entry (opening then closing after each line runs) I can see that the line "olApt.Body = currentText" does it's job, the body text is amended with the new line as intended. I step through a little further and as soon as the "end sub" in Private Sub Create_Appointment runs the text that was previously added is removed and the entry returns to whatever it was before the macro was run. No other lines highlight to show that additional code was executed to remove the line, it returns to the public sub at the end of the second if statement as expected - I'm baffled by this. Outlook does show a pop up to authorise a programs access to it, but I assume after allowing access that shouldn't cause any issues.

I'm thinking I could work around this issue by pulling the body text into a temp variable, deleting the appointment and creating a new one with all the body text that should be included, but I'm hoping there's a better way... Any thoughts?

Regards
Duane

[diberlee]

I've figured out how to fix the above... Feel a bit of a fool now, just added a .Close olSave after the olApt.Body = currentText line and that's worked. A few more little tweaks and this will be working just how I want. Thanks a lot for your help with this.

One more thing I would like to ask is whether there is a way to prevent the Outlook pop-up. I can't post an image from work but the message reads:

A program is trying to access email addresses you have stored in Outlook. Do you want to allow this?

If this is unexpected it may be a virus and you should choose "no".

I get the option to authorise access for up to 10 minutes, which works but is not ideal. Do you know of a way to write permission into the macro? We're using XP with Office 2003 but will be upgrading to Win 7 & and Offiice 10 in a couple of weeks.

Duane

[Chippy]

Maybe one of these holds the answer:

https://www.google.co.uk/search?q=a+...resses+outlook

http://stackoverflow.com/questions/2...rom-c-sharp-pr suggests using the Outlook Redemption add-in or setting macro security.

http://sogeeky.blogspot.co.uk/2007/0...y-warning.html suggests a registry tweak.

[diberlee]

Hi Chippy,

Thanks for your reply. Will take a look over those, but I suspect we'll have to put up with it. No big deal.

Thanks a lot for your help with this, if not for your post I'd have probably built a 15 minute offset into the macro to make an individual entry for each reminder. Your way is much tidier, thanks again

Duane