Code for Sending Excel in Outlook to multiple people

[Huizar]

Hey Gurus,

The other day I used this specific code and now its doesn't want to work. Any reason why? it is specifically stopping at where I have BIG TEXT. Why is this occurring? I need help ASAP. Need to send this file to multiple individuals.
Thanks.

Sub Mail_Every_Worksheet()
'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object

TempFilePath = Environ$("temp") & ""

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
FileExtStr = ".xlsm": FileFormatNum = 52
End If

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

Set OutApp = CreateObject("Outlook.Application")

For Each sh In ThisWorkbook.Worksheets
If sh.Range("A1").Value Like "?*@?*.?*" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

Set OutMail = OutApp.CreateItem(0)

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next
With OutMail
.to = sh.Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

.Close savechanges:=False
End With

Set OutMail = Nothing

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

Set OutApp = Nothing

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

 

[Huizar]

So I noticed when I send a blank book and put emails on cell A1 it works fine. but when i send the file with the data it stops from sending it.

Thanks.

 

[ask2tsp]

I tested the code and for me it works just fine.

Could you describe the error message you get when it stops on the sh.copy?

Can you describe what is different now about the sheet where the sh.copy fails? (in the Visual Basic Environment goto the immediate window [ctrl+G] and type ?sh.name[Enter] to see the name of the sheet)

 

[Huizar]

Hey ask2tsp,

I guess it's not an error message but when i try to run it ask me to debug. It highlights the specific area of the code "sh.Copy". When I went into the immediate window and I pressed enter it said December.

When I just copied the same workbook onto a new workbook the program ran just fine. Why didn't it work with the original file?

Thanks,

Huizar

 

[Huizar]

So based on what I'm seeing i forgot I had a hidden sheet in the file, but even if that was the case why would that stop it from working?

Thanks,

Huizar

 

[ask2tsp]

You were right. sheet.Copy does not like hidden sheets
If you change the sh.copy to this it always works

            'to also copy hidden sheets
            If sh.Visible <> xlSheetVisible Then
                origVis = sh.Visible
                sh.Visible = xlSheetVisible
                sh.Copy
                sh.Visible = origVis
            Else
                sh.Copy
            End If

origVis is a Long.