Excel

Email Workbook with Message (Uses xlDialogSendMail & Outlook Express)

Ease of Use

Easy

Version tested with

2000 

Submitted by:

johnske

Description:

After running the procedure the mail dialog will appear with the active workbook as an attachment, your recipients addresses completed, the subject completed, and a default body of text included. 

Discussion:

Some anti-virus programs won't allow an email to be sent without any message in the message field. If you're using xlDialogSendMail programmatically this may thus cause an error if a user inadvertantly sends an email without writing any message, you can use the following code to insert a default message to prevent that error arising. Note: I couldn't find any code anywhere on the Net that allows you to programmatically insert a message when using xlDialogSendMail - this solves that problem by using sendKeys. NOTE: Does not work properly with Outlook, but works fine with Outlook Express. 

Code:

instructions for use

			

Option Explicit ' Sub xlDialogSendMailWithMessage() 'Note: This sends the ActiveWorkBook as an attachment 'using the default email program. If the workbook to be 'sent is not the active workbook, then insert code to 'open and activate it before running this... Dim N As Long With Application 'scroll down to the message field For N = 1 To 5 .SendKeys "{TAB}", Wait:=True Next 'insert text (replace 'Email text here...' with your message) .SendKeys "Email text here...", Wait:=True 'replace aaa@bbb.org; xxx@yyyy.com; with your recipients 'replace 'Insert subject here...' with your subject .Dialogs(xlDialogSendMail).Show _ "aaa@bbb.org; xxx@yyyy.com;", _ "Insert subject here..." End With ' End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Now select File/Close and Return To Microsoft Excel
  6. Save the workbook
  7. Select Tools/Macro/Macros.../xlDialogSendMailWithMessage/Run
 

Test the code:

  1. You must be on-line to do this and have OE as your default mailer...
  2. Select Tools/Macro/Macros.../xlDialogSendMailWithMessage/Run
  3. When the dialog appears, just click 'Send'
 

Sample File:

SendMailWithMsg.zip 6.9KB 

Approved by mdmackillop


This entry has been viewed 331 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express