Hi -

I'm using the following code to send an email when a user clicks on a
link ... the code works fine, EXCEPT that the Visual Basic Editor
opens as well!

Why would that happen? and more importantly ... how do I prevent it
from happening??

[Credit to Ron deBruin for the original code!]

In the ThisWorkbook module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
ByVal Target As Hyperlink)

On Error Resume Next
Application.Run Target.TextToDisplay
If Err.Number = 0 Then Exit Sub ' This is leftover from the
original code

End Sub


In a standard code module:

Sub HNLR()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Q: " & ActiveCell.Offset(0, -4).Value
.body = "[Please enter your question/comment here...]"

'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

It may be the application.run

For example, I have a workbook that I always want the VBE editor to open
when the workbook is opened. So have have in the workbook code area the
following:

Private Sub Workbook_Open()
Application.Goto "demo"
End Sub

Where "demo" is the name of a sub in a standard module.
--
Gary''s Student - gsnu200907


"Ray" wrote:

Hi -

I'm using the following code to send an email when a user clicks on a
link ... the code works fine, EXCEPT that the Visual Basic Editor
opens as well!

Why would that happen? and more importantly ... how do I prevent it
from happening??

[Credit to Ron deBruin for the original code!]

In the ThisWorkbook module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
ByVal Target As Hyperlink)

On Error Resume Next
Application.Run Target.TextToDisplay
If Err.Number = 0 Then Exit Sub ' This is leftover from the
original code

End Sub


In a standard code module:

Sub HNLR()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Q: " & ActiveCell.Offset(0, -4).Value
.body = "[Please enter your question/comment here...]"

'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Gary -

Makes sense ... but how do I fix it? I tried changing Application.Run
to Application.GoTo -- same result ...

Basically, when the user clicks on a hyperlink (which is someone's
name), a macro (named the same way) should be fired ...

the code above works great, EXCEPT that the VBE opens also, which will
confuse my users ...

TIA,
ray

I don't get the VBE opening.
Are you getting any kind of error messages? rem out the ON ERROR RESUME
NEXT line as this is hiding issues from you

"Ray" wrote:

Hi -

I'm using the following code to send an email when a user clicks on a
link ... the code works fine, EXCEPT that the Visual Basic Editor
opens as well!

Why would that happen? and more importantly ... how do I prevent it
from happening??

[Credit to Ron deBruin for the original code!]

In the ThisWorkbook module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
ByVal Target As Hyperlink)

On Error Resume Next
Application.Run Target.TextToDisplay
If Err.Number = 0 Then Exit Sub ' This is leftover from the
original code

End Sub


In a standard code module:

Sub HNLR()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Q: " & ActiveCell.Offset(0, -4).Value
.body = "[Please enter your question/comment here...]"

'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I don't get the VBE opening.

Same here

No problem in O 2003 and also not in O 2010

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Patrick Molloy" wrote in message
...
I don't get the VBE opening.
Are you getting any kind of error messages? rem out the ON ERROR RESUME
NEXT line as this is hiding issues from you

"Ray" wrote:

Hi -

I'm using the following code to send an email when a user clicks on a
link ... the code works fine, EXCEPT that the Visual Basic Editor
opens as well!

Why would that happen? and more importantly ... how do I prevent it
from happening??

[Credit to Ron deBruin for the original code!]

In the ThisWorkbook module:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
ByVal Target As Hyperlink)

On Error Resume Next
Application.Run Target.TextToDisplay
If Err.Number = 0 Then Exit Sub ' This is leftover from the
original code

End Sub


In a standard code module:

Sub HNLR()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "Q: " & ActiveCell.Offset(0, -4).Value
.body = "[Please enter your question/comment here...]"

'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display '.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Interesting ... is there a VBE setting that would cause this issue? I
just tried it again, same result ... VBA springs open, as does the new
Outlook mail.

Thanks for looking into it!

ray