Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 24th, 2008, 06:57 PM
Registered User
Join Date: Oct 2008
Location: , CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default send emails with an Excel table as text format


I need to create a macro that sends out emails through Outlook. Plese see my exmaple code below. Instead of sending an attachment, is it possible to copy the content of the excel spreadsheet and paste it directly to the email body.

many thanks in advance.


Sub Email()

    Dim myOutlook As Object
    Dim myMailItem As Object
    Dim FileAttach As String

    Application.DisplayAlerts = False

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(olMailItem)

    FileAttach = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

    With olMail
        .To = "peter@yahoo.com"
        .Subject = "Test"
        .Body = "test emails"
        .Attachments.Add FileAttach
    End With

End Sub
Reply With Quote
  #2 (permalink)  
Old October 24th, 2008, 10:04 PM
Registered User
Join Date: Aug 2008
Location: Nanaimo, BC, Canada.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts

Try the .sendmail method. If you use the workbook you want to send, it will go through outlook as an attachment. You can specify recipient(s) and subject. e.g.,

Excel Developer Reference
Workbook.SendMail Method

Sends the workbook by using the installed mail system.expression.SendMail(Recipients, Subject, ReturnReceipt)
expression A variable that represents a Workbook object.


Name Required/Optional
Data Type Description
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.


This example sends the active workbook to a single recipient.

Visual Basic for Applications
ActiveWorkbook.SendMail recipients:="Jean Selva"

Do you know it the sender has Outlook? If you don't know for sure, use the application.mailsystem to ensure it is xlMapi; e.g.,

Visual Basic for Applications
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

Reply With Quote
  #3 (permalink)  
Old October 27th, 2008, 11:34 AM
Registered User
Join Date: Oct 2008
Location: , CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts


Thanks for your reply. But it seemed to me that the .SendMail method can only apply to an object of Workbook. Therefore, the content I want to send is still sent as an attachment. I am looking for a method that can send the content of an Excel workbook directly in the body of the email in Text format.

Reply With Quote
  #4 (permalink)  
Old October 29th, 2008, 11:45 AM
JP JP is offline
Authorized User
Points: 199, Level: 3
Points: 199, Level: 3 Points: 199, Level: 3 Points: 199, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Apr 2008
Location: NYC, NY, USA.
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts

See http://www.rondebruin.nl/mail/folder3/mail4.htm

Oh and FYI, if you are late-binding Outlook, you need to use the number constants with CreateItem.

Reply With Quote
  #5 (permalink)  
Old October 29th, 2008, 04:04 PM
Registered User
Join Date: Oct 2008
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks a lot, JP.

This is great!

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel format changes from Number to Text ajindal General .NET 2 July 24th, 2007 06:56 AM
Code to send emails maitias C# 2005 2 February 1st, 2006 01:09 PM
send emails to several users femig VBScript 4 April 15th, 2005 07:42 AM
Send two emails instead of one ss2003 Beginning PHP 3 January 28th, 2005 11:54 AM
Send Emails and Attachments. mistry_bhavin General .NET 2 August 11th, 2004 10:05 AM

All times are GMT -4. The time now is 06:49 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.