Results 1 to 5 of 5

Thread: Mailing a Range or Selection in the Body of an E-Mail Message - Help

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    13

    Mailing a Range or Selection in the Body of an E-Mail Message - Help

    Hi,
    I have the below code to send a range using outlook. But while compiling it is giving error and highlighting .HTMLBody = RangetoHTML(rng)
    Please help.

    Thanks
    Rajesh



    Code:
    Sub Mail_Selection_Range_Outlook_Body()
    ' You need to use this module with the RangetoHTML subroutine.
    ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set rng = Nothing
        On Error Resume Next
        ' Only send the visible cells in the selection.
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
        ' You can also use a range with the following statement.
        ' Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
        If rng Is Nothing Then
            MsgBox "The selection is not a range or the sheet is protected. " & _
                   vbNewLine & "Please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
            ' In place of the following statement, you can use ".Display" to
            ' display the e-mail message.
            .Send  
        End With
        On Error GoTo 0
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    Last edited by Admin; 02-29-2012 at 07:38 AM. Reason: code tag added

  2. #2

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Rajesh,

    Thanks for sharing the solution.

    Please use code tags while posting codes
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Sep 2011
    Posts
    14
    Rep Power
    0
    Getting an error msg "RangetoHTML" sub or function not defined. Using Excel 2010

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    RangetoHTML is a Function. Get the code from the link Rajesh posted above.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 12
    Last Post: 12-10-2019, 09:56 PM
  2. Copy and Past as picture/bitmap in email body form excel.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 0
    Last Post: 12-05-2012, 09:56 PM
  3. Message Box Before Saving Document
    By Lucero in forum Excel Help
    Replies: 2
    Last Post: 04-15-2012, 07:09 AM
  4. Replies: 3
    Last Post: 04-08-2012, 08:05 AM
  5. Mail Range or Selection using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 1
    Last Post: 11-03-2011, 09:00 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •