I'm having a problem E-Mailing a spreadsheet via Code from Excel. The code
itself is fine, but the file is added as an attachment as a "dat" extension.
This has only just started having worked for a long number of months.

I have used VBA Code Cleaner but that has made no difference

I have checked to ensure that the References have been set to "Microsoft
Ooulook 10 library"

My PC is clean of any viruses (AFAIK)

I have using Excel / Outlook XP

Anyone ever have similar problem? Code to E-mail is below (thanks to Ron De
Bruin), but don't think thats the issue, because why would it suddenly cause
a problem?




Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Figures").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Figures", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("E-Figures").Range("AJ1:AJ3")
.SendMail MyArr, Sheets("E-Figures").Range("AJ4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True

Sheets("E-Figures").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("E-Import").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
End Sub

Hi John

Try this


Change this

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""

To

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"



--
Regards Ron de Bruin
http://www.rondebruin.nl



"John" wrote in message ...
I'm having a problem E-Mailing a spreadsheet via Code from Excel. The code itself is fine, but the file is added as an attachment
as a "dat" extension. This has only just started having worked for a long number of months.

I have used VBA Code Cleaner but that has made no difference

I have checked to ensure that the References have been set to "Microsoft Ooulook 10 library"

My PC is clean of any viruses (AFAIK)

I have using Excel / Outlook XP

Anyone ever have similar problem? Code to E-mail is below (thanks to Ron De Bruin), but don't think thats the issue, because why
would it suddenly cause a problem?




Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Figures").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Figures", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("E-Figures").Range("AJ1:AJ3")
.SendMail MyArr, Sheets("E-Figures").Range("AJ4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True

Sheets("E-Figures").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("E-Import").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
End Sub

Ron

Thanks for that, that worked. Not sure why all of a sudden it wouldn't work
without explicitly stating the xls extension

Another side point. The temp file that is created when sending via your
code - if you actually send the e-mail it is deleted, but if a user takes
the "No" option on the Microsoft security message, this temp file is saved
to your hard disk, I think in the last "active" directory the user is in.
That means sometimes files get saved all over my hard disk and a clean up
can be bothersome trying to find them. Is it possible you could specify a
directory where these temp files are always saved to? Thus if the user takes
the No security option, you can easily locate all those temp files?

Thanks Ron


"Ron de Bruin" wrote in message
...
Hi John

Try this


Change this

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""

To

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"



--
Regards Ron de Bruin
http://www.rondebruin.nl



"John" wrote in message
...
I'm having a problem E-Mailing a spreadsheet via Code from Excel. The
code itself is fine, but the file is added as an attachment as a "dat"
extension. This has only just started having worked for a long number of
months.

I have used VBA Code Cleaner but that has made no difference

I have checked to ensure that the References have been set to "Microsoft
Ooulook 10 library"

My PC is clean of any viruses (AFAIK)

I have using Excel / Outlook XP

Anyone ever have similar problem? Code to E-mail is below (thanks to Ron
De Bruin), but don't think thats the issue, because why would it suddenly
cause a problem?




Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Figures").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Figures", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("E-Figures").Range("AJ1:AJ3")
.SendMail MyArr, Sheets("E-Figures").Range("AJ4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True

Sheets("E-Figures").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("E-Import").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
End Sub

Use a on error resume next

From my site :

If you click No on the Outlook security pop up screen that ask you
if it is OK to send the mail use this to avoid the error.

Sub test()
On Error Resume Next
ActiveWorkbook.SendMail ", _
"This is the Subject line"
On Error GoTo 0
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl



"John" wrote in message ...
Ron

Thanks for that, that worked. Not sure why all of a sudden it wouldn't work without explicitly stating the xls extension

Another side point. The temp file that is created when sending via your code - if you actually send the e-mail it is deleted, but
if a user takes the "No" option on the Microsoft security message, this temp file is saved to your hard disk, I think in the last
"active" directory the user is in. That means sometimes files get saved all over my hard disk and a clean up can be bothersome
trying to find them. Is it possible you could specify a directory where these temp files are always saved to? Thus if the user
takes the No security option, you can easily locate all those temp files?

Thanks Ron


"Ron de Bruin" wrote in message ...
Hi John

Try this


Change this

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""

To

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"



--
Regards Ron de Bruin
http://www.rondebruin.nl



"John" wrote in message ...
I'm having a problem E-Mailing a spreadsheet via Code from Excel. The code itself is fine, but the file is added as an
attachment as a "dat" extension. This has only just started having worked for a long number of months.

I have used VBA Code Cleaner but that has made no difference

I have checked to ensure that the References have been set to "Microsoft Ooulook 10 library"

My PC is clean of any viruses (AFAIK)

I have using Excel / Outlook XP

Anyone ever have similar problem? Code to E-mail is below (thanks to Ron De Bruin), but don't think thats the issue, because why
would it suddenly cause a problem?




Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Figures").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Figures", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("E-Figures").Range("AJ1:AJ3")
.SendMail MyArr, Sheets("E-Figures").Range("AJ4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True

Sheets("E-Figures").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("E-Import").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
End Sub

Thanks Ron, that sorts that too


"Ron de Bruin" wrote in message
...
Use a on error resume next

From my site :

If you click No on the Outlook security pop up screen that ask you
if it is OK to send the mail use this to avoid the error.

Sub test()
On Error Resume Next
ActiveWorkbook.SendMail ", _
"This is the Subject line"
On Error GoTo 0
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl



"John" wrote in message
...
Ron

Thanks for that, that worked. Not sure why all of a sudden it wouldn't
work without explicitly stating the xls extension

Another side point. The temp file that is created when sending via your
code - if you actually send the e-mail it is deleted, but if a user takes
the "No" option on the Microsoft security message, this temp file is
saved to your hard disk, I think in the last "active" directory the user
is in. That means sometimes files get saved all over my hard disk and a
clean up can be bothersome trying to find them. Is it possible you could
specify a directory where these temp files are always saved to? Thus if
the user takes the No security option, you can easily locate all those
temp files?

Thanks Ron


"Ron de Bruin" wrote in message
...
Hi John

Try this


Change this

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""

To

.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"



--
Regards Ron de Bruin
http://www.rondebruin.nl



"John" wrote in message
...
I'm having a problem E-Mailing a spreadsheet via Code from Excel. The
code itself is fine, but the file is added as an attachment as a "dat"
extension. This has only just started having worked for a long number
of months.

I have used VBA Code Cleaner but that has made no difference

I have checked to ensure that the References have been set to
"Microsoft Ooulook 10 library"

My PC is clean of any viruses (AFAIK)

I have using Excel / Outlook XP

Anyone ever have similar problem? Code to E-mail is below (thanks to
Ron De Bruin), but don't think thats the issue, because why would it
suddenly cause a problem?




Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Figures").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Figures", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("E-Figures").Range("AJ1:AJ3")
.SendMail MyArr, Sheets("E-Figures").Range("AJ4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True

Sheets("E-Figures").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("E-Import").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
End Sub