View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Craig[_24_] Craig[_24_] is offline
external usenet poster
 
Posts: 39
Default ActiveWorkbook.SendMail not working

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