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:
(sheet '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
"Ken Slovak - [MVP - Outlook]" wrote:
If you are running the code from the Outlook VBA project and you use
Application there as your Outlook.Application object you will have a trusted
Application object. Then you derive all of your other Outlook objects from
that trusted Application object and those objects will be trusted. That will
eliminate the security warnings.
You can use the Outlook VBA code to open Excel and the workbook you want and
use code to extract the contents you want for the emails, all within your
Outlook VBA code. That's the way I'd be doing it.
The Excel VBA macro code can be moved over to Outlook, you will need to add
starter code to start Excel and open the workbook, and to fully qualify all
Excel references (Excel.Range instead of Range, etc.). Then the code for the
email can be done using Outlook automation code.
There should be examples of that sort of thing up on www.outlookcode.com,
try searching there for "Excel" and see what turns up.
--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm
"wpiet" wrote in message
...
Thanks for the suggestion, Ken. I'll be looking into that.
Is it possible to, alternatively, start from Outlook, access the Excel
workbook & process the VBA macro there which creates the e-mails & sends
them?
I'm absolutely new at VB coding. I recorded the macro in Excel to set up
the
data for the e-mails, then had to figure out a way to add the code to that
macro to create & send the e-mails using that data. Everything works
perfectly except for having to manually reply to the "Object Model Guard."
Thanks,
Will