RE: Using SendMail through Access - Excel Automation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!
--
-DinosRose - sometimes I think I have gotten in over my head...


"DinosRose" wrote:

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.
jack.smith@xxxxxxxxxxxxx;dinos.rose@xxxxxxxxxxxxx). 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!

--
-DinosRose - sometimes I think I have gotten in over my head...
.

Relevant Pages

  • string to array
    ... please help me with converting string to an array: ... Ako ste ovu poruku primili gre¹kom i nije vam namijenjena, ... Distribucija, kopiranje i otvaranje privitaka je strogo zabranjeno. ... recipient and may contain legally privileged or confidential information. ...
    (alt.php)
  • Re: Multiple Recipients in SendMail Method. HELP!!!
    ... Regards ... >> I am not attempting to add another recipient, ... >> array of text strings if there are multiple recipients. ... >> John Baker ...
    (microsoft.public.excel.programming)
  • Re: Error with Multiple Recipients on .SendMail
    ... addresses to an array and pass the array ) to .SendMail in the ... > Regards Ron de Bruin ... >> Unknown recipient name found in recipient list. ...
    (microsoft.public.excel.programming)
  • Re: How can I get X (or another value) from this string "asdfsadf[www]X[/www]"
    ... > string into an array and from there on i have everything sorted. ... If you want to go the regular expression way, ... If you want to go the strpos(), substrway, remember the third ...
    (comp.lang.php)
  • Question about references
    ... I have a question about refrences. ... I know if you have a function that returns an array and you designate ... it and its recipient by "at" signs the reference is included. ... Prev by Date: ...
    (comp.lang.php)