Good day everyone!
Is there a code where i can create an Appointment using excel with a range of cells from the active sheet as the Body? HELP!!!
Good day everyone!
Is there a code where i can create an Appointment using excel with a range of cells from the active sheet as the Body? HELP!!!
David
Below is the code that i use, however i still can't get the selection "B10:M60" show in the Body of an Appointment.Originally Posted by macariosario
Attached is the Actual File that i use
Sub APPOINTMENT() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2010 Dim rng As Range Dim OutApp As Outlook.Application Dim OutMail As Outlook.AppointmentItem With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing On Error Resume Next Set rng = Sheets("MEETING INVITE").Range("B10:M51").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olAppointmentItem) OutMail.MeetingStatus = olMeeting ActiveSheet.Range("B10:M60").Copy On Error Resume Next With OutMail .RequiredAttendees = Sheets("MEETING INVITE").Range("B7") .Subject = Sheets("MEETING INVITE").Range("B1") .Body = Sheets("MEETING INVITE").Range("B2") & vbCrLf & vbCrLf .Start = Sheets("MEETING INVITE").Range("B4") .End = Sheets("MEETING INVITE").Range("B5") .Location = Sheets("MEETING INVITE").Range("B3") End With With OutMail .Display End With With OutMail ActiveSheet.Range("B10:M60").OutMail On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End With End Sub
Last edited by Bob Phillips; 05-10-2012 at 01:44 AM. Reason: Added VBA tags
You'll have to use the RangeToHtml function that is included in the link.
Here's Ron's personal page with the function.
David
Thanks a lot David!
I had been looking at Ron's Page for quite some time but i can't seem to have the RangetoHTML get to work. VB Reference is already activated(Object Library etc etc)
I have never used this, so I'm only offering suggestions.
This doesn't tell me anything. Does it error, or just not do anything?Originally Posted by macariosario
I do something smiliar in Word, but with a table. I simple loop through the table building a HTML string as I go.
We can probably use HTML the same way, but I'd have to experiment and it'll probably be slow as molasses with such a large range.
It may be a few days before I can work it in.
David
RangetoHTML is just creating a new excel sheet and does nothing it even shows a blank body. I was hoping it would paste the selection i selected(B10:M60) to the body of my Appointment.
Run Appointment in sheet (MEETING INVITE).
Seems to run ok to me.
David
I tried to run it however, here's what i'm getting.( See attached screenshot).
There must be a problem with my Outlook or probably with the code i am using.
Mine looked the same. I assumed it would look ok to the recipient.
I did a quick search and it may have something to do with anitvirus.
Try to sent one and see what happens.
David
David, macariosario,
Sorry to butt in, but when I looked at this I had exactly that problem. My conclusion was that it works fine with email, because you specify a format of HTML. However, appointments can be formatted, but don't seem to support HTML as far as I can see.
Last edited by Bob Phillips; 05-11-2012 at 03:57 AM.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
You are right, Xld.
Did a little searching and found this thread. In it, Sue Mosher talks about how the Appt supports Rich Text, not HTML.
Macariosario,
You might be able to use Word as the editor, but it sounds like a lot of work.
David
That was my thought, create a table in the appointment body, go through each Excell cell and derive its format and then apply that to the appointment cell - life's too short for all of that nonsense I am afraid.Originally Posted by Tinbendr
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks to both of you guys!
Sub range_values_in_appointmentbodyl() c01 = "<table border=1 bgcolor=#FFFFF0#>" sn = Sheets("Sheet1").Range("B10:M60") For j = 1 To UBound(sn) c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>" Next c01 = c01 & "</table><P></P><P></P>" With CreateObject("Outlook.Application").CreateItem(1) .To = "snb@forums.eu" .Subject = "Range values" .HTMLBody = c01 .Start = Sheets("MEETING INVITE").Range("B4") .End = Sheets("MEETING INVITE").Range("B5") .Location = Sheets("MEETING INVITE").Range("B3") .Send End With End Sub
Have you actually tried that? We all know how to create HTML with email using HTMLBody, but nothing happens as far as I can see if you try it with appointments.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Originally Posted by snb
Hmmm..This is interesting. I might give it a shot. I am actually looking for a way to improve my skills with VBA(Knowing that i'm a newbie!).
Thanks snb for your time on this one.