I do something wrong with SendMail macro...Please help

[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

 

[VILLABILLA]

I found what I was doing wrong; I had to change the default file settin
in Excel, because it was trying to save the created workbook on
location that I don't have access to...

The thread can be ignored....

Happy Easter

 

[Ron de Bruin]

Hi VILLABILLA

Check out your Win.ini file

http://www.rondebruin.nl/sendmail.htm#Problems
See number 2

 

[VILLABILLA]

The error described in my thread is still occuring. I checked out th
Win.ini file like Ron advised and it's on it's place just lik
Microsoft's site describes.

I thought I resolved the issue after I changed the default fil
location, I did this under Tools-Options-General tab.
It worked fine after I set my Desktop as the default file location, bu
when I close and reopen the file the same error appears again when
try to use the macro. When I check the default file location I see tha
it still is the path that leads to my desktop like I specified...

I really can't find anywhere what it is that I'm doing wrong...

When I empty the default file location field and replace it again wit
the same path, the macro works fine until I save, close and reopen th
file, then it gives the same error again...

When I press Debug, it shows this part of the macro in yellow:

.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

So it creates the workbook to send out but it doesn't manage to save i
I guess...

Please help

 

[Ron de Bruin]

Hi VILLABILLA

Send me your test workbook and i take a look at it

 

[VILLABILLA]

Hi Ron!

Attached you can find the workbook that i tried to use SendMail with.

I very much appreciate your help!

Regards

 

[VILLABILLA]

Once again..

 

[VILLABILLA]

It doesn't work to attach the file can I send it to your e-mai
@kabelfoon.nl

 

[Ron de Bruin]

It doesn't work to attach the file can I send it to your e-mail

@kabelfoon.nl?

You must send it to that address

Don't (never) attach a file in the newsgroup