Email


Workbook.EnvelopeVisible property you can insert various text fields for the input of e-mail addresses and subject lines in the upper border of the table window.


Sending Excel files as e-mail

ThisWorkbook.SendMail(Recipients, Subject, ReturnReceipt) 

Recipients Required Variant. Specifies the name of the recipient as text, or as an array of text strings if there are multiple recipients. At least one recipient must be specified, and all recipients are added as To recipients.
Subject Optional Variant. Specifies the subject of the message. If this argument is omitted, the document name is used.
ReturnReceipt Optional Variant. True to request a return receipt. False to not request a return receipt. The default value is False.


The following line of code will send the activeworkbook without any prompts or confirmations.

ActiveWorkbook.SendMail Recipients:="Jean Selva"
 

If you want to send an email to several recipients simultaneously you can pass an array of character strings in the Recipients parameter.
You do not need to save the workbook first before sending it.
SendMail can only be used when Application.MailSystem = xlMAPI



Determining the Type of Installed Mail System


Application.MailSystem 

Returns the mail system that's installed on the host machine. Read-only XlMailSystem.
xlMAPI

Select Case Application.MailSystem 
    Case xlMAPI
        MsgBox "Mail system is Microsoft Mail"
    Case xlPowerTalk
        MsgBox "Mail system is PowerTalk"
    Case xlNoMailSystem
        MsgBox "No mail system installed"
End Select

Sending Files Interactively

If you want the user to have an element of control over the email they are sending you have 2 options:

  • Display a separate dialog box.

Application.Dialogs(xlDialogSendMail).Show 

  • Use the built-in Envelope

ThisWorkbook.EnveloperVisible = Not ThisWorkbook.EnveloperVisible 



© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrev