Macro to send multiple meeting requests using outlook from excel

Hi All,

I have got the below macro which loops through the rows in a sheet and schedules appointments in outlook in as per my requirement but I have following issues with the below macro.

1) I want to know how I can also add the Label along with the .Body, Subject, Location etc.. Label is usually used to define colours for appointments for e.g. Important, Business, Personal etc.

2) I want to know how I can insert / copy a long text with some URL/links in it and a data table on my appointment body. I have all data in a excel sheet in a name range. .i.e. "Mailbodytext". This range is quit big .i.e. from Cell A1:X55. It's properly formatted. I want to copy this range along with formatting without gridlines on my appointment body.

Please Login or Register  to view this content.

I have attached my macro file for your reference.

Thanks a lot for your help in advance.

Hi abhay_547

I've looked at your issue for many hours and haven't been able to sort it out. Haven't given up yet but doesn't look promising.

John

Hi abhay_547

I've gotten it narrowed down to one problem (from my perspective). I'm posting a workbook that should/will demonstrate the problem. You'll have to click "OK" on this screen (See Click_OK_Button.jpg)

I'm trying to fix this shortcoming. I'll let you know of my progress.

Let me know of your issues.

John

Hi abhay_547

I don't know why your system won't open the file properly. I've tested it in both Excel 2000 and Excel 2007 and it opens fine. I've eliminated a Workbook Open Event in the attached workbook in the event it was causing a problem. The procedure I eliminated sets a reference to Microsoft Object Library in VBA. If you can open the workbook, you may need to set this reference manually in VBA->Tools->References. Although the reference should be set in the attached.

I've been all over the links you provided (many times). They don't appear to be helpful. I'll continue to work on it.

John

Hi abhay_547

The copy of your original workbook doesn't indicate such, however, that change can be incorporated. I'll need to know what will be in column F, Boby for each individual appointment. Will it be "This text should come from "Mailbodytext" range of Sheet X"? Will it be simply "Sheet 1", "Sheet2", etc? I need to know how to find which sheet goes with which appointment.

John

Hi jaslake,

Thanks a lot for your reply, Below is the answer for your question.

As of now the column F contains the text which you have mentioned in your post .i.e. "This text should come from "Mailbodytext" range of Sheet X" but if you want we can only mention the sheet name in Column F but then I would like to mention the name range in some other column .i.e. "Mailbodytext" .i.e. we can mention the name range in a separate column or as the name range is same in all sheets we can hardcode the same in our code.

I have got the below links which I think will help in someway for constructing our code.

http://www.vbforums.com/archive/index.php/t-581489.html

http://www.vbaexpress.com/forum/showthread.php?t=22757

http://www.outlookbanter.com/outlook...mailitems.html

http://www.officekb.com/Uwe/Forum.as...xpress-message

http://www.mrexcel.com/forum/showthread.php?t=338391

Thanks a lot for your help in advance.

Hi abhay_547

I'm attaching a file that resolves this issue

Sheet1 has the data for 1st appointment/meeting request. Sheet2 has the data for second appointment/meeting request and so on.

I'm still working on this issue

I've gotten it narrowed down to one problem (from my perspective). I'm posting a workbook that should/will demonstrate the problem. You'll have to click "OK" on this screen (See Click_OK_Button.jpg)

Haven't given up yet.

John

Hi abhay_547

I'm going to bed now but I wanted to post the attached. I believe we're getting closer.

John

Hi abhay_547

The attached version incorporates the feature to catagorize the appointment level of importance. I've assumed that's what you meant by this

Please Login or Register  to view this content.

If this is not what you meant, let me know.

Let me know of issues.

John

Hi jaslake,

Thanks a lot for your reply, The importance column which you have added to the file is to define the level of importance which is high or low but I am talking about the "Label" Field / Dropdown which reflects just next to Location field in a meeting request and which has following option relfecting in it's dropdown .i.e. Important, Business, Personal, Vacation etc.. I have attached a screenshot also for your reference.

Just to give you an update I have found an alternate way to select the required label automatically using the outlook mail rules. .i.e. I have created some rules in outlook which says that if the subject line of a meeting request is this then color it important, if it is something else color it personal and this solution is working fine but may be we can find the some excel VBA code which will automatically do this. However I would suggest that as we have the above alternate solution for "Label" Issue. We can go ahead and concentrate on the Copy data to meeting request issue.

I have observed that when I run the code from test7.1 file it gets stuck at below line.

Please Login or Register  to view this content.

Apart from this I am unable to find the "Click_OK_Button.jpg" attachment which you had mentioned in your earlier post.

