Problem with E-mailing from Excel

John · 07-13-2006, 12:00 PM

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 · 07-13-2006, 12:10 PM

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" <johnddd@yahoo.commie> wrote in message news:n2utg.11415$j7.315708@news.indigo.ie...
> 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
>

John · 07-13-2006, 12:30 PM

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" <rondebruin@kabelfoon.nl> wrote in message
news:uppBnYppGHA.4216@TK2MSFTNGP04.phx.gbl...
> 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" <johnddd@yahoo.commie> wrote in message
> news:n2utg.11415$j7.315708@news.indigo.ie...
>> 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 · 07-13-2006, 12:40 PM

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 "ron@debruin.nl", _
"This is the Subject line"
On Error GoTo 0
End Sub

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

"John" <johnddd@yahoo.commie> wrote in message news:tuutg.11417$j7.315667@news.indigo.ie...
> 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" <rondebruin@kabelfoon.nl> wrote in message news:uppBnYppGHA.4216@TK2MSFTNGP04.phx.gbl...
>> 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" <johnddd@yahoo.commie> wrote in message news:n2utg.11415$j7.315708@news.indigo.ie...
>>> 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
>>>
>>
>>
>
>

John · 07-13-2006, 12:55 PM

Thanks Ron, that sorts that too

"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:eyXUOqppGHA.4408@TK2MSFTNGP04.phx.gbl...
> 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 "ron@debruin.nl", _
> "This is the Subject line"
> On Error GoTo 0
> End Sub
>
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "John" <johnddd@yahoo.commie> wrote in message
> news:tuutg.11417$j7.315667@news.indigo.ie...
>> 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" <rondebruin@kabelfoon.nl> wrote in message
>> news:uppBnYppGHA.4216@TK2MSFTNGP04.phx.gbl...
>>> 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" <johnddd@yahoo.commie> wrote in message
>>> news:n2utg.11415$j7.315708@news.indigo.ie...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>