Problem with E-mailing from Excel

[John]

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 de Bruin]

Hi John

Try this


Change this

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

To

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

 

[John]

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]

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 "(e-mail address removed)", _
"This is the Subject line"
On Error GoTo 0
End Sub

 

[John]

Thanks Ron, that sorts that too

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 "(e-mail address removed)", _
"This is the Subject line"
On Error GoTo 0
End Sub