Macro to extract and modify links from emails

[schwarznavy]

Good morning,

When coworkers create Skype for Business meetings in Outlook, the invitation includes a Skype Meeting link. I'm assuming that the recipient's Outlook client reads this link and therefore enables a "Join Meeting" button in numerous places (the ribbon, when right-clicking the meeting, the reminder window, etc).

Issue: When coworkers use a Virtual Meeting Room instead of a Skype meeting, the invitation includes a different kind of link (###-####@work.domain). Outlook clients don't read this at all. No "Join Meeting" buttons are activated. Clicking the link in the email opens up a new email to that address. To join via video, I need to dial #######@work.domain (remove the hyphen) in Skype and then join video.

Solution: I'm thinking a good solution would be to create a macro that can read any ###-####@work.domain links in emails. Then, it would convert that link to whatever is the appropriate URL to tell Skype to make a video call to the correct address. My guess is it is something like: sip:<#######@work.domain>.

Any other suggested paths?

Any tips or pointers for me to get started?

 

[Sentinels]

Good morning,

When coworkers create Skype for Business meetings in Outlook, the invitation includes a Skype Meeting link. I'm assuming that the recipient's Outlook client reads this link and therefore enables a "Join Meeting" button in numerous places (the ribbon, when right-clicking the meeting, the reminder window, etc).

Issue: When coworkers use a Virtual Meeting Room instead of a Skype meeting, the invitation includes a different kind of link (###-####@work.domain). Outlook clients don't read this at all. No "Join Meeting" buttons are activated. Clicking the link in the email opens up a new email to that address. To join via video, I need to dial #######@work.domain (remove the hyphen) in Skype and then join video.

Solution: I'm thinking a good solution would be to create a macro that can read any ###-####@work.domain links in emails. Then, it would convert that link to whatever is the appropriate URL to tell Skype to make a video call to the correct address. My guess is it is something like: sip:<#######@work.domain>.

Any other suggested paths?

Any tips or pointers for me to get started?

I do something very similar - but in appointments (not e-mail) - for zoom. For me, it is triggered with any appointment that contains a zoom link (with a reminder 15 minutes before and a pop-up request to join 2 minutes before the meeting). Feel free to play around the code to work for new e-mails / skype:

Public WithEvents oReminders As Outlook.Reminders
Public sDismissSubject As String

Private Sub Application_Reminder(ByVal objItem As Object)
Dim objWordHyperlinks As Word.Hyperlinks
Dim objWordHyperlink As Word.Hyperlink

Dim iTimeDiff As Integer

Set oReminders = Outlook.Reminders

If objItem.MessageClass <> "IPM.Appointment" Then Exit Sub

On Error Resume Next
If funcIsZoomAppt(objItem) Then
iTimeDiff = (objItem.Start - VBA.Now) * 24 * 60

If iTimeDiff <= 2 And iTimeDiff >= 0 Then
Set objWordHyperlinks = objItem.GetInspector.WordEditor.Hyperlinks

For Each objWordHyperlink In objWordHyperlinks
If VBA.InStr(1, objWordHyperlink.Address, "zoom.us/", vbTextCompare) Then
If VBA.MsgBox("Zoom appointment (" & objItem.Subject & ") in " & iTimeDiff & " minutes. Do you want to join?", vbYesNo + vbDefaultButton1, "Reminder") = vbYes Then
Call VBA.Shell("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe " & objWordHyperlink.Address)
sDismissSubject = objItem.Subject
objItem.ReminderSet = False
objItem.Save
End If

Exit For
End If
Next objWordHyperlink

Set objWordHyperlink = Nothing
Set objWordHyperlinks = Nothing

ElseIf iTimeDiff > 2 Then
sDismissSubject = objItem.Subject

Call VBA.MsgBox("Zoom appointment (" & objItem.Subject & ") in " & iTimeDiff & " minutes.", vbOKOnly, "Reminder")

objItem.ReminderSet = True
objItem.ReminderMinutesBeforeStart = 2
objItem.Save
End If
End If
End Sub

Function funcIsZoomAppt(objItem As Outlook.AppointmentItem) As Boolean
Dim objWordHyperlinks As Word.Hyperlinks
Dim objWordHyperlink As Word.Hyperlink

funcIsZoomAppt = False
If VBA.Left(objItem.Location, VBA.Len("https://[Redacted].zoom.us/j/")) = "https://[Redacted].zoom.us/j/" Then
funcIsZoomAppt = True
Else
Set objWordHyperlinks = objItem.GetInspector.WordEditor.Hyperlinks

For Each objWordHyperlink In objWordHyperlinks
If VBA.InStr(1, objWordHyperlink.Address, "zoom.us/", vbTextCompare) And objItem.Subject <> "" Then
funcIsZoomAppt = True
Exit For
End If
Next objWordHyperlink

Set objWordHyperlink = Nothing
Set objWordHyperlinks = Nothing
End If
End Function

 

[schwarznavy]

Hello Sentinels,

Just getting around to trying this out. Did you possibly leave something out?

I'm getting en error with that says "User-defined type not defined." The Visual Basic editors then pops up. "Private Sub Application_Reminder(ByVal objItem As Object)" is highlighted yellow, and "objWordHyperlinks As Word.Hyperlinks" is selected.

I will try to tinker with it.

 

[miaturner95]

this can be quite handy, to filter out unnecessary links, especially in large group chats. I have found this link for something similar