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?
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?
VBA Code:
FileName = ("FileName") & "xlsx"
VBA Code:
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