E-mail to a list in worksheet

[Ed]

Is there an easy way to send e-mail to a list of addresses I have in
an
Excel worksheet? I'm fairly proficient in VBA.

TIA

Ed

 

[Ron de Bruin]

Hi Ed

Outlook or Outlook Express ?
Body or Attachment ?
Personalize or one basic mail to all ?

 

[Ed]

Hi Ron,


My data, a club membership list, is in an Excel worksheet. I normally
use Outlook for reading/sending
e-mail, but have no reason to avoid using OE for this particular task.

We normally communicate with our members by snail-mail, but sometimes
need to get something
out to them quickly. Typically, this would be the same short note to
all members. I would prefer it to be in the body
so people don't have to do an additional step. Also, some people have
their spam filtering
set up to reject attachments.

Thanks for your interest.

Ed

 

[Ron de Bruin]

Hi Ed

Try this example

http://www.rondebruin.nl/mail/folder3/smallmessage.htm
If you use Example 2 you can add the text in a text file

Change the To line to .To = strto

And add the code below to the macro to send to all the addresses in Column C in "Sheet1"

Dim cell As Range
Dim strto As String
For Each cell In 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)

 

[Ed]

Ron,

Thanks!

That sets up the message in an Outlook message. I then have to click
on the Send manually. Can I
automate that final step? I wouldn't mind doing it manually if I
wanted to but all recipients in
the same message. However, since there will be about 150 addresses I'm
a little afraid
it will be treated as Spam by someone along the way.... or I might
even be fingered as a spammer
and get put on a blackhole list! If I can automate the send I could
put it in a loop and send out
15 messages each to 10 people. What do you think?

Ed

Hi Ed

Try this example

http://www.rondebruin.nl/mail/folder3/smallmessage.htm
If you use Example 2 you can add the text in a text file

Change the To line to .To = strto

And add the code below to the macro to send to all the addresses in
Column C in "Sheet1"

Dim cell As Range
Dim strto As String
For Each cell In 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)

[/QUOTE]

 

[Ron de Bruin]

Oops

I update almost all pages yesterday and forgot to change it on this page to
..Send

Try this one you can change Send to Display to test it

Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim cell As Range
Dim strto As String
Dim I As Integer


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

For I = 1 To 150 Step 10
strto = ""
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Range("C" & I).Resize(10).Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Send 'or use .Display
End With
On Error GoTo 0
Next I

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


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

Ron,

Thanks!

That sets up the message in an Outlook message. I then have to click on the Send manually. Can I
automate that final step? I wouldn't mind doing it manually if I wanted to but all recipients in
the same message. However, since there will be about 150 addresses I'm a little afraid
it will be treated as Spam by someone along the way.... or I might even be fingered as a spammer
and get put on a blackhole list! If I can automate the send I could put it in a loop and send out
15 messages each to 10 people. What do you think?

Ed

[/QUOTE]

 

[Ed]

Yeah, that makes sense. I couldn't figure out what the difference was
between display and Display

Ed

 

[Ed]

Ron,

Everything is working pretty good. One thing I'd like to do though is
to set the mail account under which
the messages will be sent. What happens now is the .Send puts all the
messages into the Outlook Outbox folder,
getting sent next time I do a Send all from the Tools menu. However,
they will all go out under my default
mail account. I would like them to go out under another mail account
I've set up speciffically for e-mails
related to the club. I've tried .Account = (e-mail address removed)
but that has no effect.

Any ideas?

TIA