Need Help with SendMail

[Guest]

Hello,

I copied the codes below from Ron de Bruin's web site and used it for my
Excel to email using Outlook. Everything is fine until when I get the
message from Outlook about a program is trying to send something and whether
I want to send it.

If I click "Yes", the email and attachement are sent. However, when I click
"No", I got this error message "Run-time error '287': Application-defined or
object-defined error". I have an option of "End" or "Debug". When I click
on "Debug", it points me to the code ".Send" in the below coding.

My first question is can I write a code to receive the pop-up window from
Outlook and it would send it automatically? If I can't, (my second question
is) what code I should write to avoid the above error message when a user
clicks on "No" on the Outlook window.

Thanks. Here are the codes:

Sub Mail_ActiveSheet_Outlook()
'You must add a reference to the Microsoft outlook Library
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
End Sub

 

[Ron de Bruin]

Hi AccessHelp

You can use a on error code

On Error Resume Next
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
On Error GoTo 0

 

[Guest]

Ron,

Thanks very much for your help. It works. I do have one more question.

Is there a code that we can write to prevent the message from Outlook
popping-up?

Thanks again.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/prevent.htm

 

[karnak]

Sub send()

ActiveWorkbook.SendMail "(e-mail address removed)"

End Sub

Try the code above to get rid of the warning but to no avail. Is there
something I am missing.

Thanks for your answer

 

[Ron de Bruin]

Have you read the information in the link ?
http://www.rondebruin.nl/mail/prevent.htm

 

[karnak]

Yes I have been there and tried it, the problem though is that this
time, I don't have an SMTP server from work.
I will ask my IT guy if we do have one. We have an exchange server
though.

If I can't have it worked or if I can I will let you know,

Thank you for your response