On Jon Fournier's blog there's a VBA procedure to create a mailto: link in a macro. I like it but I feel it could use some more detail. I'll present an alternate with a few changes.
Default Subject and Body
His procedure allows you to specify a blank subject and body when calling it. I prefer to use a default subject and body, especially since the procedure is part of a larger tool. I can prefill in the subject and body with information I (as the developer) need.
Email Validation
There is no validation on the email address whatsoever. I've already written code that validates email addresses and we'll put it to good use here.
URL Encoding
The body is not URL encoded, so (when I tested it) words will drop off the body and not appear in the email. We're going to URL encode the body string so that the entire text will appear in the message. We're also going to urlencode the subject line as well.
API Usage
The code uses a Windows API to run the mailto link. I prefer the Windows Script Host Object Model (WSHOM) to run the link. To me it's simpler. I didn't check if there's a difference in speed, it's just a personal preference.
The StartEmail Procedure
The code starts by assuming a subject and body. I tried using a constant to define the name of the app (as the subject) but VBA won't allow that. See below for additional functions needed by this procedure.
Sub StartEmail(toAddr As String, Optional subject As String = "My App", _
Optional body As String = "Hello World!")
Dim URL As String
Dim oShell As Object
' validate email
If IsValidEmail(ToAddr) Then
URL = "mailto:" & toAddr
URL = URL & "?subject=" & URLEncode(subject)
URL = URL & "&body=" & URLEncode(body)
Set oShell = GetShell
oShell.Run URL
Else
MsgBox "email address invalid."
End If
End SubAfter validating the email address (see Validate email address below), the URL to be run is constructed. The message body is URL encoded so that nothing is dropped. For example, if I pass "Hello World" to the original procedure, the body will say "Hello" and "World" will be dropped because the space in the URL isn't encoded.
Finally, to run the URL I use the WSHOM's shell scripting abilities. See Shell Scripting using VBA and the Windows Script Host Object Model for more code samples. Like I said earlier, I find it simpler than the Windows API.
To URL encode the message body, download the URLEncode function and change this line:
erg = Replace(erg, Chr(i), "+")
to this:
erg = Replace(erg, Chr(i), "%20")
Validate email address
To validate email addresses, I use a web API. This goes further than Regex (or trying to hardcode valid domains) by actually asking the mail server if the email address is valid.
Function IsValidEmail(emailAddress As String) As Boolean
Dim xml As Object
Dim result As String
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
xml.Open "GET", _
"http://www.webservicex.net/ValidateEmail.asmx/IsValidEmail?Email=" & emailAddress, False
xml.send
result = xml.responseText
' parse result for response
IsValidEmail = (InStr(result, "true") > 0)
End FunctionWSHOM Shell Object
To return the Shell Object I use this function.
Function GetShell() As Object On Error Resume Next Set GetShell = CreateObject("WScript.Shell") End Function
Sample usage
Sub TestStartEmail() StartEmail ("someone@somewhere.com", "Excel Application", "My Support Question:") End Sub
About JP
5 Comments:
Scripting VBA Guru,
I'm total novice and a newb in need of help. I've only recently discovered Macros and there are tons I would love to use but can't figure out the basics! Am trying to get the macro below from 'The Scripting Guys' (http://technet.microsoft.com/en-us/library/ee692878.aspx) to work but have had no luck. I can get as far as cutting and pasting the code into the VBA editor while ThisOutlook Session is highlighted with the drop-downs 'General' and 'Item send' showing on top of that window, I save the Macro and try to run it.
I get 'Compile Error: Invalid Outside Procedure'. I'm running Vista and Outlook 2007.
I was wondering if you could post (or direct me to a post) that shows complete newbs like myself a very basic,step by step procedure for installing and using macros in Outlook 2007. I've scoured the internet and haven't found anything simple enough for non-programmers to understand.
Also – interesting music tastes you have. Am a bit of a cross-breed myself loving both Rock and classical. Hoping you can help,
Thanks.
Stephen J
__________________________________________________________________________________
Const olFolderContacts = 10
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items
intCount = colContacts.Count
For i = 1 To intCount
If TypeName(colContacts.Item(i)) = "DistListItem" Then
Set objDistList = colContacts.Item(i)
Wscript.Echo objDistList.DLName
For j = 1 To objDistList.MemberCount
Wscript.Echo objDistList.GetMember(j).Name & " — " & _
objDistList.GetMember(j).Address
Next
Wscript.Echo
End If
Next
There are a lot of things going on here.
First, The Scripting Guys column usually posts code meant for scripting (VBScript), not VBA.
That's the cause of the compile errors, because there's no 'Wscript' in VBA. There's also no variable declaration in VBScript.
Also, you should never use CreateObject("Outlook.Application") as a means for instantiating the native Application object. See More Outlook.Application Best Practices for my thoughts on this.
See Outlook version of GetDistListMembers for sample VBA code you can use to extract distribution list members.
Thanks JP – I really appreciate your help but I just don't know enough to make sense of it all. I get that I was using the wrong kind of script but the rest is greek to me. I know basically enough to cut and past the code and pray it works. In this case I was hoping someone has come up with code to simply search Outlooks contacts folder and list all distribution lists members and emails. Basically I have loads of people all on different lists, i.e. some people appear in 2 or three lists.
Unsubscribing people is up to me so when I get a request to unsubscribe I have to search through all the lists (which are quite large) and this becomes quite time consuming. Just looking to be able to click a button, pull up a summary of all people on all lists and delete those I need to delete.
Thanks again.
Best,
Stephen
JP,
Just a new guy learning VBA, but in my use of your code, I had to hack it up a little for my purposes; I'm sure you expected that. I wanted to let you know, though, that I found that
doesn't actually work well, since on a multi-word subject, which is fairly common, the VBA code actually truncates to the first white character, meaning that the rest of the subject line and all of the body is cut off. To resolve this, I simply changed the above line to
This allows parsing of both the subject line and the body, and prevents errors from wiping the body in realtime if the subject isn't restricted to one word.
You're right, I urlencoded the body but not the subject.