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

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,
to ) 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 ", [*
26 different addresses], ")


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

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

Hi S Davis

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

..SendMail ", "), _
"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


"S Davis" wrote in message ...
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,
to ) 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 ", [*
26 different addresses], ")


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

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

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


"Ron de Bruin" wrote in message ...
Hi S Davis

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

.SendMail ", "), _
"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


"S Davis" wrote in message ...
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,
to ) 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 ", [*
26 different addresses], ")


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

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

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

ActiveWorkbook.SendMail _
", ", _
", ", _
", "_


S Davis wrote:

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,
to ) 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 ", [*
26 different addresses], ")

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

--

Dave Peterson

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

Typo alert:

ActiveWorkbook.SendMail _
", ", _
", ", _
", ")

(I wanted a closing paren at the end)

Dave Peterson wrote:

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

ActiveWorkbook.SendMail _
", ", _
", ", _
", "_

S Davis wrote:

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,
to ) 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 ", [*
26 different addresses], ")

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

--

Dave Peterson

--

Dave Peterson

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

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.

"S Davis" wrote:

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,
to ) 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 ", [*
26 different addresses], ")


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