How to start the default mail program using Excel VBA?

[Michalakis Michael]

From Excel (using VBA) I want to click a button and start the default mail
program installed on the computer. After this I want to prepopulate the TO
line in Outlook Express or Outlook (depending on what's installed on the
machine) with a few e-mail addresses (stored in my spreadsheet). Any sample
code for this please?

Thanks in advance
Michalakis Michael
(e-mail address removed)

 

[Karl E. Peterson]

From Excel (using VBA) I want to click a button and start the default
mail program installed on the computer. After this I want to
prepopulate the TO line in Outlook Express or Outlook (depending on
what's installed on the machine) with a few e-mail addresses (stored
in my spreadsheet). Any sample code for this please?

Shortform answer: ShellExecute a mailto link.

Somewhat longer form (beware of wordwrap!):

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 UrlEscape Lib "Shlwapi.dll" Alias "UrlEscapeA" (ByVal
pszURL As String, ByVal pszEscaped As String, ByRef pcchEscaped As Long, ByVal
dwFlags As Long) As Long

Private Const URL_DONT_ESCAPE_EXTRA_INFO As Long = &H2000000

Private Sub PrepareEmail(ByVal AddrTo As String, Optional ByVal AddrCc As String,
Optional ByVal Subject As String, Optional ByVal Body As String)
Dim Link As String

' BEWARE:
' Maximum URL Length Is 2,083 Characters in Internet Explorer
' http://support.microsoft.com/default.aspx?scid=KB;en-us;q208427
Link = "mailto:" & AddrTo & _
"?cc=" & AddrCc & _
"&subject=" & Subject & _
"&body=" & EscapeURL(Body)
Call OpenDoc(Link)
End Sub

Private Function EscapeURL(ByVal URL As String) As String
' Purpose: A thin wrapper for the URLEscape API function.
Dim EscTxt As String
Dim nLen As Long

' Create a maximum sized buffer.
nLen = Len(URL) * 3
EscTxt = Space$(nLen)

If UrlEscape(URL, EscTxt, nLen, URL_DONT_ESCAPE_EXTRA_INFO) = 0 Then
EscapeURL = Left$(EscTxt, nLen)
End If
End Function

Private Function OpenDoc(ByVal DocFile As String) As Long
Dim nRet As Long
' Uses the default verb if available, and "open" otherwise
nRet = ShellExecute(0&, vbNullString, DocFile, vbNullString, vbNullString,
vbNormalFocus)
Debug.Print "ShellExecute: "; nRet
OpenDoc = nRet
End Function

It looks more involved than it really is. Trust me.

 

[Michalakis Michael]

Indeed this worked!

Thanks
Michalakis Michael

Shortform answer: ShellExecute a mailto link.

Somewhat longer form (beware of wordwrap!):

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 UrlEscape Lib "Shlwapi.dll" Alias "UrlEscapeA"
(ByVal
pszURL As String, ByVal pszEscaped As String, ByRef pcchEscaped As Long,
ByVal
dwFlags As Long) As Long

Private Const URL_DONT_ESCAPE_EXTRA_INFO As Long = &H2000000

Private Sub PrepareEmail(ByVal AddrTo As String, Optional ByVal AddrCc
As String,
Optional ByVal Subject As String, Optional ByVal Body As String)
Dim Link As String

' BEWARE:
' Maximum URL Length Is 2,083 Characters in Internet Explorer
' http://support.microsoft.com/default.aspx?scid=KB;en-us;q208427
Link = "mailto:" & AddrTo & _
"?cc=" & AddrCc & _
"&subject=" & Subject & _
"&body=" & EscapeURL(Body)
Call OpenDoc(Link)
End Sub

Private Function EscapeURL(ByVal URL As String) As String
' Purpose: A thin wrapper for the URLEscape API function.
Dim EscTxt As String
Dim nLen As Long

' Create a maximum sized buffer.
nLen = Len(URL) * 3
EscTxt = Space$(nLen)

If UrlEscape(URL, EscTxt, nLen, URL_DONT_ESCAPE_EXTRA_INFO) = 0 Then
EscapeURL = Left$(EscTxt, nLen)
End If
End Function

Private Function OpenDoc(ByVal DocFile As String) As Long
Dim nRet As Long
' Uses the default verb if available, and "open" otherwise
nRet = ShellExecute(0&, vbNullString, DocFile, vbNullString,
vbNullString,
vbNormalFocus)
Debug.Print "ShellExecute: "; nRet
OpenDoc = nRet
End Function

It looks more involved than it really is. Trust me.