Email from Excel Macro

Posted on by

You can email from Excel with a macro using an email address in your VBA code or in your Excel file.  There are several different ways of doing this. This post looks at the one using a Windows Application Programming Interface (API) function called ShellExecute.

Background

A search on the internet shows that there are several methods of emailing from Excel:

  • You can email a workbook or a PDF version of it.
  • You can email using code that specifically uses Outlook.
  • You can email using code which uses an object library called CDO that exposes the interfaces of the Messaging Application Programming Interface (MAPI). These methods require knowledge of your SMTP server.
  • You can email using a Windows Application Programming Interface (API) function called ShellExecute.

In my situation I wanted to email from Excel using a macro used by many different people in more than one country in a business context where Lotus Notes rather than Outlook was the email program. Furthermore, I didn’t know what the SMTP server was nor could I rely on that being the same for everyone. I would prefer not to be emailing an Excel file to everyone, but that would be quite an easy option from a coding perspective. So that left me with the ShellExecute function method as my best choice.

There was one other thing that I wanted from this exercise. I wanted to be able to reuse the code so that if I needed to email from Excel again I could just import this piece of code and make a simple call to it.  I wanted it able to handle a variety of circumstances while still being simple and relatively intuitive as to how to use it.

The rest of the post is split up into three sections:

  1. The Email from Excel macro (or to be more specific the VBA function)
  2. How to use the function
  3. Further information

The Email from Excel Macro

Here is the Email from Excel macro. It is a function which returns True if it didn’t encounter any errors in sending the email or else it returns False.

#If VBA7 Then
 Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
   Alias "ShellExecuteA" (ByVal hwnd As LongPtr, _
   ByVal lpOperation As String, ByVal lpFile As String, _
   ByVal lpParameters As String, ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As LongPtr
#Else
 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
#End If
Public Function SendShortEmail(EmailAddress As String, EmailSubject As String, ShortMessage As String, Optional LotusNotes As Boolean = True, Optional Outlook As Boolean = False, Optional SendKeysCode As Variant, Optional DelaySeconds As Integer = 2) As Boolean
' Sends a short email message

Dim EmailURL As String
Dim CheckSend As Long
Dim TimeDelay As String
On Error GoTo EmailFailed
If IsMissing(SendKeysCode) Then
    If Outlook Then
        SendKeysCode = "^~" ' Ctrl + Enter
    ElseIf LotusNotes Then
        SendKeysCode = "%1" ' Alt + 1
    Else
        SendKeysCode = "%s" ' Alt + s
    End If
End If
If DelaySeconds < 1 Then DelaySeconds = 1
If DelaySeconds > 59 Then DelaySeconds = 59
TimeDelay = "00:00:" & Format(DelaySeconds, "0#")
EmailAddress = Application.WorksheetFunction.Trim(EmailAddress)
EmailURL = "Mailto:" & EmailAddress & "?subject=" & EmailSubject & "&body=" & ShortMessage

CheckSend = ShellExecute(0&, vbNullString, EMailURL, vbNullString, vbNullString, vbNormalFocus)  ' Send via Windows API function
If CheckSend >= 32 Then  ' <32 is an error
    Application.Wait (Now + TimeValue(TimeDelay))
    Application.SendKeys SendKeysCode
    SendShortEmail = True ' Successfully sent = TRUE
End If
Exit Function

EmailFailed:
SendShortEmail = False
End Function

The code has two parts.  At the top there is a declaration of the ShellExecute function. This code needs to go at the top of a VBA module.  The second part is the SendShortEmail function, which must be in the same VBA module as the declaration at the top.

You can put this code onto its own module sheet, name the sheet and export it as a file which just contains the code.  You can then import this same file into any Excel project (while in the Visual Basic Editor), as and when required.

This function is set up assuming that Lotus Notes is the most common email program rather than outlook.  If Outlook is the most common in your context, then swap the default values for LotusNotes and Outlook at the start of the function:

Optional LotusNotes As Boolean = False, Optional Outlook As Boolean = True

In this case I would also recommend swapping the order of the LotusNotes and Outlook checks in the If .. Then statement block:

 If LotusNotes Then
    SendKeysCode = "%1" ' Alt + 1
 ElseIf Outlook Then
    SendKeysCode = "^~" ' Ctrl + Enter
 Else
    SendKeysCode = "%s" ' Alt + s
 End If

How to Use the Function

Here are some examples of how to call the function.

Send an email to Lotus Notes

Here are three different methods of doing the same thing, illustrating when to use parentheses and when not to when calling a subroutine or function in VBA.

Method 1: Function with arguments. (No parentheses are required around the arguments as there is no other VBA statements on this line.)

SendShortEmail "myemail@mydomain.com", "My subject", "Hi, Here is my short message."

Method 2: Use the Call statement with arguments. (Parentheses are required as “Call” is also on this line.)

Call SendShortEmail("myemail@mydomain.com", "My subject", "Hi, Here is my short message.")

Method 3: Assign a Boolean (true/false) variable to the function. Use the returned value to check if it worked.

Result = SendShortEmail("myemail@mydomain.com", "My subject", "Hi, Here is my short message.")
If Result Then MsgBox "Email was sent successfully"

Send an email to Outlook

Here is how to send an email to Outlook with the above function. (I have checked this on Outlook 2007 and 2010 but not 2013.)

SendShortEmail "myemail@mydomain.com", "My subject", "Hi, Here is my short message.", Outlook:=True

Change the Shortcut Keys to Send the Email

Your email program may require a different set of shortcut keys to send the email.  Have a look at the following website to understand how to code your key combination and use this in the function call as in the example below. This SendKeysCode parameter overrides the LotusNotes and Outlook parameters when deciding what to use for the shortcut keys to send the email. For example, Control + s would be:

SendShortEmail "myemail@mydomain.com", "My subject", _
    "My short message.", SendKeysCode:="^s"

Change the Time Delay

If you find that 2 seconds is not long enough for the email program to get ready to send the email (as evidenced by the email just sitting there and not being sent), then you can change that parameter.  This can be combined with some of the other methods.

SendShortEmail "myemail@mydomain.com", "My subject", _
    "My short message.", DelaySeconds:=4

Further Information

I will explain parts of the function in detail in a subsequent post. You may like to subscribe to get notification when I do a post.

Leave a Reply

Your email address will not be published. Required fields are marked *