Email attachement is not showing as Excel Doc

meeking86

New Member
Joined
Mar 11, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi

I have the following Code to send an email attachment of a specific sheet however is seems that it is not attaching as an excel document and in the Draft email it shows a doc without a .xls or whatever.

Code as follows

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

Sheets("Quote Email").Select
Columns("P:S").Select
Selection.EntireColumn.Hidden = True
Sheets("Quote Email").Copy
Set wb = ActiveWorkbook

FileName = wb.Worksheets(1).Name
FPath = "C:\Users\xxxxx\xxx Corporation\xx xxx - Documents\xxx PM\xxx Billing\Test\"
FileName = Sheets("Quote Email").Range("A3").Text
On Error Resume Next
Kill FPath & FileName
On Error GoTo 0
wb.SaveAs FileName:=FPath & FileName

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "someone@xxx.co.uk"
.CC = "someone@somewher.com"
'Uncomment the line below to hard code a subject
.Subject = Sheets("Quote Email").Range("A3").Text
'Uncomment the lines below to hard code a body
.Body = "All" & vbCrLf & vbCrLf & _
"PSA"
.Attachments.Add wb.FullName
.Display
End With

wb.ChangeFileAccess Mode:=xlReadOnly
Kill wb.FullName
wb.Close savechanges:=False
Selection.EntireColumn.Hidden = False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
Sheets("Home").Select
End Sub


Hope someone can help
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MrTomasz

Board Regular
Joined
May 16, 2014
Messages
84
Office Version
  1. 365
Platform
  1. Windows
what value you have in this cell: FileName = Sheets("Quote Email").Range("A3").Text

try to change this line by adding & ".xlsx":
VBA Code:
FileName = Sheets("Quote Email").Range("A3").Text & ".xlsx"
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Also FileName is used before and after the path. The last one being the value used.
 

meeking86

New Member
Joined
Mar 11, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Also FileName is used before and after the path. The last one being the value used.
Can i Clarify what you mean by this please?

Are you Saying it should be as follows, as i tried this and it failed

FileName = Sheets("Quote Email").Range("A3").Name
FPath = "C:\Users\xxxxx\xxx Corporation\xx xxx - Documents\xxx PM\xxx Billing\Test\"
FileName = Sheets("Quote Email").Range("A3").Text & ".xlsx"
On Error Resume Next
Kill FPath & FileName
On Error GoTo 0
wb.SaveAs FileName:=FPath & FileName
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I'm saying you have specified the variable FileName twice with different values.
If you specify a FileFormat:=xx when saving you don't need to use code to add the file extension - as in Filename & ".xlsx"
So go back to using the first FileName taken from the Worksheets(1)Name - 'Quote Email'
50-.xlsb
51-.xlsx
52-.xlsm
56-.xls

If you turn off DisplayAlerts it removes any prompts you'd get when saving it. (Turn it back on after use).
Save Changes=True on workbook close will overwrite a file - again without prompt - of the same name in the specified directory so
Kill isn't required to remove the previous.

Code:
Filename = wb.Worksheets(1).Name
FPath = "C:\Users\xxxxx\xxx Corporation\xx xxx - Documents\xxx PM\xxx Billing\Test\"

Application.DisplayAlerts = False

wb.SaveAs FPath & Filename, FileFormat:=51
wb.Close SaveChanges:=True
Application.DisplayAlerts = True
 

meeking86

New Member
Joined
Mar 11, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
I'm saying you have specified the variable FileName twice with different values.
If you specify a FileFormat:=xx when saving you don't need to use code to add the file extension - as in Filename & ".xlsx"
So go back to using the first FileName taken from the Worksheets(1)Name - 'Quote Email'
50-.xlsb
51-.xlsx
52-.xlsm
56-.xls

If you turn off DisplayAlerts it removes any prompts you'd get when saving it. (Turn it back on after use).
Save Changes=True on workbook close will overwrite a file - again without prompt - of the same name in the specified directory so
Kill isn't required to remove the previous.

Code:
Filename = wb.Worksheets(1).Name
FPath = "C:\Users\xxxxx\xxx Corporation\xx xxx - Documents\xxx PM\xxx Billing\Test\"

Application.DisplayAlerts = False

wb.SaveAs FPath & Filename, FileFormat:=51
wb.Close SaveChanges:=True
Application.DisplayAlerts = True
Ok

Thanks this certainly helped, however now i am finding that i am getting an automation Error further down where is has

.Attachements.Add wb.Fullname

not sure why as this worked before no problem.
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Use Attachments.Add FPath & Filename as the wb.Fullname reference is lost when it was saved and closed.
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
Duff info previously.
We can grab the .FullName before we close the file to use it later

Code:
 wb.SaveAs Fpath & FileName, FileFormat:=51
 wbs = wb.FullName
 wb.Close savechanges:=True

Attachment.Add wbs

When it comes to killing it later
Kill wbs
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,717
Members
415,922
Latest member
gemmatay88

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