Correcting URL in VBA code

DanishViking

New Member
Joined
Feb 11, 2020
Messages
1
Office Version
365
Platform
Windows
Hi,

This is actually a VBA code for use in Power Point. The purpose is that employees enter some initials and then the profile picture is fetched from the Corporate Microsoft SharePoint site and inserted into a square with rounded corners.

However, I am having difficulty getting the URL address to function properly with inserting the Initials (XXXX). The format should be: https://companyname.sharepoint.com/sites/HQ/_layouts/15/UserPhoto.aspx?size=m&accountName=Initials@companyname.com"

Thanks for your help and inputs!

See code snippet below:

[CODE=vba]Public Sub InsertSharepointPic()

'Get user initials:
Dim usr As String
usr = UCase(CreateObject("wscript.network").UserName)

'Ask user for initials
initials = InputBox(Prompt:="Initials of employee", Title:="Insert phonebook picture", Default:=usr)

If initials = "" Then
Exit Sub
End If

Dim r_margin As Double, l_margin As Double, t_margin As Double, b_margin As Double
Call getSlideDim(r_margin, l_margin, t_margin, b_margin)

ContinueProcess:

'make rounded rectangle to fill
ActiveWindow.Selection.SlideRange.Shapes.AddShape(msoShapeRoundedRectangle, l_margin, t_margin, 140, 122).Select

'fill rounded rectangle with picture
With ActiveWindow.Selection.ShapeRange

.Line.Visible = msoFalse
.Fill.Transparency = 0#
.Fill.Visible = msoTrue
.Fill.UserPicture [COLOR=rgb(184, 49, 47)]"https://companyname.sharepoint.com/sites/HQ/_layouts/15/UserPhoto.aspx?size=m&accountName=" & initials & "@companyname.com"[/COLOR]

End Sub[/CODE]
 

Forum statistics

Threads
1,085,185
Messages
5,382,203
Members
401,779
Latest member
Thonor

Some videos you may like

This Week's Hot Topics

Top