Overcoming an error when trying to send e-mail in VBA...

Xeroid

Board Regular
Joined
Aug 14, 2002
Messages
64
I have a code in place to send the active file to a given address.

I have one user who keeps getting an error when she tries to run the macro.

Here's the code:
Code:
'Send the file in an Outlook message with formletter:
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With otlNewMail
.To = "me.myself@mycompany.com"
.Subject = "New box list for review - " & ActiveWorkbook.Name
.Body = "Please review the following list of new boxes to be picked up." & Chr(13) & Chr(13)
.Attachments.Add fName
.Display

End With

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
'
End Sub

The error that is generated reads: "Run-time error 429: ActiveX component can't create object".

I am entirely self taught (thanks in greatest part to this board) and I have really no understanding of ActiveX controls.

I notice that this problem only exists for 1 in over 100 users, so I think it is specific to the settings on her PC.

Any advice would be much appreciated so I can get the code to work for her.

(y)

Xeroid.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
I'm not sure if this is the solution, but try this on the users machine.

Go to the VBE (Alt +F11)

Tools -> References -> check 'Microsoft Outlook 11.0 Object Library

Regards
Jon
 

Xeroid

Board Regular
Joined
Aug 14, 2002
Messages
64
Fixing the ActiveX control to allow e-mail to send in VBA...

Thanks.

I talked with the user over the phone and it basically came down to some company implemented settings that preclude the macro from working. One of those paranoid security settings, I guess.

I guess the 'man' wins this one! I wouldn't be allowed to correct the problem. Even the "Visual Basic for Applications" box was turned off!

Thanks anyway.
 

Xeroid

Board Regular
Joined
Aug 14, 2002
Messages
64

ADVERTISEMENT

User Defined Type Not Defined error...

Hi Jon.

I tried the code in the thread you suggested, but I get a "User Defined Type Not Defined" error...???

:eek:
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Ah ok, this is the error I was thinking about before. In the VBE go to Tools > References and check 'Microsoft Outlook 11.0 Object Library'
 

Xeroid

Board Regular
Joined
Aug 14, 2002
Messages
64

ADVERTISEMENT

...It was already checked when I ran it the first time.

...???
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Xeriod

If the original problem was caused by a security setting then I don't see how any code will make a difference.

Especially when the security setting appears to be preventing code execution.
 

Xeroid

Board Regular
Joined
Aug 14, 2002
Messages
64
Thanks anyway fellas!

Thanks anyway. The security thing is simply beyond my control, but I appreciate the time and input.

Regards,

Xeroid.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,045
Messages
5,599,488
Members
414,313
Latest member
Oonagh123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top