VBA Help with Email File errors

[NeoSez]

I found this code on this forum, but I can not get it to work properly. It keeps stopping and giving me errors.
I want to email only a certain range and not the entire worksheet from that multi-sheet workbook.
The new attachment filename should be the sheet name, but it currently names it after the Entire Workbook.
Can someone help? I've been looking at this all week and it's driving me mad
Thanks for your help.

Gives error here---> What am I supposed to have here so it doesn't keep giving error?

FileName = ("FileName") & "xlsx"

Sub EmailandSaveCellValue()

'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, MailTxt As String

'************************************************* ********
'Set email details; Comment out if not required
'Const MailTo = "someone@someone.com"
'Const MailCC = ""
'Const MailBCC = ""
'MailSub = "My Subject Line "
'MailTxt = "Body of Email"
'************************************************* ********

'Turns off screen updating
Application.ScreenUpdating = False

'Makes a copy of the active sheet and save it to
'a temporary file
ActiveWorkbook.Copy
ActiveSheet.Range ("A1:K100").copy
Set WB = ActiveWorkbook
FileName = ("FileName") & "xlsx"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = someone@someone.com
'.Cc = MailCC
'.Bcc = MailBCC
.Subject = "My Subject Line "
.Body = "Insert text"
.Attachments.Add WB.FullName
.Display
End With

'Deletes the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restores screen updating and release Outlook
Application.ScreenUpdating = False
Set oMail = Nothing
Set oApp = Nothing
End Sub

 

[rollis13]

Have you tried with".xlsx".

 

[yky]

Try this. I changed the target destination to C:\temp. I don't use Outlook. So, can't help on that part and parts after that. Specifically, ".Display" doesn't work for me, probably because I don't have Outlook installed or it's not running or something.

Sub EmailandSaveCellValue_new()

    'Variable declaration
    Dim oApp As Object
    Dim oMail As Object
    Dim wb As Workbook, ws As Worksheet
    Dim FileName As String, MailSub As String, MailTxt As String

    '************************************************* ********
    'Set email details; Comment out if not required
    'Const MailTo = "someone@someone.com"
    'Const MailCC = ""
    'Const MailBCC = ""
    'MailSub = "My Subject Line "
    'MailTxt = "Body of Email"
    '************************************************* ********

    'Turns off screen updating
    Application.ScreenUpdating = False

    Set ws = ActiveSheet
    'add a new workbook
    Set wb = Workbooks.Add
    'copy range to the new workbook
    ws.Range("A1:K100").Copy Destination:=wb.Sheets(1).Range("A1")
    'set file name to sheet name
    FileName = ws.Name & ".xlsx"
    On Error Resume Next
    Kill "C:\temp\" & FileName
    On Error GoTo 0
    wb.SaveAs FileName:="C:\temp\" & FileName

    'Creates and shows the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        .To = "someone@someone.com"
        '.Cc = MailCC
        '.Bcc = MailBCC
        .Subject = "My Subject Line "
        .Body = "Insert text"
        '.Attachments.Add wb.FullName
        .Attachments "C:\temp\" & FileName
        .Display
    End With

    'Deletes the temporary file
    wb.ChangeFileAccess Mode:=xlReadOnly
    Kill wb.FullName
    wb.Close savechanges:=False

    'Restores screen updating and release Outlook
    Application.ScreenUpdating = False
    Set oMail = Nothing
    Set oApp = Nothing
End Sub