CODEWITHSUNDEEP
vbaemailexcel
Andres Felipe Martinez
Andres Felipe Martinez

Reputation: 323

Avoid triggering the mailto hyperlink in excel-vba

Good morning everybody. Thanks for your attention and apologies in advance if this has been asked before.

I have a spreadsheet with several contacts and emails. At work, we send emails to these contacts on a regular basis, tailored through some specific templates. I've created a code that, when you click a mailto: hyperlink, a small form with a dropdown menu shows up so we can select the template to use, and when clicking a command button, it composes an email using Outloook, populating the body of the email with the template and the contact, company name, subject, etc.

All of that works perfectly.

However, it not only opens the userform; it also opens a new, blank email, as it would without the code.

Is there any way to prevent that from happening? To only execute my code when clicking the email address?

Thanks in advance

Upvotes: 2

Views: 625

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

No you cannot cancel the FollowHyperlink. This has been answered before.

Alternative

Use Worksheet_SelectionChange(ByVal Target As Range) event and trap the particular range which has email and in this event launch your form. Let's say you email hyperlinks are in range B2:B30. Then do this

A) Remove all hyperlinks from those cells. See This

B) (Optional) If you want to give them a hyperlink appearance then color the font of the cells as Blue and Underline it.

C) Paste this code in the worksheet code area

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B30")) Is Nothing Then
        MyForm.Show
    End If
End Sub

Upvotes: 2

Related Questions