Sending an email to more than one person.

[sungen99]

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

 

[Ron de Bruin]

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

Or post back with a piece of your code

 

[sungen99]

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 = "(e-mail address removed)"
.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

___________________________________________

 

[Ron de Bruin]

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

 

[sungen99]

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.SpecialCells(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")

 

[Ron de Bruin]

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(xlCellTypeConstants)
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

 

[Toby Erkson]

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!

 

[sungen99]

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