0

I am trying to copy an entire sheet into an email body and the sheet is already filtered and hides rows. I want to copy only the visible rows into the email. I thought my code would do that but when the people reply to the emails, the entire sheet (both hidden and unhidden) appears in the email. Any ideas?

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
'Working in Excel 2002-2013
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Application.DisplayAlerts = False
    End With

    'Fill in the Worksheet/range you want to mail
    'Note: if you use one cell it will send the whole worksheet
    Set Sendrng = Worksheets("Test").Range("A1").SpecialCells(xlCellTypeVisible)

    'Remember the activesheet
    Set AWorksheet = ActiveSheet

    With Sendrng

        ' Select the worksheet with the range you want to send
        .Parent.Select

        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell

        'Select the range you want to mail
        .Select

        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "Test"

            With .Item
                .To = "test@email.com"
                .CC = ""
                .BCC = ""
                .Subject = "Test"
                .Send
            End With

        End With

        'select the original ActiveCell
        rng.Select
    End With

This was essentially taken from this Example 2 of Ron de Bruin, with some code from another example.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
user3009860
  • 41
  • 1
  • 4
  • 10

2 Answers2

0

The code below seems to work. You will have to fill it in with Ranges selection/activation and other details as needed.

EDIT The final step is sending the email (as per an added request of the OP). DoEvents added thanks to an answer to Excel VBA: Sent Outlook email does not include pasted Range

Sub SendEmail()

    Dim OutlookApp As Object
    'Dim OutlookApp As Outlook.Application
    Dim MItem As Object
    'Dim MItem As Outlook.MailItem

    'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
    'Set OutlookApp = New Outlook.Application

    Dim Sendrng As Range
    Set Sendrng = Worksheets("Test").Range("A1").SpecialCells(xlCellTypeVisible)
    Sendrng.Copy

    'Create Mail Item
    Set MItem = OutlookApp.CreateItem(0)
    'Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
        .To = "test@email.com"
        .Subject = "Test"
        .CC = ""
        .BCC = ""
        '.Body = "a"
        .Display
    End With
    SendKeys "^({v})", True
    DoEvents
    With MItem
        .Send
    End With

    Set OutlookApp = Nothing
    Set MItem = Nothing

End Sub
Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • This is great and appears to solve my problem! However, the email does not send automatically as part of the code? Is there a way to add that? – user3009860 Jan 06 '14 at 18:47
  • Actually, I figured it out. Change .Display to .Send. However, it is not pasting the selection in the body. It was hit or miss, but now its not pasting anything in... – user3009860 Jan 06 '14 at 20:50
  • @user3009860 - The change you propose is not the way to do it. Please check updated code, and give feedback on what you find. – sancho.s ReinstateMonicaCellio Jan 07 '14 at 02:22
-1

Since you did not state it is mandatory to use VBA (at least when this answer was first posted), you might:

Go to Home -> Find & Select -> Go To Special -> Visible cells only. Then copy and paste into your email. That worked for me.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185