Send Email Without An Attachment

    Hi all


    I'm new here, so apologise if this has already been asked/answered before.


    I use Office 200.


    I would like to send an email message to a supervisor when a user
    completes an order and pushes a button. The email needs to include
    some data relating to their order, all found on one row.


    The recipient is selected by the user from a dropdown list,
    and this will always be on the row of the last entry.


    I need to extract some data from the same row, and add the data to
    the body of the message.


    eg Item, Date Ordered, Lab or Plant, ETA, etc. I can do this part.
    (The subject will always be the same, eg "You Have A New Order".)


    At present when I use:

    Code
    ActiveWorkbook.SendMail Recipients:=Person, Subject:=Subject


    it sends the active workbook as an attachment.
    (I used this previously, and it worked fine.)


    Does anyone know how to send just an email with recipient,
    subject AND body text, but WITHOUT an attachment?


    Thanks in advance


    Stephen B

    Stephen,


    Below is a generic solution (i e not depended on the e-mailclient in use):


    [vba]
    Option Explicit


    Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long


    Sub Send_Email()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaData As Variant
    Dim stMsg As String, URL As String
    Dim i As Long


    'The e-mailaddress to the recipient.
    Const stRec As String = "[email protected]"
    Const stSubject As String = "You have a new order!"
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet1")

    'Read the rangedata into an array.
    With wsSheet
    vaData = Application.Transpose(.Range("B2:H2").Value)
    End With


    'Create the message to be placed into the body of the e-mail.
    For i = 1 To UBound(vaData)
    stMsg = stMsg & vaData(i, 1) & " "
    Next


    'Create the string.
    URL = "mailto:" & stRec & "?subject=" & stSubject & "&body=" & stMsg


    'Create the e-mail and show it.
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus


    End Sub
    [/vba]


    If You use MS Outlook or MS Outlook Express then take a look on Ron's nice utility for sending e-mail from Excel:
    http://www.rondebruin.nl/mail/add-in.htm

    Hi Dennis
    Thanks for this. I have modifed it somewhat to suit my needs, but I think I can follow it ok. There's always lots of syntax that I'm not familiar with, but each question and answer adds a little more.
    Cheers
    Stephen

    Re: Send Email Without An Attachment


    Hi Guys,


    I have tried the above code but get a Run Time error "9", subscript out of range. Any Ideas?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login