Excel

Email Link on a Userform

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Create an Email link using a label on a userform 

Discussion:

If you have alot of user who work with useforms you created, you may want to provide a quick and easy way to let them email you directly from the userform. This code snippet allows your users to click on a label that contains an email address, and open the default email application (Outlook probably) and fill in the email address for them. All the user have to do is fill in the subject, and the comments they wish to send. 

Code:

instructions for use

			

Option Explicit '====================================== ' From the book: ' VBA and Macros for Microsoft Excel '====================================== Private Declare Function ShellExecute& Lib "shell32.dll" _ Alias "ShellExecuteA" (ByVal hWnd As Long, _ ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, _ ByVal nShowCmd As Long) Const SW_SHOWNORMAL = 1 Private Sub lblEmail_Click() ' Label's caption is the email address: Dim szEmailAddy As String szEmailAddy = Me.lblEmail.Caption ShellExecute 0&, "open", "mailto:" & szEmailAddy, _ vbNullString, vbNullString, SW_SHOWNORMAL Unload Me End Sub '------------------------------------ 'Standard Module: Sub ShowEmailForm() frmEmail.Show End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > USERFORM from the menubar
  5. Rename the userform: frmEmail
  6. Add a Label and rename it lblEmail
  7. Change the labels caption to a valid email address:
  8. Double Click on the label
  9. Paste code into the userform code module
  10. -------------------------------------------------------
  11. Select INSERT > MODULE from the menubar
  12. Paste code into the right pane
  13. Copy the {ShowMyForm} code into the standard module
  14. Press Alt+Q to return to Excel
  15. Save workbook before any other changes
 

Test the code:

  1. Go to TOOLS > MACRO > MACROS
  2. Select (ShowEmailForm) from the dialog box
  3. Press 9Run)
  4. The userform will be displayed
  5. Click on the label to send an email to the address listed on the label
 

Sample File:

FormLabel_Email.zip 10.31KB 

Approved by mdmackillop


This entry has been viewed 227 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express