Wow I can’t believe I (with very little programming experience) hav
been able to put together a rather complex program. Even my boss i
impressed. I have but one final question and that will be the end o
what I need to make things perfect.

I currently have my little macro hard coded to send the emails to me.
For testing and what not and now it works perfectly. I would like t
have starting at B15 and going down. B15, B16, B17 and so on a list o
email addresses that the program will automatically send the same emai
that has been going to me to these others. I was thinking I could jus
do the hard code thing 5 or so times but I would like the ability fo
my boss to be able to add or remove an email address and have it wor
correctly.

If this is too hard that’s ok you have been a great help on the rest.

As always- thanks in advance

--
Message posted from http://www.ExcelForum.com

Look at my site
http://www.rondebruin.nl/sendmail.htm

Or post back with a piece of your code

--
Regards Ron de Bruin
http://www.rondebruin.nl


"sungen99 " wrote in message ...
Wow I can't believe I (with very little programming experience) have
been able to put together a rather complex program. Even my boss is
impressed. I have but one final question and that will be the end of
what I need to make things perfect.

I currently have my little macro hard coded to send the emails to me.
For testing and what not and now it works perfectly. I would like to
have starting at B15 and going down. B15, B16, B17 and so on a list of
email addresses that the program will automatically send the same email
that has been going to me to these others. I was thinking I could just
do the hard code thing 5 or so times but I would like the ability for
my boss to be able to add or remove an email address and have it work
correctly.

If this is too hard that's ok you have been a great help on the rest.

As always- thanks in advance.


---
Message posted from http://www.ExcelForum.com/

This is my code:


___________________



Sub Mail_workbook_Outlook()
'This example send the last saved version of the Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
strdate = Format(Now, "mm-dd-yy")



Application.DisplayAlerts = False

Dim wb As Workbook
Sheets(Array("US for 69221", "European for 69221")).Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:="C:\xxx\xxx\xxxBilling\xxx\xxxx "
Format(Date, "mm-dd-yy") & ".xls"
wb.Close False

Application.DisplayAlerts = True



Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "xxxx recap " & strdate & " (nospam4me)"
Dim strbody As String
strbody = "Dear xxx," & vbNewLine & vbNewLine & _
"Attached, please find the trade recap for the US markets an
European fills" & vbNewLine & _
"" & vbNewLine & _
"Best Reguards," & vbNewLine & _
"Ken" & vbNewLine & _
"" & vbNewLine & _
""
.Body = strbody

.Attachments.Add ("C:\xxx\xxx\xxx\xx\xx Fills " & Format(Date
"mm-dd-yy") & ".xls")
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing



Sheets(Array("US for 69221", "European for 69221")).PrintOut



End Sub

___________________________________________

--
Message posted from http://www.ExcelForum.com

http://www.rondebruin.nl/sendmail.htm#Outlook
Look here sungen99 for changing the To line


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sungen99 " wrote in message ...
This is my code:


___________________



Sub Mail_workbook_Outlook()
'This example send the last saved version of the Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
strdate = Format(Now, "mm-dd-yy")



Application.DisplayAlerts = False

Dim wb As Workbook
Sheets(Array("US for 69221", "European for 69221")).Copy
Set wb = ActiveWorkbook
wb.SaveAs Filename:="C:\xxx\xxx\xxxBilling\xxx\xxxx " &
Format(Date, "mm-dd-yy") & ".xls"
wb.Close False

Application.DisplayAlerts = True



Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = "xxxx recap " & strdate & " (nospam4me)"
Dim strbody As String
strbody = "Dear xxx," & vbNewLine & vbNewLine & _
"Attached, please find the trade recap for the US markets and
European fills" & vbNewLine & _
"" & vbNewLine & _
"Best Reguards," & vbNewLine & _
"Ken" & vbNewLine & _
"" & vbNewLine & _
""
Body = strbody

Attachments.Add ("C:\xxx\xxx\xxx\xx\xx Fills " & Format(Date,
"mm-dd-yy") & ".xls")
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing



Sheets(Array("US for 69221", "European for 69221")).PrintOut



End Sub

____________________________________________


---
Message posted from http://www.ExcelForum.com/

Ron so it looks like I need to use this code.

