Populating data from an excel spreadsheet to Outlook Calender

Hello,

I am looking for some assistance. I am a beginner in the world of VBA. I am using Excel 2007. I am trying to use an excel spreadsheet to populate my teams outlook calender. Attached is the spreadsheet that I am working with (Worksheet 1).

What I am looking to do a few things with this data.....

1. I am looking to polulate the outlook calender using the date in column F
2. The subject line of the outlook calender entry is column A "-" column D
3. The location line of the outlook calender entry is column E
4. The body of the outlook calender entry should look as follows:
Column A
Column B if anything is in the cell
Column C if anything is in the cell
Column G if anything is in the cell
5. The start time should be 7 AM
6. The end time should be 7:01 AM. (it is okay to have multiple meetings at the same time, but if possible to make any on the same day follow each other (ex. the next one will start at 7:01 and go to 7:02, it is appreciated but not necessary)

This will all fit into a command button that each manager will be able to push (I have this already written). If it is possible, I would only like it to populate the outlook of the assigned executive in column H, If there is not an assigned executive, then I would liek that line to populate all peoples calender that push the command button.

I have tried to use generic code off of the internet (but not understanding the Outlook code it is difficult for me). The first error I received was that one of the Tools/Reference items was not checked off. To ensure that any code you help me with works, please indicate if I need to check special tabs in this area for excel and for outlook.

I know that I may not be specific enough here so if you have any questions, please ask me and I will be more then forward with the information you may need, also any explanations you can give so I actually understand what you are doing is great.

Thank you in advance for your time and assistance.

Hi jsgray

Are the Calendar Names in Outlook the same as the name listed under Assigned Executive in Sheet1?

What Version of Outlook are you using?

John

I did a google search "populate outlook calendar from excel" and got lots of hits.

This is not something I have done in the past.

Alan

Hi jsgray

I've developed some code that does most of what you require. It populates the calendar as you described (based on my perception of your description). I'm fairly certain I can't do this

if possible to make any on the same day follow each other (ex. the next one will start at 7:01 and go to 7:02

This has been done in the code

I would only like it to populate the outlook of the assigned executive in column H

This is accomplished by a table in the code that correlates the logged in user name with users mailbox name.

This has been done in the code

If there is not an assigned executive, then I would like that line to populate all peoples calender that push the command button

This is accomplished the same way as above.

A major issue. Each time a given user presses the command button, a calendar event will be created for all items applicable to that user such that the same event will be added again and again. I can overcome this by simply flagging the event as having been processed.

This approach works if there is an assigned executive. It doesn't work if there is no assigned executive. If an unassigned executive event is flagged because someone pressed the button, nobody else will get that event.

A way to overcome this is to send that unassigned event to EVERYONE then flag it as being sent. I know of no way to check if a calendar event already exists in Outlook.

From my previous post

Are the Calendar Names in Outlook the same as the name listed under Assigned Executive in Sheet1?

What Version of Outlook are you using?

What are your thoughts?

John

Hi jsgray

Trying to understand your process.

Each day (or periodically) the spreadsheet is emailed (sent out somehow) to five recipients. They each open the excel file on their computer and press a command button. A macro is then executed to create calendar events in their copy of Outlook for the person that pressed the command button.

If this is correct and if each person is using their own Default Outlook Calendar, the code becomes simpler. I just need to know "Who pushed the button".

I need something that tells me which Assigned Executive is sitting in front of the computer. I also need to know what "Nickname" is associated with that Assigned Executive (the nicknames MUST be spelled consistently the same). I also need to know if the Default Calendar is being used.

One approach to finding out who's sitting in front of the computer is to look in the computer's "Environment" and see who's logged on. That's the approach I've taken. Of course, if everyone logs on as "Administrator" this won't work.

So, assuming each of the five individuals has a unique user name and I can relate a nickname to each user name and each individual is using their Default Calendar, I believe I can make the procedure do as you require.

I'd suggest you put data validation on Column H of your data file. The names in your sample file are not spelled consistently.

I'll await your feedback.

John

Hi jsgray

Play with the attached. Based on my understanding and assumptions, it performs as expected.

Let me know of issues.

John

Hi Justin

I assume the code worked previously. So, when you say

I have made some changes

what changes did you make? Code changes?

If you made code changes, I'd need to see the code. In the sample file, it matters not which name I select, the items get posted to my default calendar. If an event has no assigned name, that event also gets posted to my default calendar.

Please post an updated workbook with your changes and I'll look at it. Can't really troubleshoot without it.

John

In regard to the code, I have changed the CommandButton#'s from 1 and 2 to 4 and 5 (respectively) Please see the code below:

Please Login or Register  to view this content.

I have also changed the names in the ShowUsers form to Madeline, Carol, Bob, Jen, Tiz. In this order.

I get the ShowUsers Form to pop up but when I hit the update button, nothing happens.
Thank you for all of your help.

Hi Justin

Anytime. Stepping through the code almost always works.

John

Originally Posted by jerryleigh

I would really appreciate if I can get a copy of that excel sheet ... trying to download and it is corrupted I need it for outlook office 2013

If you are talking about the attachment from post #8, I had no problem downloading it.