Using the Integrated Email Dialog in Excel VBA

If you want to always give the user the opportunity to add additional elements to an email (such as Cc, Bcc recipients, or a message body), you can invoke Excel’s built-in email dialog box:

Sub EmailDialog()
    Dim success As Boolean
    success = Application.Dialogs(xlDialogSendMail).Show("moyofranck@gmail.com", "Test")
    If success Then
        MsgBox "Email was saved"
    Else
        MsgBox "Email saving was canceled"
    End If
End Sub

Explanation:

• The Show() method of the Dialogs collection is called to display one of Excel’s many built-in dialog boxes (see Section 10.1, « Built-in Dialog Boxes »).
• Using the constant xlDialogSendMail, the email dialog box is opened.
• You can specify a single recipient or multiple recipients as an array.
• The automatically generated subject line is known to be incorrect and should be overwritten by the user.
• The active workbook is attached to the email.
• The user can add further elements to the email, such as additional recipients or a message body, within the dialog.
• The method returns a Boolean indicating whether the email was saved. However, note that the return value is sometimes True even if the user cancels the email operation in Outlook.
• If the user saves the email, it is placed in the Outlook Outbox.