Sending email routine, tweks needed

[HammerJoe]

Hi,

Take a look at this code:
============
Public Sub mail()
Dim wb As Workbook
On Error GoTo ErrorHandler
'Copy to new workbook to be emailed
Sheets("Week to Date").Select
Range("A1:C22").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


'Sheets("Week to Date").Copy
Set wb = ActiveWorkbook
With wb
'.SaveAs Sheets("Week to Date").Name & ".Xls"
.SaveAs wb.Name & "eTracker.Xls"
.SendMail "", wb.Name
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
ErrorHandler:

End Sub
========
The idea is to add a workbook with new sheet, copy the format and
values *only* to the new sheet and email it.

Two things with this code:
- It works, but only once. if the code is run a second time then on
the line .SaveAs wb.Name & "eTracker.Xls" it jumps to ErrorHandler.
Obviosuly there is a problem, dont know what it is and how to solve.
(Yes I checked and there is no file with same name in the folder!)
I need help make sure than it doesnt happen.

- Is there a way to confirm that the email was sent?
If not how to go around to get some sort of confirmation?
The reason is that after information is deleted and cannot be
recovered so need to make sure an email was sent.

- What this does it opens an email message window from Outlook, which
is fine, and adds the file as an attachment.
I was wondering if it is possible "paste" the sheet in the message
body?

Thanks for all the help.

 

[Ron de Bruin]

See this page for code that work correct in every Excel version
http://www.rondebruin.nl/sendmail.htm

 

[Trent Argante]

To debug your own code, try the following:
1) REM out your On Error Goto line, then execute it twice (i.e., until it
errors), and then read what the error message says. Let us know if it
doesn't help you.
2) eMail the workbook to yourself for verification.
HTH