Excel VBA - Email - transfer to Microsoft Outlook?

Question

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).

Cozmosis · Accepted Answer

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.

VBAXLMan · Answer

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, "<HTML> <\HTML>"

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

? · Answer

Use the import/export function. Find the location where you have the old emails and import them using File==>Import and Export, then click Import from another program or file. After that, select Personal Folder File(.pst) and navigate to the location holding the file. On the next screen, you can choose what folders you want to copy over.

Trending News

Excel VBA - Email - transfer to Microsoft Outlook?

3 Answers