VBA to get file path and active worksheet then create link to it

[jackson1990]

Good morning,

Was looking for some help on this small project. I have macro with code that open up an appointment in outlook, but I am looking to auto generate a link to the current worksheet they are in, in the body of the appointment. That way when the appointment comes up they can simply click the link instead of having to go look for the file. Is there a way to use VBA to get the reference of the file path and current active sheet they are on and create a link to that to put in the body of that appointment?

I was thinking it might be something related to this code I have

Link = Application.ThisWorkbook.FullName

Which would retrieve the file path of the workbook, but then I would need the active sheet too and then create a link based off of that in the body of the appointment.

Any help or ideas?

Much appreciated everyone!

 

[jackson1990]

This is my full code for it so far.

Sub EmailProp() 
  Dim oApp As Outlook.Application
  Dim oNameSpace As Namespace
  Dim oItem As AppointmentItem
  Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Link = Application.ThisWorkbook.FullName
      
  On Error Resume Next
  ' check if Outlook is running
  Set oApp = GetObject("Outlook.Application")
  If Err <> 0 Then
    'if not running, start it
    Set oApp = CreateObject("Outlook.Application")
  End If
  
  Set oNameSpace = oApp.GetNamespace("MAPI")
  
  Set oItem = oApp.CreateItem(olAppointmentItem)
  
  With oItem
  
    .Subject = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(-12, -4) + " - " + Worksheets("Template Generator").Range("F16") + " - " + Worksheets("Template Generator").Range("B16")
    .Start = Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Offset(0, -1)
    .Duration = "01:00"
    .Body = Link
    
    .AllDayEvent = True
    .Importance = olImportanceNormal
    
    
    .ReminderSet = True
    .ReminderMinutesBeforeStart = "10"


    
    Select Case 1 ' do you want to display the entry first or save it immediately?
      Case 1
        .Display
      Case 2
        .Save
    End Select
  
  End With
    
  Set oApp = Nothing
  Set oNameSpace = Nothing
  Set oItem = Nothing
     
End Sub

Where this

Link = Application.ThisWorkbook.FullName

will get the reference for the workbook location and the active sheet they are on.

And this

    .Body = Link

will be where the link is generated in the body of the appointment that can be clicked on by the user.

 

[jackson1990]

I figured out I could do this with .HTMLbody. but i'm struggling to find why my .HTMLbody is not working. It just doesn't display anything in the body when I try it. Any help is appreciated!