|
|
|
|
|
|
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()
Dim N As Long
With Application
For N = 1 To 5
.SendKeys "{TAB}", Wait:=True
Next
.SendKeys "Email text here...", Wait:=True
.Dialogs(xlDialogSendMail).Show _
"aaa@bbb.org; xxx@yyyy.com;", _
"Insert subject here..."
End With
End Sub
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Insert/Module
- Copy and paste the code into the Module
- Now select File/Close and Return To Microsoft Excel
- Save the workbook
- Select Tools/Macro/Macros.../xlDialogSendMailWithMessage/Run
|
Test the code:
|
- You must be on-line to do this and have OE as your default mailer...
- Select Tools/Macro/Macros.../xlDialogSendMailWithMessage/Run
- When the dialog appears, just click 'Send'
|
Sample File:
|
SendMailWithMsg.zip 6.9KB
|
Approved by mdmackillop
|
This entry has been viewed 331 times.
|
|