Hidden rows showing up in my email attachment

Hi Everyone:

Sorry if this is a really stupid question,but here goes:

I want to send a worksheet in the body of the mail,in the worksheet I have hidden cells:I unhide them but they still show up in the body of the email I send,Can some please show me how I unhide them in my macro? my code is pretty simple:


Sub Send_Range()

   ActiveWorkbook.EnvelopeVisible = True
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "E-Mail_Address_Here"
      .Item.Subject = "My subject"
      .Item.Send
   End With
End Sub


if it helps any: my hidden cells are from column F : column H,but i want the values in column I to be displayed.


if this is not doable my other (much disliked) option is have the the activesheet saved on the desktop and then the user sends it as an attachment.i really really dont want to do this

Sub MailSheet()
    Dim shtName As String
    shtName = ActiveSheet.Name
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs FileName:=Application.GetSaveAsFilename("Copy of " & shtName, "Microsoft Excel File, *.xls")
    Application.DisplayAlerts = False
    Application.Dialogs(xlDialogSendMail).Show
    With ActiveWorkbook
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With

    Application.DisplayAlerts = True
End Sub


so my request is either:

1)i am able to remove the hidden cells from the email body OR
2)send the activesheet as an attachment without saving on the users PC

Can someone please guide me?

Regards

Arun






kmgingeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Use this code. It creates a copy of the worksheet, converts all formulas to values, deletes the hidden columns, sends it, then deletes it.

Sub Send_Range()

    Dim SourceWorksheet As Worksheet
    Dim ColumnWidth As Double
    Dim Column As Long
    ActiveWorkbook.EnvelopeVisible = True
    ' Set the optional introduction field thats adds
    ' some header text to the email body. It also sets
    ' the To and Subject lines. Finally the message
    ' is sent.
    Application.DisplayAlerts = False
    ThisWorkbook.ActiveSheet.Copy After:=ThisWorkbook.ActiveSheet
    Set SourceWorksheet = ActiveSheet
    SourceWorksheet.UsedRange.Value = SourceWorksheet.UsedRange.Value
    With SourceWorksheet.UsedRange
        For Column = .Column To .Column + .Columns.Count - 1
             If .Columns(Column).Hidden Then
                 ColumnWidth = .Columns(Column).Offset(0, 1).ColumnWidth
                 .Columns(Column).Delete
                 .Columns(Column).ColumnWidth = ColumnWidth
             End If
        Next Column
    End With
    With SourceWorksheet.MailEnvelope
       .Introduction = "This is a sample worksheet."
       .Item.To = "E-Mail_Address_Here"
       .Item.Subject = "My subject"
       .Item.Send
    End With
    SourceWorksheet.Delete
    Application.DisplayAlerts = True
End Sub

Kevin
kmgingeeAuthor Commented:
thanks kevin,but i get this error when i run your code:

Run-time error '1004'

Application-defined or object-defined error


Thanks

Arun
zorvek (Kevin Jones)ConsultantCommented:
What line?

Kevin
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Learn More!
kmgingeeAuthor Commented:
I Get the error in this line:

Run-time error '1004'

Cannot enter a null value as an item or field name in a pivot table report


it is coming on this line of your code:

Application.DisplayAlerts = False


I have attached the file for your reference



2009-Vacation-Tracking-Schedule.xls
zorvek (Kevin Jones)ConsultantCommented:
Fixed:

Sub read()
   
    Dim SourceWorksheet As Worksheet
    Dim Column As Long
    Dim Calculation As Long
    Application.ScreenUpdating = False
    Calculation = Application.Calculation
    Application.Calculation = xlCalculationManual
    ActiveWorkbook.EnvelopeVisible = True
    ' Set the optional introduction field thats adds
    ' some header text to the email body. It also sets
    ' the To and Subject lines. Finally the message
    ' is sent.
    Application.DisplayAlerts = False
    ThisWorkbook.ActiveSheet.Copy After:=ThisWorkbook.ActiveSheet
    Set SourceWorksheet = ActiveSheet
    SourceWorksheet.UsedRange.Copy
    SourceWorksheet.UsedRange.PasteSpecial xlPasteValues
    With SourceWorksheet.UsedRange
        Column = .Column + .Columns.Count - 1
        Do
            If .Columns(Column).ColumnWidth = 0 Then
                .Columns(Column).ColumnWidth = .Columns(Column + 1).ColumnWidth
                .Columns(Column).Delete
            End If
            Column = Column - 1
        Loop While Column >= .Column
    End With
    With SourceWorksheet.MailEnvelope
       .Introduction = "This is a sample worksheet."
       .Item.To = "E-Mail_Address_Here"
       .Item.Subject = "My subject"
       .Item.Send
    End With
    SourceWorksheet.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = Calculation
   
End Sub

Kevin
kmgingeeAuthor Commented:
Thanks kevin,but unfortunately I still seem to be seeing the hidden cells showing up in the body of my mail:

I have attached a screenshot if it helps.

If this is not doable,i dont mind sending this activeworksheet as a excel attachment ( but it has to be automatic-no saving on the users PC)

any suggestions u might have?

untitled.JPG
zorvek (Kevin Jones)ConsultantCommented:
Are you sure you are running the latest code? I can't reproduce the problem. I did make a few tweaks.

Sub read()
   
    Dim Calculation As Long
    Dim SourceWorksheet As Worksheet
    Dim Column As Long
   
    Application.ScreenUpdating = False
    Calculation = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    ThisWorkbook.ActiveSheet.Copy After:=ThisWorkbook.ActiveSheet
    Set SourceWorksheet = ActiveSheet
    SourceWorksheet.UsedRange.Copy
    SourceWorksheet.UsedRange.PasteSpecial xlPasteValues
    With SourceWorksheet.UsedRange
        Column = .Column + .Columns.Count - 1
        Do
            If .Columns(Column).ColumnWidth = 0 Then
                .Columns(Column).ColumnWidth = .Columns(Column + 1).ColumnWidth
                .Columns(Column).Delete
            End If
            Column = Column - 1
        Loop While Column >= .Column
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = True
    DoEvents
    ActiveWorkbook.EnvelopeVisible = True
    With SourceWorksheet.MailEnvelope
       .Introduction = "This is a sample worksheet."
       .Item.to = "E-Mail_Address_Here"
       .Item.Subject = "My subject"
       .Item.Send
    End With
    SourceWorksheet.Delete
    Application.DisplayAlerts = True
    Application.Calculation = Calculation
   
End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kmgingeeAuthor Commented:
WOAH,it worked! - AWESOME!!
thank you soo very much!!!!
u the man :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.