Create appointment in Outlook with formatted body (1 Viewer)

nhorton79 · Mar 16, 2018

Hi All,

I am wanting to setup appointments in my Access database, and then have those appointment populate to Outlook 365.

I have setup the table (tblAppointments) with the necessary fields and have created a form to handle these (frmAppointments).

I have some VBA code which is mostly working perfectly, the only issue I have it getting it to send the body of the appointment.

On my save&close button (btnSavenClose) I have this sub procedure:

Code:

Private Sub btnSavenClose_Click()

    Dim oOutlook As Object
    Dim sAPPPath As String
    
    If IsAppRunning("Outlook.Application") = True Then    'Outlook was already running
        Set oOutlook = GetObject(, "Outlook.Application")    'Bind to existing instance of Outlook
    Else    'Could not get instance of Outlook, so create a new one
        sAPPPath = GetAppExePath("outlook.exe")    'determine outlook's installation path
        Shell (sAPPPath)    'start outlook
        Do While Not IsAppRunning("Outlook.Application")
            DoEvents
        Loop
        Set oOutlook = GetObject(, "Outlook.Application")    'Bind to existing instance of Outlook
    End If
 
    Const olAppointmentItem = 1
    Dim oOutlookAppt As Object
    
    Set oOutlookAppt = oOutlook.CreateItem(olAppointmentItem)    'Start a new appointment
    
    With oOutlookAppt
        
        .RequiredAttendees = Nz(Me.txtApptAttendees, "")
        .Subject = Nz(Me.txtApptSubject, "")
        .Location = Nz(Me.txtApptLocation, "")
    
        .Start = Me.txtApptStart
        .End = Me.txtApptEnd
        .Duration = Me.txtApptDurationMinutes
        .AllDayEvent = Me.chkAllDayEvent
    
        .Importance = Me.cboApptImportance
        .BusyStatus = Me.cboApptShowTimeAs
    
        .RTFBody = Nz(Me.txtApptNotes, "")
    
        .Mileage = Me.txtApptID 'Store the appointment id in the mileage field for later reference, updates, deletion etc.
    
    End With
    
    oOutlookAppt.Save
 
MsgBox "Appointment Added!"
 
End Sub

I originally set this to .Body = ...., but it originally included <div> tags on the body text when the appointment was created. Once I changed it to .RTFBody I now get: Runtime error 6 Overflow and when I click Debug it highlights the .RTFBody line.

The field and the textbox are both currently set to Rich Text, and I have tried this with the table field set to plain text also.

I also tried declaring a variable as a byte and then setting this to my field:

Code:

Dim b() As Byte
b = Nz(Me.txtApptNotes, "")

Then further down in the with statement, setting RTFBody to the byte variable:

Code:

.RTFBody = b

Still nothing...:banghead:

I want my team to be able to add signatures and formatted text to their appointments, especially for when they send them out to clients.

Please help. I have searched and searched and searched on this one, and feel like I'm so close....to getting there....or losing my mind.

Cronk · Mar 16, 2018

So far I have not encountered a need to work with Outlook appointments but I think I can give you a steer.

The RTFbody is unicode I believe and if so, you will need to convert your ascii string to unicode.

So try something like

.RTFBody = strConv(Nz(Me.txtApptNotes, ""), vbUnicode)

Mark_ · Mar 16, 2018

nhorton79 said:
Code:

Dim b() As [COLOR="Red"]Byte[/COLOR] b = Nz(Me.txtApptNotes, [COLOR="red"]""[/COLOR])

Byte can hold a numeric value between 0 and 255. You most likely intended this to be a STRING

nhorton79 · Mar 16, 2018

Mark_ said:
Byte can hold a numeric value between 0 and 255. You most likely intended this to be a STRING

Hi Mark,
Thank for the pointer there but have tried with and without this declaration.

That was my attempt to generate a byte array which, according to Microsoft, is what appointment bodys take.

https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/appointmentitem-rtfbody-property-outlook

I had looked up info on type conversion to ensure that when it reached outlook it was in the correct format.
https://www.cryptosys.net/pki/manpki/pki_stringstobytes.html

But still doesn’t work.

Sent from my iPhone using Tapatalk

nhorton79 · Mar 16, 2018

Cronk said:
So far I have not encountered a need to work with Outlook appointments but I think I can give you a steer.

The RTFbody is unicode I believe and if so, you will need to convert your ascii string to unicode.

So try something like

.RTFBody = strConv(Nz(Me.txtApptNotes, ""), vbUnicode)

Hi Cronk, getting error and crashes outlook “remote procedure call failed”

Tried both with my field and just sending an empty string.

If I comment out the body line the appointment gets created perfectly... so this is the only thing holding me back at present.

Sent from my iPhone using Tapatalk

nhorton79 · Mar 17, 2018

I have attached my database I've been working on, which is currently just the table and forms. There are some misc. functions I was playing around with in there also.

I intend to get this working outside of my production database and then import in the tables, forms and any modules that make it to the final draft.

Its pretty rough at the moment, there are some yellow textboxes that I am still working through which will eventually hold data relating to recurring appointments, but want to get this issue sorted first.

If anyone is able to have a look at this and see whether they have an suggestions on how to get the body working.

Thanks in advance.

Cronk · Mar 17, 2018

I thought at first the problem might have been you had the text box set to Rich text format. Changing it did not solve the issue.

In my previous post, I indicated I had not had neeed to work with Outlook appointments. I nobody here can offer anything, I'd suggest you look for an Outlook vba forum.

If you do find a solution, I for one would appreciate you posting it here just in case I have future need.

arnelgp · Mar 17, 2018

Hete is the fix (somewhat). I have not modified the converter yet. The bug is it cant chane the size of text if there are muliple font size. Anyway thats a TODO.

nhorton79 · Mar 18, 2018

Thanks for that arnelgp. It was certainly looking like an option, but it felt slightly clunky and didn't appear to do everything I needed it to do.

I have searched and searched, far and wide, high and low and I finally found something that seems to work really well...the WordEditor and the Inspector.

After some playing around, I have finally managed to make this work in a round about way, by assigning the body text to an Outlook message as HTML with formatting and then copying that over to my newly created appointment and then closing the message.

I have updated my forms code and tidied up the modules etc.

Once I have a fully working form with recurring events etc, I will upload this back here and also to the Samples/Code Repository area, so that everyone can enjoy and use across their projects.

Thank you to all who have assisted.

Create appointment in Outlook with formatted body (1 Viewer)

nhorton79

Registered User.

Cronk

Registered User.

Mark_

Longboard on the internet

nhorton79

Registered User.

nhorton79

Registered User.

nhorton79

Registered User.

Attachments

Cronk

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

nhorton79

Registered User.

Attachments

Similar threads

Users who are viewing this thread