Dim cell As Range
Dim strto As String
For Each cell I
ThisWorkbook.Sheets("Sheet1").Columns("C").Cells.S pecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
_______________________________________

But my question is how would this wrap around my code? Does it com
after or do I have to merge the two? I assume that the code below (m
(well actually yours) code) would need to be altered and honestly thi
it beyond me on how to change if…. thens within if….thens….. Sorry t
be a big pain in the arse on this.


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = "xxxx recap " & strdate & " (nospam4me)"
Dim strbody As String
strbody = "Dear xxx," & vbNewLine & vbNewLine & _
"Attached, please find the trade recap for the US markets and
European fills" & vbNewLine & _
"" & vbNewLine & _
"Best Reguards," & vbNewLine & _
"Ken" & vbNewLine & _
"" & vbNewLine & _
""
Body = strbody

Attachments.Add ("C:\xxx\xxx\xxx\xx\xx Fills " & Format(Date,
"mm-dd-yy") & ".xls")
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothin

--
Message posted from http://www.ExcelForum.com

Here is a example with the E-mail addresses in Column C

Sub Mail_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim strto As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstant s)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
If strto < "" Then strto = Left(strto, Len(strto) - 1)

With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"sungen99 " wrote in message ...
Ron so it looks like I need to use this code.

Dim cell As Range
Dim strto As String
For Each cell In
ThisWorkbook.Sheets("Sheet1").Columns("C").Cells.S pecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
_______________________________________

But my question is how would this wrap around my code? Does it come
after or do I have to merge the two? I assume that the code below (my
(well actually yours) code) would need to be altered and honestly this
it beyond me on how to change if.. thens within if..thens... Sorry to
be a big pain in the arse on this.


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = "xxxx recap " & strdate & " (nospam4me)"
Dim strbody As String
strbody = "Dear xxx," & vbNewLine & vbNewLine & _
"Attached, please find the trade recap for the US markets and
European fills" & vbNewLine & _
"" & vbNewLine & _
"Best Reguards," & vbNewLine & _
"Ken" & vbNewLine & _
"" & vbNewLine & _
""
Body = strbody

Attachments.Add ("C:\xxx\xxx\xxx\xx\xx Fills " & Format(Date,
"mm-dd-yy") & ".xls")
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing


---
Message posted from http://www.ExcelForum.com/

Here's what I did:
Create a hidden sheet with a list -- in the A column -- of the email addys and
label it "Email List".
This piece of code captures the range of emails:

'Get email reciepients
Sheets("Email List").Activate
Range("A1").Select
Selection.CurrentRegion.Select
Set rSend_To = ActiveCell.CurrentRegion 'Load region into an array

Later in my code...

Dim sRecipients As String
endloop = rSend_To.Count 'Total array number
If endloop 1 Then
For xloop = 1 To endloop
sRecipients = sRecipients & rSend_To.Value2(xloop, 1) & "; "
Next
Else
sRecipients = sRecipients & rSend_To.Value2 & "; "
End If

When you send the email, sRecipients will be used for your .Configuration.To =
sRecipients i.e. .Configuration.To = sRecipients

Yes, I used Ron's web site on CDO emailing to help me (and help me it did! :-)
--
Toby Erkson
Oregon, USA

"sungen99 " wrote in message
...
Wow I can’t believe I (with very little programming experience) have
been able to put together a rather complex program. Even my boss is
impressed. I have but one final question and that will be the end of
what I need to make things perfect.

I currently have my little macro hard coded to send the emails to me.
For testing and what not and now it works perfectly. I would like to
have starting at B15 and going down. B15, B16, B17 and so on a list of
email addresses that the program will automatically send the same email
that has been going to me to these others. I was thinking I could just
do the hard code thing 5 or so times but I would like the ability for
my boss to be able to add or remove an email address and have it work
correctly.

If this is too hard that’s ok you have been a great help on the rest.

As always- thanks in advance.


---
Message posted from http://www.ExcelForum.com/

I am sorry for being so difficult (stupid) but as I’m not really
programmer (just good at taking code and making little changes to fi
my applications) I don’t understand how I would incorporate my cod
with the looping email list. Do I have to place the code you guys ar
speaking of around my email send? Or does it take the place of it- o
is it just a component above or below my current program?

Again sorry for being so difficult

--
Message posted from http://www.ExcelForum.com