E-mail an excel workbook to several users 1

[funkmonsteruk]

I have used the following code to e-mail a workbook (using Lotus Notes)to a single address:

Private Sub send_mail()

Dim Maildb As Object 'the mail database
Dim username As String
Dim maildbname As String
Dim maildoc As Object
Dim attachme As Object
Dim session As Object
Dim embedobject As Object
Dim mytime As String
Dim recipient As String
Dim subject As String
Dim attachment As String
Dim BodyText As String

recipient = "Andy P Smith\HBEU\HSBC@HSBC"
subject = " PSM Diary"
attachment = "C:\My Diary.xls"
BodyText = "Diary"
Set session = CreateObject("Notes.NotesSession&quot
username = session.username

Set Maildb = session.GETDATABASE("", maildbname)
If Maildb.ISOPEN = True Then

Else
Maildb.OPENMAIL
End If
Set maildoc = Maildb.CREATEDOCUMENT
maildoc.Form = "Memo"
maildoc.sendto = recipient
maildoc.subject = subject
maildoc.Body = BodyText


If attachment <> &quot;&quot; Then
Set attachme = maildoc.CREATERICHTEXTITEM(&quot;Attachment&quot
Set embedobject = attachme.embedobject(1454, &quot;&quot;, attachment, &quot;Attachment&quot


End If

maildoc.PostedDate = Now()
maildoc.send 0, recipient

Set Maildb = Nothing
Set maildoc = Nothing
Set attachme = Nothing
Set session = Nothing

MsgBox &quot;Mail sent ro&quot; & recipient & &quot;re:&quot; & subject
End Sub

However i need to send the workbook to several addresses at once, is it possible to reference a list of e-mail addresses to send the workbook to or do i have to add names individually to the code?

For example replacing the recipient = &quot;address&quot; line with a Recipient= Variable line in which the variable is a list of e-mail addresses

 

[acron]

Try the following,

Dim oCell As Range
For Each oCell In [Recipients]
    recipient = recipient & oCell.Value & Chr(44)
Next

which should work assuming you have a range of cells named [bold]Recipients[/bold] which hols the email addresses you wish to use.

A.C

 

[RobBroekhuis]

Hey Acron,
I just gave you a star for the [Recipients] syntax. I was previously unaware of that way of referring to a named range, and can't find any VBA help on it. Is it a documented feature? Can you point me in the direction of the help topic describing it? Using it will make a lot of code look cleaner!
Rob

 

[acron]

The syntax in question is a short cut for the Evaluate method of the Excel application. Search for Evaluate in your Excel VBA help.

You can replace Range(&quot;A1:A100&quot with [A1:A100]

It is also useful for using workshhett functions in VBA. Instead of having to use

= Application.WorksheetFunction.Sum(Range(&quot;A1:A100&quot)

you can use

= [SUM(A1:A100)]

A.C.

 

[RobBroekhuis]

Thanks - I'll go look for the evaluate help. Could you take a look at my follow-up question (different thread) as well?
Rob