Error on submitting an Excel Sheet using Outlook

[Guest]

I submitted the same tread a few weeks ago. As of today, I have not found a
solution for the problem. I hope one of you had the same experience in the
past and would like to share with me on the solution of the problem.

Basically, I use the code from Ron (see below) to email an Excel sheet using
Microsoft Outlook. Everyone in the company who uses this code has no problem
using it, except one person. Everytime, he uses it on his computer he
receives an automation error (Runtime error: 2147024770(8007007e)). When I
debugged it, it pointed me to the code "Set OutApp =
CreateObject("Outlook.Application")".

When I ask him to send me his file (so that I can try it on my computer) and
when I try it on my computer, I don't have any problem.

I checked "References" in VBA on his computer, and he has all the references
that I use. I also tried to submit the sheet when the Outlook is opened, and
I still had problem.

I know the problem is on his computer, not the code. I am trying to figure
out what he is missing on his computer to trigger that problem.

Any help is appreciated.

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing

 

[Ron de Bruin]

Hi AccessHelp

Try Late Binding
See the example on my site

There are AV programs that block this kind of code

Have you try the SendMail example on that machine
http://www.rondebruin.nl/mail/folder1/mail2.htm

 

[Guest]

Good morning Ron,

Thanks for your help. We tried the late binding code below from you site,
and it didn't work. He does have a Trio hooked up to Outlook. Do you think
it's causing to happen?

This is what happened after replacing the late binding code when we tested.
After running the code, we didn't get any error message (Before we got an
automation error message and a security warning from Outlook). When I looked
up his "Sent Items" box in Outlook, I didn't see that email. I also checked
the email box that supposed to send to, and his email never got into that
email account.

I also tried it on my computer after replacing with Late Binding, and I
didn't have any problem. I even got a security warning message from Outlook.


What do you think? As always, I appreciate your help. Below are the codes
(I replaced the second one with the first one).

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutMail = OutApp.CreateItem(olMailItem)

Dim OutApp As Object
Dim OutMail As Object
Set OutMail = OutApp.CreateItem(0)

 

[Ron de Bruin]

Hi AccessHelp

Sounds like a machine with problems <g>

If you use the code with Early binding you also get errors. Am I right.
Have you try the SendMail example
http://www.rondebruin.nl/mail/folder1/mail2.htm

 

[Guest]

Hi Ron,

When I use the late binding on his laptop, I didn't get any error message,
not even the security warning from Outlook. It looked like it went through
but it's not. I didn't see that email in his Sent Item folder. Also I
didn't see that email coming in when I checked the Inbox of the email that we
sent.

However, when I use it on my laptop, I got an Outlook security warning and
went throught.

I have not tried the SendMail code. How is it different from the other code?

Have you heard of that problem before? Thanks.

 

[Ron de Bruin]

Is Outlook the default mail program on his laptop

Start>Settings>Control Panel....Internet options (Program Tab)
Close Excel first before you make a change.

 

[Guest]

Yes, Outlook is the default mail program on his laptop. Is it a factor?

Thanks.