ActiveWorkbook.SendMail not working

C

Craig

i'm trying to make a file copy a single sheet to a new workbook, save
the new workbook, then email the workbook. I make it decide whether or
not to just save or save and email based on a flag mySave The save
works without problems, but when i try to save and email, i get a run
time error 1004, method 'SendMail' of object'_Workbook' failed.

here's what i have so far

If mySave = 2 Then ActiveWorkbook.SendMail myRecipients, mySubject

right now i have myRecipients = [main!b2:b3].value and mySubject =
"Subject" i've tried changing the myRecipients to just a single email
address, even changed the sendmail line to refer directly to a single
email address and subject without referencing a variable, but with
everthing i've tried, the result has always been the error 1004.
here's the whole sub, any ideas?

Sub CreateFile()
Call TurnOff
Dim myName As String
Call ParseName
Sheets(myData).Select
myName = [g1].Value
Sheets(mySheet).Select
Range("A1:K55").Select
Range("K55").Activate
Selection.Copy
Workbooks.Open Filename:="C:\SCR\Templates\VersaCold.xlt",
Editable:=True
Call TurnOff
Range("A1:K55").Select
Range("K55").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=False, _
Transpose:=False
Range("H15").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\SCR\" & myFolder & "\" & myName
& ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
If mySave = 2 Then ActiveWorkbook.SendMail myRecipients, mySubject
MsgBox "File Saved"
ActiveWorkbook.Close
Windows("scr workstation.xls").Activate
Sheets("main").Activate
Call TurnOn
End Sub
 
P

Paul B

Craig, have a look at Ron de Bruin's site here
http://www.rondebruin.nl/sendmail.htm for LOTS of information on send mail

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top