Code for Sending Excel in Outlook to multiple people

Huizar

Board Regular
Joined
Aug 11, 2016
Messages
94
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You were right. sheet.Copy does not like hidden sheets
If you change the sh.copy to this it always works
Code:
            '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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top