PDA

View Full Version : email active sheet problems



CCkfm2000
01-03-2007, 01:26 AM
happy new year to all...

i'm using the following code to email the active sheet but keep getting an error message

run-time error 429
activex component can't create object

using office 2002.



Option Explicit
Sub auto_open()
'Sub CheckOLopen()
Dim AppOutLook As Object

On Error Resume Next
Set AppOutLook = GetObject(, "Outlook.Application")
On Error GoTo 0
If AppOutLook Is Nothing Then
MsgBox "Please Open Outlook, This E-Mail Will Stay In Your Outbox Till You Do !"
Else
'MsgBox "Outlook is open, so no need to open it."
End If
End Sub
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
Wb As Workbook, _
FileName As String
Sheets("Stats").Select
'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set Wb = ActiveWorkbook
FileName = "Fridays Register 3rd Quarter 2006.xls"
On Error Resume Next
Kill "c:\" & FileName
On Error GoTo 0
Wb.SaveAs FileName:="c:\Fridays Register 3rd Quarter 2006 stats.xls"

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application") ' ******** error on this line *******
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient


.To = "abc@xyz.co.uk"



'Uncomment the line below to hard code a subject
.Subject = "Fridays Register 3rd Quarter 2006 stats"
.Attachments.Add Wb.FullName
.Body = "See Attached File Please...."
'Send it right away, use .Display to just show the mail
' Comment out below line and uncomment .send if you just want to send it
'.display
.send
End With

'Delete the temporary file
Wb.ChangeFileAccess Mode:=xlReadOnly
Kill Wb.FullName
Wb.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
MsgBox ("your e-mail has been sent. thanks for using this program, have a nice day, please call again.")
End Sub





thanks

Bob Phillips
01-03-2007, 01:30 AM
Do you have Outlook installed? The line seems okay, and you are using late-binding, so unless you don't have Outlook, I can't see why it fails.

CCkfm2000
01-03-2007, 01:58 AM
outlook is installed.

Bob Phillips
01-03-2007, 02:51 AM
I have just tried your code, and I do have Outlook installed, and it works fine.

A couple of other thoughts though.

- why don't you just start Outlook in the auto_open code if it is not started

- why do you create another instance of Excel? You ask them to start Outlook and then ignore it

- why kill the file when you then SaveAs, it will just overwrite.

I know these are not the problem, but it would simplify the code.

CCkfm2000
01-03-2007, 02:57 AM
answer to all your questions is i just found the code and used it. not very good with vba.

would you be able to show me the code.

would be very greatfull to you.

Bob Phillips
01-03-2007, 03:33 AM
Option Explicit

Private oApp As Object

Sub auto_open()
'Sub CheckOLopen()
Dim AppOutLook As Object

On Error Resume Next
Set oApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If oApp Is Nothing Then
Set oApp = CreateObject("Outlook.Application")
If oApp Is Nothing Then
MsgBox "Serious error, could not start Outlook"
End If
End If
End Sub
Sub EmailWithOutlook()
'Variable declaration

Dim oMail As Object, _
Wb As Workbook, _
FileName As String

If Not oApp Is Nothing Then

Sheets("Stats").Select
'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set Wb = ActiveWorkbook
FileName = "Fridays Register 3rd Quarter 2006"
Application.DisplayAlerts = False
Wb.SaveAs FileName:="c:\" & FileName & ".xls"
Application.DisplayAlerts = True

Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "abc@xyz.co.uk"

'Uncomment the line below to hard code a subject
.Subject = FileName
.Attachments.Add Wb.FullName
.Body = "See Attached File Please...."
'Send it right away, use .Display to just show the mail
' Comment out below line and uncomment .send if you just want to send it
.display
'.send
End With

'Delete the temporary file
Wb.ChangeFileAccess Mode:=xlReadOnly
Kill Wb.FullName
Wb.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
MsgBox ("your e-mail has been sent. thanks for using this program, have a nice day, please call again.")

End If
End Sub