Hyperlink Help

[Clemo41]

Hi Guys,

Ive recently been working on a few things and haven't done VBA in over 10 years and i've got myself a little stuck.

I've tasked myself with the following, and hopefully someone has a very easy solution that doesn't fry my brain any further. I've manged to create a hyper link in Column "U" that once clicked selects data from the active row and from columns "B:K" and emails the data over. All works fine

What i want to do is add a second hyper link in column "AA" that will do the same but email over the active row data from "B:Z"
Also i would like it to include the column titles (currently in Row 4 but not a huge problem if not)

Is this at all possible?

Heres what i have manged so far: (dont laugh if its really bad)


Private Sub Worksheet_FollowHyperlink(ByVal Target As hyperlink)

Range("B" & ActiveCell.Row & ":K" & ActiveCell.Row).Select
MsgBox "Row " & ActiveCell.Row & " Has been emailed"

ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "Marketing Trigger."
.Item.To = "My Email Address"
.Item.Subject = "New Part Tracking"
.Item.Send
End With
End Sub

Thank you

[SamT]

Option Explicit

Dim SendMailBK As Boolean
Dim SendMailBZ As Boolean

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

If Target.Hyperlinks.Count Then
If Target.Address = "$U$1" Then 'Adjust address to suit
SendMailBK = True
ElseIf Target.Address = "$AA$1" Then
SendMailBZ = True
End If
End If

If SendMailBK Then EmailBK
If SendMailBZ Then EmailBZ
End Sub

Private Sub EmailBK()
'Put BK code here
SendMailBK = False
End Sub

Private Sub EmailBZ()
'Put BZ code here
SendMailBZ = False
End Sub
If this was going in one of my workbooks, I would swap the names of the Booleans and the Subs, because it makes better English.

[Clemo41]

Hi SamT,

Thank you for your help.

[Clemo41]

Hi, Do you know why i get 2 message box pop ups?



Option Explicit

Dim SendMailBK As Boolean
Dim SendMailBZ As Boolean

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)

If Target.Hyperlinks.Count Then
If Target.Column = 21 Then
SendMailBK = True
ElseIf Target.Column = 27 Then
SendMailBZ = True
End If
End If

If SendMailBK Then EmailBK
If SendMailBZ Then EmailBZ
End Sub

Private Sub EmailBK()
Range("B" & ActiveCell.Row & ":K" & ActiveCell.Row).Select
MsgBox "Row " & ActiveCell.Row & " Is ready to send"

ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "Marketing Trigger."
.Item.To = "Email"
.Item.Subject = "New Part Tracking"

End With
SendMailBK = False

End Sub

Private Sub EmailBZ()
Range("B" & ActiveCell.Row & ":Z" & ActiveCell.Row).Select
MsgBox "Row " & ActiveCell.Row & " Is ready to send"

ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Introduction = "Engineering Release."
.Item.To = "Email"
.Item.Subject = "New Part Tracking"

End With
SendMailBZ = False
End Sub

[SamT]

Because your Email Subs are selecting a range, AND you changed the code in the Selection Change sub from Target Addresses to Target Columns inside that selection. Two MsgBoxes indicates that you are running the Email sub twice.

Add this line as the first line in the Selection Change sub

If Target.Count > 1 Then Exit Sub

My mistake. I did not catch that the entire column(s) was to trigger the send mail process.

The other alternative, which I prefer, is to use the BeforeDoubleClick Event.

The only change needed is to replace this line

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Then, the mail sending process will only be triggered when double clicking a cell in U or AA