email entire spreadsheet (unhide rows)

[bigproblem]

is there a way to unhide my rows when the spreadsheet is sent to email? this
is the macro that i'm using. the macro works and an email is generated but i
want to send the entire spreadsheet (unhide my rows) as an attachement.

thanks

Private Sub CommandButton1_Click()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
..To = ""
..CC = ""
..BCC = ""
..Subject = "Revenue Enhancement Escalation"
..Body = ""
..Attachments.Add ActiveWorkbook.FullName
..display
End With
Set OutMail = Nothing
Set OutApp = Nothing


End Sub

 

[Ron de Bruin]

Hi bigproblem

Are the rows hidden or do you have a filter on ??

 

[bigproblem]

i have them hidden when the checkbox is selected. is there a way to unhide
when the spreadsheet is converted into an attached?

(just in case) macro used for checkbox...
Private Sub CheckBox2_Click()
Dim myRng As Range

Set myRng = Me.Range("c12:c12")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

 

[Ron de Bruin]

You can add this for the activesheet in the code before you create the mail

With ActiveSheet
.UsedRange.Columns(1).EntireRow.Hidden = False
End With


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

i have them hidden when the checkbox is selected. is there a way to unhide
when the spreadsheet is converted into an attached?

(just in case) macro used for checkbox...
Private Sub CheckBox2_Click()
Dim myRng As Range

Set myRng = Me.Range("c12:c12")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

 

[bigproblem]

I've tried to insert the text into the macro. Maybe i'm doing something
wrong or putting it in the wrong place because i can't get it to work.

Where should i put your text ?

Private Sub CommandButton1_Click()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
..To = ""
..CC = ""
..BCC = ""
..Subject = "Revenue Enhancement Escalation"
..Body = ""
..Attachments.Add ActiveWorkbook.FullName
..display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With ActiveSheet
..UsedRange.Columns(1).EntireRow.Hidden = False
End With


End Sub

 

[Ron de Bruin]

Add the code Before

With Outmail



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I've tried to insert the text into the macro. Maybe i'm doing something
wrong or putting it in the wrong place because i can't get it to work.

Where should i put your text ?

Private Sub CommandButton1_Click()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Revenue Enhancement Escalation"
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With ActiveSheet
.UsedRange.Columns(1).EntireRow.Hidden = False
End With


End Sub

 

[bigproblem]

After i put the code in, check my checkboxs, hit save and then hit my command
button, it unhides what is on my desktop not what is sent to the email.

Private Sub CommandButton1_Click()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With ActiveSheet
.UsedRange.Columns(1).EntireRow.Hidden = False
End With
With OutMail
..To = ""
..CC = ""
..BCC = ""
..Subject = "Revenue Enhancement Escalation"
..Body = ""
..Attachments.Add ActiveWorkbook.FullName
..display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With ActiveSheet
..UsedRange.Columns(1).EntireRow.Hidden = False
End With


End Sub

 

[Ron de Bruin]

Sorry my mistake
It will send the last saved version your code

Try this

Sub Mail_workbook_Outlook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

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

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2.Sheets(1)
.UsedRange.Columns(1).EntireRow.Hidden = False
End With
wb2.Save

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Revenue Enhancement Escalation"
.Body = "Hi there"
.Attachments.Add wb2.FullName
.Display 'or use .Send
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

After i put the code in, check my checkboxs, hit save and then hit my command
button, it unhides what is on my desktop not what is sent to the email.

Private Sub CommandButton1_Click()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With ActiveSheet
.UsedRange.Columns(1).EntireRow.Hidden = False
End With
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Revenue Enhancement Escalation"
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With ActiveSheet
.UsedRange.Columns(1).EntireRow.Hidden = False
End With


End Sub

 

[bigproblem]

unbelieveable...you are a genius !!!
--
problem

Sorry my mistake
It will send the last saved version your code

Try this

Sub Mail_workbook_Outlook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

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

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2.Sheets(1)
.UsedRange.Columns(1).EntireRow.Hidden = False
End With
wb2.Save

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Revenue Enhancement Escalation"
.Body = "Hi there"
.Attachments.Add wb2.FullName
.Display 'or use .Send
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Federico Giuliani]

Hi i'm using the following sub to create a email with attachments, and its works just fine.

But when i use outlook mine signature appears by itslef, but when i create the mail from excel the signature doesn't appears.

i tried to use .Signature = "name of signature"

but didn't work.

anybody knows how this works?

Thanks!

Sub Enviar_Informe()

Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(olMailItem)
'Set OutMail = OutApp.CreateItem(0)

Application.DisplayAlerts = False

ChDir "C:\temp\"
ActiveWorkbook.SaveAs Filename:= _
"C:\temp\Libro.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

body_str = "Adjunto informe de Producción." & vbNewLine & _
"" & vbNewLine & _
"Saludos" & vbNewLine & _
"" & vbNewLine & _
"Federico"

On Error Resume Next
With OutMail
..To = "produccion"
..CC = ""
..BCC = ""
..Subject = "Informe Diario de Producción"
..Body = body_str
'.Signature = "sin título"
..Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
..Display
End With

ActiveWindow.Close

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile "C:\temp\Libro.xls", force

Application.DisplayAlerts = True

End Sub

 

[Ron de Bruin]

Hi Federico

See
http://www.rondebruin.nl/mail/folder3/signature.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


in message news:[email protected]...