How to create a hyperlink in an Outlook appointment?

Question

I am trying to add a formatted value, from Excel to an appointment in Outlook, based on: HTMLBody Workaround For OlAppointment Object?

I get the following error:

"Run-time error '287': Application-defined or object-defined error"

I also need to create a hyperlink in an appointment, using link in one cell and text I want to be visible in another.

Sub MakeApptWithRangeBody()
    
    Dim olApp As Outlook.Application
    Dim olApt As Outlook.AppointmentItem
    
    Const wdPASTERTF As Long = 1
    
    Set olApp = Outlook.Application
    Set olApt = olApp.CreateItem(olAppointmentItem)
    
    With olApt
        .Start = Now + 1
        .End = Now + 1.2
        .Subject = "Test Appointment"
        .Location = 18
        'Sheet1.ListObjects(1).Range.Copy
        ThisWorkbook.Worksheets("Sheet1").Range("D2").Copy
        .Display
        .GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF
    End With
    
    End Sub

I can't reproduce your issue, your code works fine. And as for your second question, just write the text you want to display in cell D2 on your worksheet and insert the hyperlink to that text. Then it will show up like you want it to in your appointment. — dwirony, Sep 15 '20 at 21:28
Then I Guess I'm missing a Reference in vba, but do not know which one. Could you help me with this? — orjansj, Sep 15 '20 at 21:40
Well you wouldn't get a run-time error if you were missing a reference - but you do need to have Microsoft Outlook 16.0 Object Library enabled. What line are you getting the run-time error on? — dwirony, Sep 15 '20 at 21:43
.GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF — orjansj, Sep 15 '20 at 22:01

score 0 · Answer 1 · answered Sep 16 '20 at 08:12

0

I think you are getting this error because of security settings in Outlook which is generally controlled by the IT Administrators in any organization if you are using an official Outlook Email account

you could try below

.Body = ThisWorkbook.Worksheets("Sheet1").Range("D2").Value

in place of

ThisWorkbook.Worksheets("Sheet1").Range("D2").Copy

and remove the below line

.GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF

answered Sep 16 '20 at 08:12

Radhish Thekkute

87
3

Do you know which Security settings? Makro allowed in outlook? If .body is used i can not implement formated text. Also the pastertf enable a security problem in the settings for the organisation (i think) – orjansj Sep 16 '20 at 13:08

Pᴇʜ · Answer 2 · 2020-09-16T12:35:27.143

0

You also need to activate the "Microsoft Word Object Library" in the references. Outlook is using Word as editor and this line of code

.GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF

is using Word VBA code (in the editor). So you need the Word reference too not only the one for Outlook.

To create a hyperlink you can check the Hyperlinks.Add method which should work (not tested):

With olApt
    .Start = Now + 1
    .End = Now + 1.2
    .Subject = "Test Appointment"
    .Location = 18
    'Sheet1.ListObjects(1).Range.Copy
    ThisWorkbook.Worksheets("Sheet1").Range("D2").Copy
    .Display

    Dim Sel As Object
    Set Sel = .GetInspector.WordEditor.Windows(1).Selection

    Sel.PasteAndFormat wdPASTERTF
    Sel.Hyperlinks.Add Anchor:=Sel.Range, Address:="http:\\www.microsoft.com"

End With

edited Sep 16 '20 at 12:35

answered Sep 16 '20 at 09:52

Pᴇʜ

56,719
10
49
73

Thanks, this explains alot! I get a server not available when I use you example above. Any ideas? – orjansj Sep 16 '20 at 12:13
@orjansj in which line? The example worked for me. See the screenshot: `aaaaaaa` was in cell D2. – Pᴇʜ Sep 16 '20 at 12:32
Thanks, but the line with pasterft enable a security problem, due to outlook settings in the organisation. Maybe it works with unformatted text that I Implement with a hyperlink, but i do not know how – orjansj Sep 16 '20 at 14:26

How to create a hyperlink in an Outlook appointment?

2 Answers2