V
VILLABILLA
Hello! I would like to refer to Ron de Bruin's macro that should make i
possible to mail sheet or sheets to one or more people, described o
his I-net site; www.rondebruin.nl/sendmail.htm
I added a sheet "mail" to my workbook and added 6 columns(needed t
send out the two sheets that I would like to send) :
The macro looks like this:
Sub Mail_sheets()
Dim MyArr As Variant
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
Dim wb As Workbook
Dim cell As Range
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(2, a).Value = "" Then Exi
Sub
Application.ScreenUpdating = False
strdate = Format(Now, "dd-mm-yy h-mm-ss")
With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(2, a + 1), .Cells(Rows.Count, a
1).End(xlUp))
If Application.WorksheetFunction.CountIf(.Columns(a + 1)
"*@*") = 0 Then
MsgBox "There are no E-Mail addresses"
Application.ScreenUpdating = True
Exit Sub
End If
N = 0
For Each cell In .Range(.Cells(2, a), .Cells(Rows.Count
a)).Cells.SpecialCells(xlCellTypeConstants)
If SheetExists(cell.Value) = True Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = .Cells(cell.Row, a).Value
Else
MsgBox "There is a sheet that don't exist in th
list"
Application.ScreenUpdating = True
Exit Sub
End If
Next cell
End With
ThisWorkbook.Sheets(Arr).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(2, a
2).Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Next a
End Sub
When I try to run the macro it creates the workbook for the first shee
it wants to send out and then it gives me the following window:
Run-Time error '1004'
Microsoft Excel cannot acces the file 'C:\Program Files\Commo
Files\System\Mapi\1033\NT'. There are several possible reasons:
-The file name or path does not exist
-the file you try to open is being used by another program.
-The name of theworkbook you're trying to save is the same as the
Would someone know what I am doing wrong?
Thanks a lot in advance
possible to mail sheet or sheets to one or more people, described o
his I-net site; www.rondebruin.nl/sendmail.htm
I added a sheet "mail" to my workbook and added 6 columns(needed t
send out the two sheets that I would like to send) :
The macro looks like this:
Sub Mail_sheets()
Dim MyArr As Variant
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
Dim wb As Workbook
Dim cell As Range
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(2, a).Value = "" Then Exi
Sub
Application.ScreenUpdating = False
strdate = Format(Now, "dd-mm-yy h-mm-ss")
With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(2, a + 1), .Cells(Rows.Count, a
1).End(xlUp))
If Application.WorksheetFunction.CountIf(.Columns(a + 1)
"*@*") = 0 Then
MsgBox "There are no E-Mail addresses"
Application.ScreenUpdating = True
Exit Sub
End If
N = 0
For Each cell In .Range(.Cells(2, a), .Cells(Rows.Count
a)).Cells.SpecialCells(xlCellTypeConstants)
If SheetExists(cell.Value) = True Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = .Cells(cell.Row, a).Value
Else
MsgBox "There is a sheet that don't exist in th
list"
Application.ScreenUpdating = True
Exit Sub
End If
Next cell
End With
ThisWorkbook.Sheets(Arr).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(2, a
2).Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Next a
End Sub
When I try to run the macro it creates the workbook for the first shee
it wants to send out and then it gives me the following window:
Run-Time error '1004'
Microsoft Excel cannot acces the file 'C:\Program Files\Commo
Files\System\Mapi\1033\NT'. There are several possible reasons:
-The file name or path does not exist
-the file you try to open is being used by another program.
-The name of theworkbook you're trying to save is the same as the
Would someone know what I am doing wrong?
Thanks a lot in advance