One more thing as we are sending a meeting invite and not scheduling just appointments in our calender. We will have to add the below code in our macro.

Please Login or Register  to view this content.

And apart from above Just to highlight I have selected the references to the below in the macro file.
Microsoft Word 10.0 Object Library
Microsoft Outlook 10.0 Object Library
as I have MS Office XP 2002 installed on my system.


Thanks a lot for your help in advance.

Hi abhay_547

I'm using Outlook 2007 and this line of code executes properly

Please Login or Register  to view this content.

What Outlook version are you running? It may be a version problem but I have no idea at this point.

I've Outlook 2000 on my old desktop. Tomorrow, I'll try running the code on the desktop and see if I have your problem.

Today, I researched the "Label" issue and solved it on my machine by setting the rules in Outlook, just as you did. I've not yet found a means to do so through VBA, although, I'm certain such means probably exists.

I'll continue to work on the issue. The immediate problem is

Please Login or Register  to view this content.

and getting this to run on your machine. It approaches a "complete" solution to your process.

John

Ha...as I was working on this post and talking on the phone with my daughter, I see that you've edited your last post. Let me review your entire post and I'll get back to you.

Hi Jaslake,

I am using Outlook 2002 on my system. It doesn't seem to be a version problem because it gets stuck on the below code line and then shows following error message.

Code line :

Please Login or Register  to view this content.

Error :
Run time error '91':
Object Variable or With Block Variable not set


Thanks a lot for your help in advance.

Hi abhay_547

Regarding this

One more thing as we are sending a meeting invite and not scheduling just appointments in our calender. We will have to add the below code in our macro.

Please Login or Register  to view this content.

Please post a copy of your updated workbook with code. I wish to make sure we're not working at cross purposes; that I'm not creating fields where your's already exist.

John

Hi abhay_547

Nothing yet. I get

Error :
Run time error '91':
Object Variable or With Block Variable not set

on my Outlook 2000 machine. The error appears to be caused by this line of code

Please Login or Register  to view this content.

as it evaluated to "Nothing".

I'll continue to work with it.

John

Hi abhay_547

Well, I did find this (emphasis added)

In earlier versions of Outlook, the WordEditor was available only for messages and posts and only if the user had configured Word as the email editor. Since Word is the only editor in Outlook 2007 (except for NoteItem and DistListItem objects), it is available not just for messages and posts, but also for appointments, contacts, tasks, and journal entries. It even works if only Outlook 2007 is installed, and not Word 2007.

Still searching.

John

Hi abhay_547

I'll look at this today.

John

Hi abhay_547

Well, I've spent two days trying to sort this out and have come up empty. We've got two issues working here.

1. The first issue is a compatibility issue:

The code I've provided was written in Excel 2000 with Outlook 2007. Your file Test7.2.xls runs just as expected in Excel 2000 with Outlook 2007; it also runs just as expected in Excel 2007 with Outlook 2007. It will not run in Excel 2000 with Outlook 2000.

It's this code that Outlook 2000 doesn't like

Please Login or Register  to view this content.

I've been all over the Web to see if I can convert these lines to work in Outlook 2000 (or 2003) and have found nothing that I can make work. In my research I've seen repeated references to a COM addin called Redemption. I've downloaded Redemption but haven't made any sense of it in terms of how it helps in this situation.

2. The second issue deals with available properties.

Your reference http://www.mrexcel.com/forum/showthread.php?t=8086 while appearing to be promising, doesn't solve the issue. It relies on the HTMLBody property and that simply doesn't exist for an Outlook Appointment Item. That's why I've taken the "Paste" approach in the code I've provided..."Paste" works...I just can't seem to get it to work in Outlook 2000.

I really don't know where to go next (unless your boss will invest in Outlook 2007 )

The best I can suggest at this point is for you to convince yourself that this does indeed perform with Outlook 2007. Then, either upgrade or ask one of the true Gurus on this site to help you convert the code so it runs in Outlook 2003.

Truly sorry we couldn't solve this.

John

Hi abhay_547

Well, I've never used the DataObject method before so you've shown me a new approach. The method doesn't provide all the formatting you desired and not all the links come across. But it does work with Outlook 2000.

You'll need to set a reference to Microsoft Forms 2.0 Object Library.

Try the attached and let me know of issues.

John

Hi abhay_547

I can resolve all issues using Outlook 2007. I can't resolve all issues using Outlook 2000. I don't have Outlook 2002 (your version). The unresolved issues are compatibility issues and I haven't been successful in resolving them.

John