File Name for E-mailed workbook

Untested BUT........

Dim myFile As String, s As String
myFile = Range("B5").Value
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.Name = myFile
ThisWorkbook.Activate
If Not IsNull(Application.MailSession) Then Application.MailLogon
With ThisWorkbook.Worksheets("Sheet6").Cells
For Each c In .Range("D2:D12").Cells
s = c.Value
If s = "" Then GoTo Skip
Workbooks(myFile).SendMail Recipients:=s, _
Subject:="Here is a NEW Problem Report", _
ReturnReceipt:=False
Skip:
Next c
End With
Workbooks(myFile).Close savechanges:=False

NB: Put in error handler for Filename routine
Just in case (JIC)

HTH

Ivan

I am trying to get it to use a value from a cell in the workbook for the file name.

I have tried putting

myFile = Range("B5").Value

in at various points without sucess. The value in B5 is numeric.

Any suggestions??

I got an error on line
ActiveWorkbook.Name = myFile
which highlighted .Name and said
"can't assign to read only property"

Lewis I am trying to get it to use a value from a cell in the workbook for the file name. I have tried putting myFile = Range("B5").Value in at various points without sucess. The value in B5 is numeric. Any suggestions?? : Lewis

Try this;

Sub test()
Dim myFile As String, s As String
Dim c

myFile = Range("B5").Value
ThisWorkbook.ActiveSheet.Copy
'Need to assign name via saveas
ActiveWorkbook.SaveAs "C:\Windows\temp\" & myFile
ThisWorkbook.Activate
If Not IsNull(Application.MailSession) Then Application.MailLogon
With ThisWorkbook.Worksheets("Sheet6").Cells
For Each c In .Range("D2:D12").Cells
s = c.Value
If s = "" Then GoTo Skip
Workbooks(myFile & ".xls").SendMail Recipients:=s, _
Subject:="Here is a NEW Problem Report", _
ReturnReceipt:=False

Skip:
Next c
End With

Workbooks(myFile & ".xls").Close False
'Now cleanup
Kill "C:\Windows\temp\" & myFile & ".xls"
End Sub

Ivan

Works fine (NT)

Sub test() Dim myFile As String, s As String Dim c myFile = Range("B5").Value 'Need to assign name via saveas ActiveWorkbook.SaveAs "C:\Windows\temp\" & myFile With ThisWorkbook.Worksheets("Sheet6").Cells For Each c In .Range("D2:D12").Cells s = c.Value If s = "" Then GoTo Skip Workbooks(myFile & ".xls").SendMail Recipients:=s, _ Subject:="Here is a NEW Problem Report", _ ReturnReceipt:=False Skip: Next c Workbooks(myFile & ".xls").Close False 'Now cleanup Kill "C:\Windows\temp\" & myFile & ".xls" End Sub

Ivan