I have a word template that I open (as a new document) with excel VBA.
This template has bookmarks in it and I can copy and paste text or cell values from excel to the template to fill it out
automatically. The one area I am having problems is in trying to create an email hyperlink in one of my bookmarks.
Does anybody have any suggestions?
Here is an example of the code I am using that does not work correctly:
Dim wb As Excel.Workbook
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim LinkName As String
Dim LinkAddress As String
Dim SPE, SCM, Purch, SQE, Vendor, Veri As Boolean
Set wb = ActiveWorkbook
Path = wb.Path & "Test.dotm"
'On Error GoTo ErrorHandler
'Create a new Word Session
Set wdApp = CreateObject("Word.Application")
'Open document in word
Set myDoc = wdApp.Documents.Add(Path)
'Activate word and display document
.Visible = True
.ActiveWindow.WindowState = 0
LinkName = "Name Here"
LinkAddress = "Email@Address.com"
.Hyperlinks.Add Anchor:=Selection.Range, Address:="Mailto:%20" & _
LinkAddress, SubAddress:="", ScreenTip:="", TextToDisplay:=LinkName, Target:=""
I have tried several variations on this and cannot seem to get it to work. I can paste the "LinkName" to the
bookmark and select the bookmark just fine, but I cant get the hyperlink to attach.
Any help would be greatly
P.S. I am using Office 2007 for this project. The Macro starts and runs in excel which then opens the word
document. I wasn't sure if any of that was clear or not.