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
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