Re: Can I disable Outlook warning from Excel macro "Send" method

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for the Quit suggestion.
I also discovered an intermittent Error 9 (subscript out of range) on
'XL.Windows("Master.xls").Visible = True' because sometimes the preceding
statement,
'Set WB = GetObject("\\Server\Folder\Master.xls")' did not open the workbook.
I fixed that by replacing those two lines with:
XL.Workbooks.Open FileName:="\\Server\Folder\Master.xls",
which makes more sense, anyway. Live & learn!

Thanks, again.
--
Will


"JP" wrote:

Nice job! You might also want to call the Quit Method on the Excel
Application Object, to make sure that instance of Excel is destroyed.
Just setting the reference to Nothing may not be enough.

HTH


On Jan 5, 5:11 pm, wpiet <wp...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Ken & JP,

Thanks to both of you, everything works beautifully.

For the benefit of others looking for such a solution, here's the relevant
code from my Outlook macro that creates e-mails from an Excel workbook:

(*** 'E-Mail' has 2 columns; 'A' = e-mail addresses; 'B' = text of
messages)

Sub MacroName()
Dim XL As Object
Dim WB As Workbook
Dim Cell As Range
Dim EmlMsg As MailItem

' Open Excel & workbook

Set XL = CreateObject("Excel.Application")
XL.Visible = True
Set WB = GetObject("\\Server\Folder\Master.xls")
XL.Windows("Master.xls").Visible = True

' Send e-mails

For Each Cell In XL.Sheets("E-Mail").Columns("B").Cells
If Cell.Value = "" Then
Exit For
Else
Set EmlMsg = CreateItem(0)

With EmlMsg
.To = Cell.Offset(0, -1)
.Subject = "Your Subject Here"
.Body = Cell.Value
.Send
End With

Set EmlMsg = Nothing
End If
Next Cell

' Release workbook & Excel application

Set WB = Nothing
Set XL = Nothing
End Sub
--
Will


.

Quantcast