Use VBA to Create Clipboard Message with Clickable URLs

[Barklie]

Good morning,

I am trying to figure out how to use VBA to put a message with text and clickable URLs into the clipboard. To further complicate matters, I use a PutInClipboard workaround (which works fine) because PutInClipboard does not work for me unless my file explorer is closed. Below is what I have, which I think is a long way from working but demonstrates what I'm trying to achieve.

Sub CopyMessage()

'Dimension variables
Dim String2 As Range

'Assign variables
String1 = "Please use the URL below:" & Chr(13) & Chr(13)
With String2
    .Address = "www.google.com"
    .TextToDisplay = "Google"
End With
Message = String1 & String2

'Copy message to clipboard
ClipboardMessage = CreateObject("htmlfile").parentWindow.clipboardData.setData("Text", Message)


End Sub

The desired clipboard contents would be:

Please use the URL below:

Google


_______________________________________________

Gmail is the ultimate destination for the copied contents.


Thanks,
Barklie

 

[Dan_W]

Where are the contents going after you place them in the clipboard? Because URLs are not 'clickable', per se - they are text.

What is the actual end goal?

 

[Barklie]

Hi Dan,

Gmail is the ultimate destination for the copied contents. The routine puts together a message that can be copied into an email thread.

Regards,
Barklie

 

[Dan_W]

Like this?

 

[Dan_W]

Gmail will autodetect any URLs and add the hyperlinks for you - which is handy. The above output was made using the following assumption - that you had a number of cells containing hyperlinks as follows:

The following code will parse the selected cells, and copy the details to the clipboard.

Sub CopyLinksToClipboard()
    
    Dim Target As Range, TargetCell As Range, Message As String
    
    Set Target = Application.Selection
    Message = "Please use the URL below:" & vbNewLine & vbnewwline
    
    For Each TargetCell In Target.Cells
        If TargetCell.Hyperlinks.Count > 0 Then
            Message = Message & vbTab & "- " & TargetCell.Hyperlinks(1).TextToDisplay & " - " & TargetCell.Hyperlinks(1).Address & vbNewLine
        End If
    Next
    Call CreateObject("htmlfile").parentWindow.clipboardData.setData("Text", Message)
    
End Sub

Once pasted into gmail, Gmail will render the output as the above.

 

[Barklie]

Thanks Dan. That code worked but the clipboard contents did not paste as URLs on Gmail or MS Word. Below is how it spat out:

 

[Barklie]

I think I'm going to stick with a displayed hyperlink solution. Getting a text to the clipboard that has a hidden address is more trouble than it is worth. Thanks again for the help.

 

[Dan_W]

Thanks Dan. That code worked but the clipboard contents did not paste as URLs on Gmail or MS Word. Below is how it spat out:

That's how it displays until you send it - the email client that receives the email then renders the URL by adding hyperlinks. What you saw in my screen capture above is the received (therefore, the rendered) email.

There is a way of doing it, but it's a bit of work. Essentially, the content being saved to the clipboard would need to be converted in HTML anchor elements (hyperlinks) - that's because most (all?) emails these days are formatted using HTML code, and those using hyperlinks definitely are.

Another way of doing it would be to automate the composition and sending of the email via VBA which can be done, but I'm not 100% certain if Gmail lets you connect to their email servers for programmatic emailing. I feel that I read somewhere that they don't allow this anymore, and insist on 2 factor authentication, etc etc. I'm not sure.

So all in all, I figured that the easiest solution would be just to display the URL and let gmail render the hyperlink automatically upon sending. I perhaps should have explained my thinking from the outset, my apologies.