Using SendMail through Access - Excel Automation

[DinosRose]

I have a program which uses the automation in Access to send an Excel
spreadsheet via email using the following code:

ExcelObj.ActiveWorkbook.SendMail Recipients:=ito, Subject:="PHI OCR
Report_Chain 123_022008_1111"

where ito is a variable defined by information contained in the Email field
of a recordset.

In most cases, the Email field only contains the email address of one
person. However, we have a few of our customers who want this spreadsheet to
go to more than one contact. So there are some records where this field will
contain more than one email address separated by semi-colons (i.e.
(e-mail address removed);[email protected]). In this case, it returns
an error stating "Unknown recipient name found in the recipient list." I've
read the help file for SendMail in Excel which indicates that I should be
able to send to multiple recipients using an array, but I'm not certain how
to do this. Could someone please assist me with an example?

Thanks!

 

[ward376]

I use something like this to send sheets to multiple recipients in
Lotus Notes...

Recipients = VBA.Array _
("(e-mail address removed),[email protected]")

Cliff Edwards

 

[DinosRose]

I was able to find a solution to my problem. After reading up some more on
arrays and how to define them and working with a collegue to determine a way
to separate my string of email addresses into components within the array I
came up with the following solution (which involved changing the ito variable
to a variant ito()):

eml = rec("Issue To")

p = 0
Do
strpos = InStr(eml, ";")
If strpos = 0 Then
peml = eml
Else
peml = Left(eml, strpos - 1)
End If
ReDim Preserve ito(p)
ito(p) = peml

p = p + 1
eml = Right(eml, Len(eml) - strpos)
Loop Until strpos = 0

Anyway, wasn't sure if anyone else might find this useful, so I thought I
would share.

Thanks!