I am adapting Ron DeBruin's code for sending email to a range of names. It is
reproduced below:

Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim SigString As String
Dim Signature As String
Dim Assgn As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
SigString = "C:\Documents and Settings\" & Environ("username") & _
"\Application Data\Microsoft\Signatures\blah blah.txt"
If Dir(SigString) < "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
Assgn = Range("B1").Value

For Each cell In Range("D6:D100").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value < "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value & "
.Subject = "Your Grade For " & Assgn
.Body = "Dear " & cell.Offset(0, -2).Value & vbNewLine &
vbNewLine & _
"Please contact us to discuss bringing your account
up to date" & Signature
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

After every email a multi-second window pops up asking if I want to allow
Excel to use Outlook. For 50 emails, this gets old. Is there any way to
induce the "use Outlook for X minutes" option?

Many thanks

I found Ron's code using CDO configuration. I used his optional configuration
code because the vanilla version wasn't sending.

Now however, when I got to the send command, I received a "couldn't connect
with server". That's prolly because my server requires authentication. Any
code help much appreciated.

"John V" wrote:

I am adapting Ron DeBruin's code for sending email to a range of names. It is
reproduced below:

Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim SigString As String
Dim Signature As String
Dim Assgn As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
SigString = "C:\Documents and Settings\" & Environ("username") & _
"\Application Data\Microsoft\Signatures\blah blah.txt"
If Dir(SigString) < "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
Assgn = Range("B1").Value

For Each cell In Range("D6:D100").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value < "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value & "
.Subject = "Your Grade For " & Assgn
.Body = "Dear " & cell.Offset(0, -2).Value & vbNewLine &
vbNewLine & _
"Please contact us to discuss bringing your account
up to date" & Signature
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

After every email a multi-second window pops up asking if I want to allow
Excel to use Outlook. For 50 emails, this gets old. Is there any way to
induce the "use Outlook for X minutes" option?

Many thanks

Hi John

I use Express clickyes v1.2 available here

http://www.contextmagic.com/

This is a utility which automatically performs the yes function for the VBA
security pop up you described in realtion to sending email via Outlook

Good Luck

N10



"John V" wrote in message
...
I am adapting Ron DeBruin's code for sending email to a range of names. It
is
reproduced below:

Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim SigString As String
Dim Signature As String
Dim Assgn As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
SigString = "C:\Documents and Settings\" & Environ("username") & _
"\Application Data\Microsoft\Signatures\blah blah.txt"
If Dir(SigString) < "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
Assgn = Range("B1").Value

For Each cell In
Range("D6:D100").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value < "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value & "
.Subject = "Your Grade For " & Assgn
.Body = "Dear " & cell.Offset(0, -2).Value & vbNewLine &
vbNewLine & _
"Please contact us to discuss bringing your account
up to date" & Signature
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

After every email a multi-second window pops up asking if I want to allow
Excel to use Outlook. For 50 emails, this gets old. Is there any way to
induce the "use Outlook for X minutes" option?

Many thanks