Hello,

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.

Peter

-------------------------------
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 = "[email protected]"
        .Subject = "Test"
        .Body = "test emails"
        .Attachments.Add FileAttach
        .Send
    End With

End Sub
-------------------------

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.

Parameters

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.

Example:

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

Hi LBAKER,

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.

Peter

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.


HTH,
JP

Thanks a lot, JP.

This is great!

Peter