Send or Not Send dialog box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When sending an email from within Excel (2002) through Outlook (2002), I get
a popup dialog box asking permission to send the email.

Can I supress this dialog box or in some way not have it popup?

Thank you for the assistance.

Don
 
Sue,

Thank you for the information. Unfortunately, I cannot figure out how to
use "objMailItem.To = "(e-mail address removed)" ".

My code is as follows.

"
Sub SendEmails()
Set Source = Nothing
On Error Resume Next
Set Source = Selection
On Error GoTo 0
If Source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
If ActiveWindow.SelectedSheets.Count > 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count > 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If
Application.ScreenUpdating = False
ActiveSheet.Copy
Set Dest = ActiveWorkbook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = EmailTo
.Subject = "Need input to " & myProject
.Importance = olImportanceHigh
.FlagStatus = olFlagMarked
.FlagDueBy = Now + 2
.HTMLBody = _
"Need The following for each item with a required date but no
email date" _
& vbNewLine & RangetoHTML 'RangetoHTML is from following function
.Send
End With
Dest.Close False
Set OutMail = Nothing
Set OutApp = Nothing
Set Dest = Nothing
End Sub

Function RangetoHTML()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
Source:=Selection.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function
"

Can you, please, show me how to modify it to get around the popup blocking
message?

Thank you for the help.

Don
 
This is the statement that will trigger a security prompt:

OutMail.Send

Have you downloaded and installed the Redemption software that will let you rewrite your code to avoid that prompt?

BTW, you have this statement setting the To field of the new message:

OutMail.To = EmailTo

but I don't see any indication of where the value for EmailTo is supposed to come from.


--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
Hi Sue,

I have downloaded Redemption and will try working with it. Thank you.

The "EmailTo" comes from a do loop in the calling sub. The application is
sending several emails with differing content.

Thanks, again.

Don
 
Back
Top