bulk mailing problem
 

Hi,

I'm writing an utility for our club to keep track of adresses,
members, finance,...
One posibility is to send automaticaly an email to all members in the
month of December for their membership renewal.
I have now 24 email addresses where i have to send the message, and
this list will extend, but it won't work.

I do have an error when sending the mail, saying there's an email
address refused.
When i copy / paste the strMailto string into the 'To' field from
Outlook Express, it does work. Strange, isn't it?
I'm using Excel 2000 (NO service packs installed !) & Outlook Express
5.5

Question:
1) How many recipient addresses can i place in the 'To' textbox?
2) How can i create with VBA code a 'Group' with recipient addresses,
so hopefully preventing above problem.
Let say that i have in the future 500 recipient addresses where i do
have to send the same message.

I use following code in the frmBulkMail form to create the mail:
REMARK : The strMailTo variable is a Public variable

Public strMailTo as String

Private Sub cmdSendMembers_Click()
MemberMailAddresses 'create recipient list - strMailTo contains
ALL recipient addresses
Sheets("blad1").Activate
Send 'send the mail
With Me
.txtSubject.Enabled = True 'enable subject field for
input
End With
blContactMe = False
Unload Me
End Sub
-------------------------------
This routine creates the recipient string

Sub MemberMailAddresses()
Dim PreviousAddress As String
Dim ActualAddress As String
strMailTo = ""
Sheets("members").Activate
Range("J2").Select 'this column contains the email address
Do
Selection.Offset(1, 0).Select 'select next email address
PreviousAddress = Trim(Selection.Offset(-1, 0).Value)
ActualAddress = Trim(ActiveCell.Value)
If PreviousAddress = ActualAddress Or ActualAddress = "" Then 'if
PreviousAddress equals ActualAddress or ActualAddress = empty, select
next row
Selection.Offset(1, 0).Select
End If
If Trim(PreviousAddress) < "" Then
strMailTo = strMailTo & PreviousAddress & ";" 'create
recipient string, separate the address by ;
End If
Loop Until Selection.Offset(0, 1).Value = False 'True if member,
False if no member
'remove last pointcomma
strMailTo = Left$(strMailTo, Len(strMailTo) - 1) 'remove last ;
in recipient string 'strMailTo'
Debug.Print strMailTo
End Sub
--------------------------------
This routine is used to send the message using the 'strMailTo' string
as recipient

Sub Send()
frmBulkMail.MAPIMessages1.MsgIndex = -1
frmBulkMail.MAPISession1.SignOn
frmBulkMail.MAPIMessages1.SessionID =
frmBulkMail.MAPISession1.SessionID
If blContactMe = True Then
frmBulkMail.MAPIMessages1.RecipAddress =
"
Else
frmBulkMail.MAPIMessages1.RecipAddress = Trim(strMailTo)
End If
If Trim(frmBulkMail.txtSubject) = "" Then
frmBulkMail.MAPIMessages1.MsgSubject = "Dit is een
automatische mail verstuurd door BeLUG Admin."
Else
frmBulkMail.MAPIMessages1.MsgSubject = frmBulkMail.txtSubject
End If
frmBulkMail.MAPIMessages1.MsgNoteText = frmBulkMail.tbMessage
frmBulkMail.MAPIMessages1.Send
frmBulkMail.MAPISession1.DownLoadMail = True
frmBulkMail.MAPISession1.SignOff
End Sub

Thanks for any help,
Regards,
Ludo

bulk mailing problem
 

Hi Ludo

Try John's example or the macro I changed from John's Site
http://www.j-walk.com/ss/excel/tips/tip86.htm
http://www.rondebruin.nl/sendmail.htm#body

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ludo Soete" wrote in message om...
Hi,

I'm writing an utility for our club to keep track of adresses,
members, finance,...
One posibility is to send automaticaly an email to all members in the
month of December for their membership renewal.
I have now 24 email addresses where i have to send the message, and
this list will extend, but it won't work.

I do have an error when sending the mail, saying there's an email
address refused.
When i copy / paste the strMailto string into the 'To' field from
Outlook Express, it does work. Strange, isn't it?
I'm using Excel 2000 (NO service packs installed !) & Outlook Express
5.5

Question:
1) How many recipient addresses can i place in the 'To' textbox?
2) How can i create with VBA code a 'Group' with recipient addresses,
so hopefully preventing above problem.
Let say that i have in the future 500 recipient addresses where i do
have to send the same message.

I use following code in the frmBulkMail form to create the mail:
REMARK : The strMailTo variable is a Public variable

Public strMailTo as String

Private Sub cmdSendMembers_Click()
MemberMailAddresses 'create recipient list - strMailTo contains
ALL recipient addresses
Sheets("blad1").Activate
Send 'send the mail
With Me
.txtSubject.Enabled = True 'enable subject field for
input
End With
blContactMe = False
Unload Me
End Sub
-------------------------------
This routine creates the recipient string

Sub MemberMailAddresses()
Dim PreviousAddress As String
Dim ActualAddress As String
strMailTo = ""
Sheets("members").Activate
Range("J2").Select 'this column contains the email address
Do
Selection.Offset(1, 0).Select 'select next email address
PreviousAddress = Trim(Selection.Offset(-1, 0).Value)
ActualAddress = Trim(ActiveCell.Value)
If PreviousAddress = ActualAddress Or ActualAddress = "" Then 'if
PreviousAddress equals ActualAddress or ActualAddress = empty, select
next row
Selection.Offset(1, 0).Select
End If
If Trim(PreviousAddress) < "" Then
strMailTo = strMailTo & PreviousAddress & ";" 'create
recipient string, separate the address by ;
End If
Loop Until Selection.Offset(0, 1).Value = False 'True if member,
False if no member
'remove last pointcomma
strMailTo = Left$(strMailTo, Len(strMailTo) - 1) 'remove last ;
in recipient string 'strMailTo'
Debug.Print strMailTo
End Sub
--------------------------------
This routine is used to send the message using the 'strMailTo' string
as recipient

Sub Send()
frmBulkMail.MAPIMessages1.MsgIndex = -1
frmBulkMail.MAPISession1.SignOn
frmBulkMail.MAPIMessages1.SessionID =
frmBulkMail.MAPISession1.SessionID
If blContactMe = True Then
frmBulkMail.MAPIMessages1.RecipAddress =
"
Else
frmBulkMail.MAPIMessages1.RecipAddress = Trim(strMailTo)
End If
If Trim(frmBulkMail.txtSubject) = "" Then
frmBulkMail.MAPIMessages1.MsgSubject = "Dit is een
automatische mail verstuurd door BeLUG Admin."
Else
frmBulkMail.MAPIMessages1.MsgSubject = frmBulkMail.txtSubject
End If
frmBulkMail.MAPIMessages1.MsgNoteText = frmBulkMail.tbMessage
frmBulkMail.MAPIMessages1.Send
frmBulkMail.MAPISession1.DownLoadMail = True
frmBulkMail.MAPISession1.SignOff
End Sub

Thanks for any help,
Regards,
Ludo