PDA

View Full Version : VBA APPOINTMENT BODY



macariosario
05-09-2012, 08:34 AM
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!!! :)

Tinbendr
05-09-2012, 11:55 AM
Take alook at this. (http://msdn.microsoft.com/en-us/library/ff519602(v=office.11).aspx)

macariosario
05-10-2012, 01:17 AM
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!!! :)

Below is the code that i use, however i still can't get the selection "B10:M60" show in the Body of an Appointment.
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

Tinbendr
05-10-2012, 06:11 AM
You'll have to use the RangeToHtml function that is included in the link.

Here's Ron's personal page (http://www.rondebruin.nl/mail/folder3/mail4.htm) with the function.

macariosario
05-10-2012, 08:23 AM
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):banghead:

Tinbendr
05-10-2012, 08:32 AM
I have never used this, so I'm only offering suggestions.


i can't seem to have the RangetoHTML get to work.

This doesn't tell me anything. Does it error, or just not do anything?

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.

macariosario
05-10-2012, 08:52 AM
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.:(

Tinbendr
05-10-2012, 09:47 AM
Run Appointment in sheet (MEETING INVITE).

Seems to run ok to me.

macariosario
05-11-2012, 12:21 AM
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.:banghead::banghead::banghead:

Tinbendr
05-11-2012, 02:43 AM
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.

Bob Phillips
05-11-2012, 03:02 AM
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.

Tinbendr
05-11-2012, 08:04 AM
You are right, Xld.

Did a little searching and found this thread. (http://social.msdn.microsoft.com/forums/en-US/vsto/thread/469a17dc-4f73-4243-b182-75e2677d8f54) 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.

Bob Phillips
05-11-2012, 08:14 AM
Macariosario,

You might be able to use Word as the editor, but it sounds like a lot of work.

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.

macariosario
05-14-2012, 09:28 PM
Thanks to both of you guys!:)

snb
05-15-2012, 12:43 AM
or: from http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#L4 (http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#L4)

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

Bob Phillips
05-15-2012, 01:38 AM
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.

macariosario
05-17-2012, 02:38 AM
or: from http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#L4 (http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#L4)

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



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!:rotlaugh:).
Thanks snb for your time on this one.