Hi All
Please can you help me identify why the below code is failing, shown in bold where it errors, it may be beacause i am trying to save as a temp file but with a specific name and then trying to delete temp file further down in the code. Thanks in advance for your assistance
Please can you help me identify why the below code is failing, shown in bold where it errors, it may be beacause i am trying to save as a temp file but with a specific name and then trying to delete temp file further down in the code. Thanks in advance for your assistance
Rich (BB code):
Sub EmailWithOutlook1()
Dim oApp As Object
Dim oMail As Object
Dim wb As Workbook
Dim FileName As String
Dim wSht As Worksheet
Dim shtName As String
Application.ScreenUpdating = False
' Make a copy of the active worksheet
' and save it to a temporary file
Sheets("Quote Email").Copy
Set wb = ActiveWorkbook
FileName = wb.Worksheets(1).Name
FPath = "C:\Windows\Temp\"
FileName = Sheets("Quote Email").Range("B3").Text
On Error Resume Next
Kill FPath & FileName
On Error GoTo 0
wb.SaveAs FileName:="C:\" & FileName
'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "Donald.Duck@Disney.com"
.CC = "Somebody@email.com"
'Uncomment the line below to hard code a subject
.Subject = Sheets("Quote Email").Range("B3").Text
'Uncomment the lines below to hard code a body
.Body = "All" & vbCrLf & vbCrLf & _
"PSA"
.Attachments.Add wb.FullName
.Display
End With
'Delete the temporary file
wb.ChangeFileAccess Mode:=xlReadOnly
Kill wb.FullName
wb.Close savechanges:=False
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
Last edited by a moderator: