Email attachement is not showing as Excel Doc

[meeking86]

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

 

[MrTomasz]

what value you have in this cell: FileName = Sheets("Quote Email").Range("A3").Text

try to change this line by adding & ".xlsx":

FileName = Sheets("Quote Email").Range("A3").Text & ".xlsx"

 

[daverunt]

Also FileName is used before and after the path. The last one being the value used.

 

[meeking86]

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]

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.

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]

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.

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]

Use Attachments.Add FPath & Filename as the wb.Fullname reference is lost when it was saved and closed.

 

[daverunt]

Duff info previously.
We can grab the .FullName before we close the file to use it later

 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