How can i make a Button that takes the email address in a ListBox and transfers it to Outlook to send an email? (Not to save the address, just to send an email).
How can i make a Button that takes the email address in a ListBox and transfers it to Outlook to send an email? (Not to save the address, just to send an email).
Last articles
First of all, there are two kinds of list boxes. One from the “Control Toolbox” toolbar and the other from the “Forms” toolbar. The macro below assumes you are using the listbox from the Control Toolbox toolbar and its name is Listbox1.
Put a command button (CommandButton1) from the Control toolbox toolbar on your sheet and add the code below. Double click on your command button and replace the default code with the code below.
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) As Long
Sub Commandbutton1_Click()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
‘email address
Email = ListBox1.Value
Subj = “The subject of the email”
Msg = “Message body of the email”
‘Create the URL
URL = “mailto:” & Email & _
“?subject=” & Subj & “&body=” & Msg
‘Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, _
vbNullString, vbNullString, vbNormalFocus
End Sub
This will open MS-Outlook and create an email with your selected address from Listbox1. You can define the Subject and Message of the email by assigning what you want to the Subj and Msg variables in the code.
Here is the macro that will enables you to open new email and prepare to send
‘ Load the default mail client or the default browser and go to a specified page!
‘……………………………….…
‘Insert two buttons into a form and try them, the first will open the default browser at the specified location, the second will open the default mail client and set the address to the specified send mail address
‘ Usage
Private Sub Command1_Click()
GoToInternet Me, “http://www.welford-costelloe.com”, Normal
End Sub
Private Sub Command2_Click()
GoToInternet Me, “mailto:dwc99@home.com?SUBJECT=Hello This is cool”, Normal
End Sub
‘ Decleration
Option Explicit
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) As Long
Private Declare Function FindExecutable Lib “shell32.dll” Alias “FindExecutableA” (ByVal lpFile _
As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
Public Enum T_WindowStyle
Maximized = 3
Normal = 1
ShowOnly = 5
End Enum
‘ Function
Public Sub GoToInternet(Parent As Form, URL As String, WindowStyle As T_WindowStyle)
Dim lngReturn As Long
If fsGetBrowserInfo Then
lngReturn = ShellExecute(Parent.hwnd, “Open”, URL, “”, “”, WindowStyle)
If lngReturn < = 32 Then ' Error
MsgBox "Web Page not Opened", vbExclamation, "URL Failed"
End If
Else
'* nope no Browser
MsgBox "Could not find associated Browser", vbExclamation, "Browser Not Found"
End If
End Sub
Public Function fsGetBrowserInfo() As Boolean
Dim strFileName As String, strDummy As String
Dim strBrowserExec As String * 255
Dim lngRetVal As Long
Dim intFileNumber As Integer
' Create a HTML file
strBrowserExec = Space(255)
strFileName = "C:TEST.HTM"
intFileNumber = FreeFile
Open strFileName For Output As #intFileNumber
Write #intFileNumber, " ”
Close #intFileNumber
‘ Do we have an associated application?
lngRetVal = FindExecutable(strFileName, strDummy, strBrowserExec)
MsgBox “Found Browser: ” & strBrowserExec
strBrowserExec = Trim(strBrowserExec)
‘ If an application is found return True
If lngRetVal <= 32 Or strBrowserExec=”" Then ‘ Error
fsGetBrowserInfo = False
Else
fsGetBrowserInfo = True
End If
Kill strFileName
End Function
VBAXLMan