Sleeper: vba Appointment body

[macariosario]

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]

Take alook at this.

[macariosario]

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]

You'll have to use the RangeToHtml function that is included in the link.

Here's Ron's personal page with the function.

[macariosario]

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)

[Tinbendr]

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]

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]

Run Appointment in sheet (MEETING INVITE).

Seems to run ok to me.

[macariosario]

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.

[Tinbendr]

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]

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]

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.

[Bob Phillips]

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]

Thanks to both of you guys!

[snb]

 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]

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]

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