Efficiently copy text from Excel to Word, hyperlink if applicable

Question

What I'm trying to do
I'm trying to copy contents of different Excel cells into their respective Word bookmarks. Then this filled-in Word document gets pasted into an Outlook email. If the cell has a URL in it, I want it to be a clickable hyperlink in the email.

The problem
If the cell's value was a URL, then while it was un-hyperlinked before sending the email, it appeared hyperlinked (for some people) when the email was received. However, others users saw what looked like a clickable link (blue and underlined), but were unable to click because it wasn't actually hyperlinked. My guess is that certain mail programs recognize the URL and automatically hyperlink it while others do not.

What I've tried
I figured the solution was to make sure the URL was hyperlinked before sending. I tried a few forms of .PasteSpecial and .PasteAndFormat with the URL already hyperlinked in Excel. I got close, but either got an unwanted carriage return or no hyperlink. My latest try is a 2-line hack:

'put the cell contents in (sans formatting)
   BKMRange.Text = ActiveCell.Value

'If it looks like a hyperlink, then hyperlink it
   If UCase(Left(ActiveCell.Value, 4)) = "HTTP" Or UCase(Left(ActiveCell.Value, 4)) = "WWW." Then TempEmailDoc.Hyperlinks.Add BKMRange, ActiveCell.Value

The problem with this is that if the cell is more than just a URL (e.g. "This is your URL: www.google.com"), the URL won't get hyperlinked, since the first 4 characters are not "HTTP" or "WWW.". I could do a more robust search for "HTTP" or "WWW.", but then the code becomes much more complicated. There must be a more efficient way.

My question
How can I efficiently copy cell contents, but preserve hyperlinks if the cell contains them?

Thanks!

It is the sending email program's editor that puts the hyperlink in the emails. The receiving programs will probably preserve them. — Paul Ogilvie
Can you explain, then, why only some people replied saying they couldn't click the link? I thought it might have been user error, but since they replied, I could see the email text below that they had received, and in that text the URL was formatted like a link but was not hyperlinked and therefore not clickable. — Alex Silverman
If you send it correctly, but they receive it wrong then it seems something with their server or email client. Ask them what client they use. — Paul Ogilvie
Unfortunately, we can't yet follow up with those folks who had issues. However, if there's a workaround, such as the "hack" I have now, that would be fine. In other words, I'm trying to not have to rely on their client to automatically hyperlink, and instead just do the hyperlinking myself — Alex Silverman

Paul Ogilvie Paul Ogilvie · Accepted Answer · 2016-04-18T17:53:25

You can use the cell's Hyperlinks collection to learn whther the cell contains a hyperlink:

Sub test()
    Dim i
    i = Selection.Hyperlinks.Count
    If (i > 0) Then MsgBox Selection.Hyperlinks(1).Address
End Sub

Now that you know a cell is (contains) a hyperlink, you can insert it as a hyperlink in Outlook. I assume you work in Outlook VBA and open the worksheets in Outlook VBA.

Note that when you enter a proper URL in Excel it will turn it automaticlly into a hyperlink.

Efficiently copy text from Excel to Word, hyperlink if applicable

1 Answers