Using VBA to send email to distribution list ... how to CC?

[S Davis]

Hey folks,

I've been using this for over a year now, so it works great. However,
I've run out of room on the single line I have been using within VBA
to send the email to addresses.

All the code below does is save one worksheet within the workbook to a
file named the current date, then email that seperate file out to a
distribution list. The distribution list (below,
(e-mail address removed) to (e-mail address removed)) has been manually
typed in and updated as our group expands. The problem is I have run
out of room *sheepish* and don't know how to either expand this
distribution list onto a second line, or dump some addresses into the
CC for outlook.

If that's unclear - VBA simply won't let me type anymore characters
into the line I am using for the distribution list, and I need to add
more addresses.

Here's the (simple) code:

Private Sub WorkbookOpen()
ActiveWorkbook.Worksheets("Daily Email").Copy
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.value
ActiveWorkbook.SaveAs "\\Our_Server\Location\" & "Fleet Daily Status -
" & Format(Date, "Mmm-dd-yy") & ".xls"
ActiveWorkbook.SendMail Recipients:=Array("(e-mail address removed)", [*
26 different addresses], "(e-mail address removed)")


Can anyone please help me expand the distribution list onto a second
line? is it something as simple as ", &" ?

 

[Ron de Bruin]

Hi S Davis

you can ise the _ to split code lines if you want

..SendMail Array("(e-mail address removed)", "(e-mail address removed)"), _
"This is the Subject line"

For CC and BCC use Outlook object model code (not SendMail code)
You can also send to a group in your Outlook address book then

There is code on my site
http://www.rondebruin.nl/sendmail.htm

Read the tips page for changing the To line
http://www.rondebruin.nl/mail/tips2.htm

 

[Ron de Bruin]

If you want to use the SendMail code you can add the addresses also in a range

Dim MyArr As Variant
MyArr = Sheets("mysheet").Range("C1:C10")
..SendMail MyArr, "This is the Subject line"



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi S Davis

you can ise the _ to split code lines if you want

.SendMail Array("(e-mail address removed)", "(e-mail address removed)"), _
"This is the Subject line"

For CC and BCC use Outlook object model code (not SendMail code)
You can also send to a group in your Outlook address book then

There is code on my site
http://www.rondebruin.nl/sendmail.htm

Read the tips page for changing the To line
http://www.rondebruin.nl/mail/tips2.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hey folks,

I've been using this for over a year now, so it works great. However,
I've run out of room on the single line I have been using within VBA
to send the email to addresses.

All the code below does is save one worksheet within the workbook to a
file named the current date, then email that seperate file out to a
distribution list. The distribution list (below,
(e-mail address removed) to (e-mail address removed)) has been manually
typed in and updated as our group expands. The problem is I have run
out of room *sheepish* and don't know how to either expand this
distribution list onto a second line, or dump some addresses into the
CC for outlook.

If that's unclear - VBA simply won't let me type anymore characters
into the line I am using for the distribution list, and I need to add
more addresses.

Here's the (simple) code:

Private Sub WorkbookOpen()
ActiveWorkbook.Worksheets("Daily Email").Copy
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.value
ActiveWorkbook.SaveAs "\\Our_Server\Location\" & "Fleet Daily Status -
" & Format(Date, "Mmm-dd-yy") & ".xls"
ActiveWorkbook.SendMail Recipients:=Array("(e-mail address removed)", [*
26 different addresses], "(e-mail address removed)")


Can anyone please help me expand the distribution list onto a second
line? is it something as simple as ", &" ?

 

[Dave Peterson]

Did you try using the line continuation characters (space character followed by
an underscore):

ActiveWorkbook.SendMail _
Recipients:=Array("(e-mail address removed)", "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)"_

Hey folks,

I've been using this for over a year now, so it works great. However,
I've run out of room on the single line I have been using within VBA
to send the email to addresses.

All the code below does is save one worksheet within the workbook to a
file named the current date, then email that seperate file out to a
distribution list. The distribution list (below,
(e-mail address removed) to (e-mail address removed)) has been manually
typed in and updated as our group expands. The problem is I have run
out of room *sheepish* and don't know how to either expand this
distribution list onto a second line, or dump some addresses into the
CC for outlook.

If that's unclear - VBA simply won't let me type anymore characters
into the line I am using for the distribution list, and I need to add
more addresses.

Here's the (simple) code:

Private Sub WorkbookOpen()
ActiveWorkbook.Worksheets("Daily Email").Copy
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.value
ActiveWorkbook.SaveAs "\\Our_Server\Location\" & "Fleet Daily Status -
" & Format(Date, "Mmm-dd-yy") & ".xls"
ActiveWorkbook.SendMail Recipients:=Array("(e-mail address removed)", [*
26 different addresses], "(e-mail address removed)")

Can anyone please help me expand the distribution list onto a second
line? is it something as simple as ", &" ?

 

[Dave Peterson]

Typo alert:

ActiveWorkbook.SendMail _
Recipients:=Array("(e-mail address removed)", "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)")

(I wanted a closing paren at the end)

Did you try using the line continuation characters (space character followed by
an underscore):

ActiveWorkbook.SendMail _
Recipients:=Array("(e-mail address removed)", "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)", _
"(e-mail address removed)", "(e-mail address removed)"_

Hey folks,

I've been using this for over a year now, so it works great. However,
I've run out of room on the single line I have been using within VBA
to send the email to addresses.

All the code below does is save one worksheet within the workbook to a
file named the current date, then email that seperate file out to a
distribution list. The distribution list (below,
(e-mail address removed) to (e-mail address removed)) has been manually
typed in and updated as our group expands. The problem is I have run
out of room *sheepish* and don't know how to either expand this
distribution list onto a second line, or dump some addresses into the
CC for outlook.

If that's unclear - VBA simply won't let me type anymore characters
into the line I am using for the distribution list, and I need to add
more addresses.

Here's the (simple) code:

Private Sub WorkbookOpen()
ActiveWorkbook.Worksheets("Daily Email").Copy
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.value
ActiveWorkbook.SaveAs "\\Our_Server\Location\" & "Fleet Daily Status -
" & Format(Date, "Mmm-dd-yy") & ".xls"
ActiveWorkbook.SendMail Recipients:=Array("(e-mail address removed)", [*
26 different addresses], "(e-mail address removed)")

Can anyone please help me expand the distribution list onto a second
line? is it something as simple as ", &" ?

 

[JLGWhiz]

Why not control the distribution list with the e-mail program. In Outlook,
you can set up groups which can be modified as needed and you would only have
to use the group name in the send mail command in VBA. That would shorten
the code considerably.