VBA Excel mandare mail da excel

If you use Microsoft Outlook, it's fairly easy to create a macro to send personalized email from Excel. Just create an Outlook object and manipulate Outlook's object model using data from your worksheet.

But what if you don't use Outlook? This tip presents a technique for sending personalized email from Excel. I developed and tested the code using Outlook Express (which does not support VBA). It may or may not work with other email clients.

An Example

Consider the simple worksheet shown in the figure below. The goal is to send a personalized email to each of the three people listed in the workbook, informing them of their annual bonus amount.

For example, the first email message would read:

Dear John Jones,

I am pleased to inform you that your annual bonus is $2,000.

William Rose
President

The VBA Code

The VBA code to create and send these email messages is listed 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 SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 4 'data in rows 2-4
'       Get the email address
        Email = Cells(r, 2)
        
'       Message subject
        Subj = "Your Annual Bonus"

'       Compose the message
        Msg = ""
        Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that your annual bonus is "

        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "William Rose" & vbCrLf
        Msg = Msg & "President"
        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")        

'       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg        

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
    Next r
End Sub

How it Works

The first part of the macro is straightforward. It loops through the rows, picks up the email address and composes the custom message. Notice the use of Excel's Substitute worksheet function. This is used to replace space characters and carriage return/line feed sequences with their hex code equivalent (the standard characters are not allowed in a URL). A URL is created, and stored in the variable named URL. It then uses the ShellExecute API function to execute associated application.

At this point, the compose window for Outlook Express is displayed and ready to be sent. It uses the SendKeys message to send an Alt+S command (the "send" command for Outlook Express). Notice that there is a two second delay before the keystroke is sent. This can be adjusted as needed (one second may work fine).

I only use SendKeys as a last resort -- and this qualifies. Because of the pause, this macro is not very fast.

Note: The size of the email message is limited to (I think) 255 characters.

Note: My first approach was to use the FollowHyperlink method of the Workbook object. However, I was not able to figure out how to insert blank lines in the email body. Ron de Bruin solved that problem (see below).

Update

"Bugs" suggested a way to insert a block of static text into the message, thereby negating the 255-character limit. The solution? Copy your text to the Clipboard. Then add the following statement before the Application.SendKeys statement:

 Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v"

This statement tabs through the email window headers, jumps to the end of the message text, inserts two carriage returns, and pastes the contents of the clipboard. You may need to experiment with the number of Tabs sent.

For more information on sending email from Excel, visit Ron de Bruin's site.