Send Email with Outlook

meeking86

New Member
Joined
Mar 11, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

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:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
683
Office Version
  1. 365
Platform
  1. Windows
You are naming your file "C:/" and file explorer does not allow files to have special characters
 

meeking86

New Member
Joined
Mar 11, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
For some reason i changed it to this and it still doesn't work :( oddly exact same code exept file and sheet names works fine in another macro, any ideas?

wb.SaveAs FileName:=FPath & FileName
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
683
Office Version
  1. 365
Platform
  1. Windows
FPath still has special character, you aren't choosing the location of your folder, the FileName:= part is only for the file name. You cant put special characters in your file name.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,728
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top