|
|
|
|
|
|
|
|
|
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:
|
- 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 246 times.
|
|
|