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

[Xeroid]

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:

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



Xeroid.

 

[Jon von der Heyden]

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]

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.

 

[Jon von der Heyden]

Hmmm, in which instance maybe using another method may work. See this thread: http://www.mrexcel.com/board2/viewtopic.php?t=191581

This uses send keys and works for me.

 

[Xeroid]

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

 

[Jon von der Heyden]

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]

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

...???

 

[Norie]

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]

Thanks anyway fellas!

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

Regards,

Xeroid.