G
Guest
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("D6100").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value <> "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value & "@xyz.edu"
.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
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("D6100").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value <> "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value & "@xyz.edu"
.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