CODEWITHSUNDEEP
vbaexcelhyperlinkoutlook
Alex Silverman
Alex Silverman

Reputation: 203

Efficiently copy text from Excel to Word, hyperlink if applicable

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!

Upvotes: 0

Views: 1033

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

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.

Upvotes: 0

Related Questions