Creating Word Document w/ hyperlinks via Excel VBA

[Dave Punky]

Hi all,

Hopefully somebody can help me with regards to this.

Basically I am wanting to create a full Word document on the press of a button basically within a userform in VBA. It transfers some of the information in said userform into the document, however that isn't the issue.

The word document I am trying to create has to include hyperlinks among other things and I was wondering what the best way of doing this was? When creating an email from Excel VBA you can do .HTMLBody which solves that problem as you just write it as a HTML line, but is there a way of doing this when transferring text into Word?

Basically to help explain what I'm trying to do, this is the information I have in my userform:

Reference Number: 12345
Name: Mr Smith

It basically needs to transfer the information into Word so it ends up like:

---

Dear Mr Smith,

With regards to reference 12345, something has happened. You can check the status here.

Regards.

Note: Here would be where the hyperlink is, to Google for example.

---

The idea is that it auto generates this and also saves it in a directory I've created. I've got it creating documents and saving these to the directory, its just the hyperlinks which are causing the problem and if I can format the whole thing in HTML I'll be plain sailing if that's possible. Obviously considering how little in actuality I'm doing, if there's a set of code with minimal actions for creating a Word document with this information then that would be of great benefit too.

Has anybody done this previously or knows how to do this? It would be a real benefit for me to know how to do this and save me about a million hours of copy/paste every day.

Many thanks.

 

[Trevor G]

Have you looked at recording a macro in Word to create a hyperlink in a document and then look at the code, as it should supply the answer.

Here is a recorded macro that creates a hyperlink to the BBC website.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro12()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Macro12 Macro</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'</SPAN><br>    Selection.TypeText Text:="Hyperlink"<br>    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend<br>    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _<br>        "http://www.bbc.co.uk/", SubAddress:="", ScreenTip:="BBC", TextToDisplay _<br>        :="Hyperlink"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Dave Punky]

Hi Trevor,

Thanks for your reply, however the issue I have is that I'm trying to create a hyperlink from text in VBA, so it's not even in Word to begin with and so there is no selection.

I have 3 large strings which eventually get merged together to make the word document as described above, and all of these contain hyperlinks. None of the text is actually on a worksheet of any kind or another document, it's all created from the VBA code to create a fresh document.

So basically I have 3 strings like so:

head = "Address" & vbNewLine & "Website: " & # Hyperlinked Website #
 
main = "Dear " & Me.tb_name.value & "Rest of text"
 
foot = "Standard footer"

This all gets put together at the end and created into a Word document using the following:

    Dim head As String
    Dim foot As String
    Dim main As String
 
head = "Address" & vbNewLine & "Website: " & # Hyperlinked Website #
 
main = "Dear " & Me.tb_name.value & "Rest of text"
 
foot = "Standard footer"
 
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim i As Integer
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Add
 
    With wrdDoc
    .Content.Font.NameAscii = "Tahoma"
        .Content.Font.Size = 10
 
         ### Text Content Here ###
         'i.e - .Content.InsertAfter = head & main & foot
 
        .SaveAs ("C:\Desktop\" & Me.tb_name.value & ".doc")
    End With
    Set wrdDoc = Nothing
    Set wrdApp = Nothing

Hopefully I'm explaining thorougly enough what I'm trying to do, in my mind it sounds straightforward enough. If there was a way to just write the string as HTML obvously that would be easier for me but I know that probably only works for outlook.

 

[Trevor G]

OK think of this do you have anything like FrontPage or Dreamweaver available to you as HTML applications as you could then create the document in it and look at the code and that would enable you to have the HTML source you need.

 

[Dave Punky]

Hi Trevor,

I know exactly how it would be written in HTML, I can do that myself as I can write in HTML.

The issue is if I write in HTML at the moment, when the Word document is created it'll include all the HTML code and will not create hyperlinks.

When opening an email to an Outlook Application, Excel actually has a function called .HTMLBody which enables you to write in HTML and when you open the email, it is all pre-formatted in the correct manner including all links. This function doesn't seem to exist when trying to convert content to the Word Application, which is where my problem lies as I don't know what to do to hyperlink.

In HTML I would just be writing like so (ignore the #'s):

Text Line 1, <#a href="http://google.com">Link<#/a>

However if I currently write that, it'll come out like this in Word:

Text Line 1, <#a href="http://google.com">Link<#/a>

And obviously that's not what I want, I want it to do this:
Text Line 1, Link

 

[Trevor G]

Dave like you I am aware of the options for Outlook, but in my earlier reply I stated I recorded a macro in Word to get the code so that when you create a word application and add a new document you should be able to use the recorded code to add the hyperlink.

Am I really missing your point.

You want to create a word document from an Excel UserForm (Yes)
You are adding various Headers and Footers and want to do a basic letter (Yes)
In the letter you want to add a Hyperlink to a website or what ever (?)
Then save the document to a file location with a name(Yes)

 

[Dave Punky]

I'm not sure because the code you've given me above I cannot use, as the hyperlink is created at the same time as the Word document so from the same set of VBA code as creating the .doc file. Maybe it's a lack of understanding on my part - but I can't do Selection.TypeText as there is not selection at that point.

The issue I also have is that because it's a string, it can't hold a hyperlink within it as such at that point, which is why I thought the HTML method would be a good method around this if possible.

 

[Norie]

Dave

Why are you creating the document in one go?

Even if you do that you could create the hyperlink afterwards using code similar to that Trevor posted.

 

[Norie]

Here's a very simple example of the sort of thing I mean.

Sub test()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdPar As Word.Paragraph
Dim strURL As String
Dim strFriendlyName As String
 
    strURL = "http:\\www.mrexcel.com"
 
    strFriendlyName = "MrExcel"
 
    Set wrdApp = CreateObject("Word.Application")

    wrdApp.Visible = True

    Set wrdDoc = wrdApp.Documents.Add
 
    With wrdDoc
        .Content.Font.NameAscii = "Tahoma"
        .Content.Font.Size = 10
 
        .Paragraphs.Add
 
        Set wrdPar = .Paragraphs(1)
 
        With wrdPar.Range
            .Text = "Dear MrExcel"
            .Hyperlinks.Add Anchor:=.Words(2), Address:=strURL, TextToDisplay:=strFriendlyName
        End With
 
        wrdPar.Range.InsertParagraphAfter
        
        Set wrdPar = .Paragraphs(2)
        With wrdPar.Range
            .Text = "Main Text"
            
        End With
 
        wrdPar.Range.InsertParagraphAfter
 
        Set wrdPar = .Paragraphs(3)
        With wrdPar
            .Range.Text = "Footer"
        End With
        .SaveAs ("C:\Test\" & "NewDocTest" & ".doc")
    End With
 
    Set wrdDoc = Nothing

    Set wrdApp = Nothing

End Sub

 

[Dave Punky]

Ok actually seeing that makes a lot more sense to me, so basically having all the text outlined first of all then getting it to find the text and hyperlink accordingly. I'll try that and let you know if I have any problems.

I apologise if I came across as rude in my earlier posts, it certainly wasn't my intention - just a lack of understanding on my part and not being able to picture what I needed to do in my head.

I appreciate all the help!