Printable View
I need to send batch emails. Well some groups (on certain criteria) contain e.g. 600 emails while some contain just 90 but the limit of the BCC field is 100 (ISP limitation).
How do i send all the email groups splitting those of 600 to 6 equal groups?
The emails are stored to the SQL Server 2000 database.
Thanks
Hi,
Can you determine beforehand the total number of emails in each criteria? If you can and if it is greater than 100 for a certain criteria then you can use a while loop or something and write code in that loop and send the emails in batches. That is if there are 600 addresses then you can write some code in a while loop and that sends 100 emails in each pass of the loop. I agree it is tedious coding but I am not sure if you can change the limitation of 100 in any way.
Regards,
sr_jay
Quote:
Originally Posted by selanec
Hi sr_jay, thanks for the pointers. it's much appreciated. Can you at least post a small code snippet that i better understand the idea. thanks
Hi!
I did an app that would send heaps of emails (several thousands), and I used a database table where I stored the emails to be sent and then had a stored procedure loop through each row and send the mail, when sent was complete I deleted the row.
/Henrik
Here is a link that can point you in the right way:
http://classicasp.aspfaq.com/email/h...ql-server.html
Here is some sample code I threw together in a hurry. In this code first a data table with two columns called "Email" and "Category" is created. And 539 records are added to this data table. The first 200 records have their "Category" column set to "Cat1". The remaining 339 records have their Category column set to "Cat2". Now the Email field of the first record has a value of "[email protected]" and in the second record has a value of "[email protected]" and so on and so forth till the last record which has "[email protected]" in the Email field.
Then there is code that sends the emails. 100 records are sent in one email. In the first email that is sent the "to" address is set to the Email address in the Email field of the 0th record and the "Bcc" field set to all the email id's in the Email fields of the records from 1 to 99. In the second email that is sent the "to" address is set to the Email field of the 100th record and the "Bcc" field set to records from 101 to 199 and so on and so forth till all the records are sent.
Finally once you fully execute the code all you will see is the web page with a text box telling you that the number of mail message objects created was 6 and the total number of mails sent was also 6. But everyone would have received the emails.
Open a web application in VS 2005. It will create the Default page. Just substitute the code I give below for both Default.aspx and Default.aspx.cs and run the page.
HTML Code for Default.aspx
Code for Default.aspx.csCode:<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" TextMode="MultiLine" runat="server" Style="z-index: 100; left: 175px; position: absolute;
top: 64px" Height="82px" Width="356px"></asp:TextBox>
</div>
</form>
</body>
</html>
It is a very complicated piece of coding and it works. All email id's are dummies. So do not expect anyone to receive emails. You can just go ahead and try out the code. You may have to analyze the code and adapt it to suit your specific purpose:) .Code:using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Mail;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable("EmailTable");
dt.Columns.Add("Email", System.Type.GetType("System.String"));
dt.Columns.Add("Category", System.Type.GetType("System.String"));
string domain = "@mydomain.com";
for(int i= 0; i < 539; i++)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
if (i < 200)
{
dr["Email"] = "sample" + i.ToString() + domain;
dr["Category"] = "Cat1";
}
else
{
dr["Email"] = "sample" + i.ToString() + domain;
dr["Category"] = "Cat2";
}
}
string cat = dt.Rows[0]["Category"].ToString();
string q = "";
int pt = 0;
int ncreated = 0;
int nsent = 0;
bool sentlast = false;
string bccval = "";
MailMessage msg = null;
while(pt < dt.Rows.Count)
{
if (cat != dt.Rows[pt]["Category"].ToString())
cat = dt.Rows[pt]["Category"].ToString();
DataRow[] drlist = dt.Select("Category='" + cat + "'");
int numemails = drlist.Length;
int j = 0;
while (j < numemails)
{
if((j % 100) == 0)
{
if (msg != null)
{
sentlast = true;
msg.From = "[email protected]";
msg.Bcc = bccval;
SmtpMail.Send(msg);
nsent++;
bccval = "";
}
msg = new MailMessage();
msg.Body = "This is fine";
ncreated++;
}
if (j % 100 == 0)
msg.To = drlist[j]["Email"].ToString();
else
bccval += drlist[j]["Email"].ToString() + ",";
j++;
pt++;
sentlast = false;
}
}
if (!sentlast)
{
sentlast = true;
msg.From = "[email protected]";
msg.Bcc = bccval;
SmtpMail.Send(msg);
nsent++;
}
q += "Number of mail message objects created = " + ncreated.ToString() + Environment.NewLine;
q += "Number of mail message objects sent = " + nsent.ToString() + Environment.NewLine;
TextBox1.Text = q;
}
}
Hope this helps.
Warm Regards,
sr_jay
Quote:
Originally Posted by selanec
Thank you sr_jay, i will test your code shortly and let you know if ti suits my needs. Actaully i am more interested in sending emails from the sql server directly. MrNorth's idea sounds very good.
I CHECKED the link you posted but it is too general ... so i was wondering if you can post some of your code here. or pm me with the code.Quote:
Originally Posted by MrNorth
However notice that my main issue is that all of these emails are different.
That's why i want to group them somehow that i don't send 1000's diferent emails as all emails from one group should receive the same email.\\
Thanks
See if this helps:
vb.net Code:
Looks good but, as soon as i set the maxLength to 10 i get an System.OutOfMemoryException exception, on this line:
emailBatches.Add(emailGroup.Substring(0, idx + 1))
Ok let me explain what i need actually.
say we have these email addresses:
emailGroup = "[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; " & _
"[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]" & _
"[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]"
Means now we have:
11 - 123.com
5 - 789.com
2 - 456.com
2 - 101.com
And say we set the maxLengthPerBatch to 2.
The result should be 6 batches for 123.com, 3 batches for 789.com and one batch for 456.com and 101.com.
Means if i iterate emailBatches generics list
I should get:Code:For Each batch As String In emailBatches
Response.Write(batch & "<br />")
Next
[email protected]; [email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected];
[email protected]; [email protected];
[email protected]; [email protected];
[email protected];
etc.
Thank you very much
Ohhhh... I seem to have misinterpreted your problem.Quote:
Originally Posted by selanec
Is this 100 email addresses or 100 characters in the BCC field?Quote:
... but the limit of the BCC field is 100 (ISP limitation).
means is the restriction provided by your ISP xx characters or xx email addresses per message?
That procedure was designed to split it on xx characters.
i.e. each batch has maxLengthPerBatch characters irrespective of the number of email addresses in it.
Also you must ensure that maxLengthPerBatch is bigger than the length of longest email address to avoid that System.OutOfMemoryException exception.
e.g. you are splitting on 10 characters, but [email protected] contains 15. So maxLengthPerBatch must be bigger than 15.
It is 100 email addresses. Can you please help a bit further? ThanksQuote:
Is this 100 email addresses or 100 characters in the BCC field?
this is a rough pseudo code of what i need:
SELECT email, url FROM temptable GROUP BY url
' set the maximum allowed emails for a single group e.g. 100 (the most confusing group).
' then just iterate the groups:
For Each Group As List(Of String) In Groups
For Each email As String In Group
SendEmail(email)
Next
Next
Seems simple actually but, it is not. Thanks
ok.. here's the modified procedure.Quote:
Originally Posted by selanec
Now maxLengthPerBatch is Number of email addresses in each batch, instead of number of characters.
vb.net Code:
hmm looks very close but it yet doesn't return what i need.
e.g. if i set the maxLengthPerBatch to 2 it will return them in groups but without to be grouped properly.
Maybe i should sort the list before the function returns the generic list?
But i don't know how to set the comparer to sorts the items per domain.
@123.com ... maybe i can use split function and do something after?
Any ideas? this is really close .. well i hoped that i will make this on the database layer but anyway i am fine with this approach. thanks in advance
Are you using VS2005 or VS2008?
I am using both. i mean i have installed FW 3.5, and have VS 2k5 on this and VS 2k8 on another machine. Thanks
Here it is, using LINQ:
vb.net Code:
Pradeep :)
ok... here's a better implementation. It doesn't need a static variable.
vb Code:
Omg this post has gone in a totally different direction to what I originally planned to ask. Please let me explain it again.
i have a table. one column is email addresses. the other is url's
the email addresses columns is about 5.000 rows, of course all with unique email addresses
the url column has maybe 800 unique urls, so many rows will be the same
Now what i need is to create sort of temp table within the SP with also 2 fields.... url and a field named bcc.
The BCC field will contain all the email addresses from the other table that have the same url
So for example: if url http://www.something.com has 300 rows or email addresses, in the new table it would be one row with a field that has the 300 email addresses in them comma delimited.
After that i only want a max of 100 emails per record. so if http://www.something.com has 300 emails associated with it, the end table would only have 3 records for http://www.something.com. This is a ISP limitation for the BCC field. odd i know :(
My goal is to improve this process so that instead of sending 5000 individual emails i send 800 using BCC field.
Thank you and sorry for the confusion :)
Did you try my last code example. It seems to be doing exactly what you need.
If you already have all email addresses in a database column, you need not necessarily make it a comma seperaed string. Instead you can just modify only one line and use that database column/list/arraylist or collection etc.
Just replace:
withCode:Dim emailBatches = From email In emailGroup.Split(emailSeperator.ToCharArray) _
and it should start working with your list. (assuming it is named emailGroup)Code:Dim emailBatches = From email In emailGroup _
Hi,
I have got a solution here for you that in principle does what you want. To demonstrate it I have created a script file. I am giving the code below.
I can tell you what this script does. It first creates a table called emailtable with 2 fields email and url of varchar(500). It uses a create table statement for that. Next it inserts records into this table. It inserts 26 records into this table with distinct values for the email field starting from the first letter of the alphabet 'a' to the last letter of the alphabet 'z'. In the url it inserts values like 'a1', 'a2', 'a3', 'a4', and 'a5'. These are the only allowed values.Code:CREATE TABLE [emailtable] (
[email] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[url] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert into emailtable(email, url) Values('a', 'a1');
Insert into emailtable(email, url) Values('b', 'a1');
Insert into emailtable(email, url) Values('c', 'a1');
Insert into emailtable(email, url) Values('d', 'a1');
Insert into emailtable(email, url) Values('e', 'a1');
Insert into emailtable(email, url) Values('f', 'a1');
Insert into emailtable(email, url) Values('g', 'a2');
Insert into emailtable(email, url) Values('h', 'a3');
Insert into emailtable(email, url) Values('i', 'a4');
Insert into emailtable(email, url) Values('j', 'a1');
Insert into emailtable(email, url) Values('k', 'a2');
Insert into emailtable(email, url) Values('l', 'a3');
Insert into emailtable(email, url) Values('m', 'a2');
Insert into emailtable(email, url) Values('n', 'a2');
Insert into emailtable(email, url) Values('o', 'a3');
Insert into emailtable(email, url) Values('p', 'a4');
Insert into emailtable(email, url) Values('q', 'a4');
Insert into emailtable(email, url) Values('r', 'a5');
Insert into emailtable(email, url) Values('s', 'a4');
Insert into emailtable(email, url) Values('t', 'a3');
Insert into emailtable(email, url) Values('u', 'a5');
Insert into emailtable(email, url) Values('v', 'a3');
Insert into emailtable(email, url) Values('w', 'a2');
Insert into emailtable(email, url) Values('x', 'a1');
Insert into emailtable(email, url) Values('y', 'a1');
Insert into emailtable(email, url) Values('z', 'a2');
Go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [segregate]
(
@cnt int
)
AS
select * into #temp from emailtable order by url
Declare mycursor Cursor for
Select * from #temp
Declare @url Varchar(500)
Declare @bcc Varchar(500)
Declare @email Varchar(500)
Declare @tempval varchar(500)
Declare @bcc1 Varchar(5000)
Declare @cnt1 int
Set @cnt1 = 0
Set @tempval = ''
CREATE TABLE #temp1 (
bcc varchar(5000) NULL,
url varchar(5000) NULL
);
open mycursor
Fetch NEXT FROM mycursor INTO @email, @url
Set @tempval = ''
While (@@FETCH_STATUS <> -1)
Begin
if(@url <> @tempval)
Begin
if(@tempval <> '')
begin
Set @bcc1 = SUBSTRING(@bcc, 0, Len(@bcc))
insert into #temp1(bcc, url) Values (@bcc1, @tempval)
end
Set @tempval = @url
Set @bcc = ''
Set @cnt1 = 0
End
if(@cnt1 >= @cnt)
Begin
Set @bcc1 = SUBSTRING(@bcc, 0, Len(@bcc))
insert into #temp1(bcc, url) Values (@bcc1, @tempval)
Set @bcc = ''
Set @cnt1 = 0
End
Set @bcc = @bcc + @email + ","
Set @cnt1 = @cnt1 + 1
Fetch NEXT FROM mycursor INTO @email, @url
END
Set @bcc1 = SUBSTRING(@bcc, 0, Len(@bcc))
insert into #temp1(bcc, url) Values (@bcc1, @tempval)
CLOSE mycursor
DEALLOCATE mycursor
Select * from #temp1
GO
Some of the records have the same value for the url field. But the email field is distinct in each record. That is for example the record with email field 'a' and the record with email field 'b' both could the same value 'a1' for the record. Then I have created a stored procedure called segregate.
This procedure takes a parameter called @cnt. It denotes the number of records you want together. If you give it a value of 2 it will pick 2 records with the same url value and create a comma delimited string of the email field. If you specify 3 it will use 3 records and so on and so forth. So if you run the script in sql sever and then execute the statement
Exec segregate 2
it will return you a table with a field called bcc and a field called url. The bcc field will have the email field values delimited strung up together but delimited by commas and since the parameter passed is 2 then it would have a maximum of 2 email field values delimited by a comma in each field and the url field will have the same url.
To demonstrate how the stored procedure works let us say your records in the emailtable are
'a','a1'
'b','a1'
'c','a1'
'd','a2'
'e','a3'
'f','a4'
'g','a4'
'h','a5'
'i','a5'
'j','a5'
'k','a5',
'l','a6'
Then
Exec segregate 2
would give you
'a,b' , 'a1'
'c' , 'a1'
'd' , 'a2'
'e' , 'a3'
'f,g' , 'a4'
'h,i' , 'a5'
'j,k' , 'a5'
'l' , 'a6'
I think this is what you wanted and it can be adapted to your requirements.
Hope this helps.
Warm Regards,
sr_jay
Quote:
Originally Posted by selanec
ok here's a better implementation that doesn't need temp tables or cursors or looping thru the records:
Usage:Code:create procedure GetEmailBatches(@MaxEmailsPerUrl as int, @EmailSeperator as char(2) = ',')
as
begin
;with mycte(email,url,rownum, gcount) as
(select e1.email, e1.url, ROW_NUMBER() over(order by e1.url) as rownum, e2.gcount as gcount from emailtable e1
inner join (select url, count(url) as gcount from emailtable group by url) e2 on e1.url = e2.url
)
select mycte.url, mycte.gcount,(mycte.rownum % mycte.gcount)/@MaxEmailsPerUrl as groupNumber,
(SELECT email + @EmailSeperator FROM mycte e3
where e3.url = mycte.url and (e3.rownum % e3.gcount)/@MaxEmailsPerUrl = (mycte.rownum % mycte.gcount)/@MaxEmailsPerUrl
FOR XML PATH('')) as emails
from mycte
group by mycte.url, mycte.gcount, (mycte.rownum % mycte.gcount)/@MaxEmailsPerUrl
order by mycte.url, groupNumber
end
EDIT: Since you are looking for a database solution, maybe you would get better response in case you post your question in the database fourm. This forum is specifically for questions related to ASP.NET. Just PM any moderator to move your thread to database forums.Code:--To get 10 emails per batch seperated by comma (default)
exec GetEmailBatches 10
--To get 10 emails per batch seperated by semicolon
exec GetEmailBatches 10, ';'
Pradeep :)
This is exactly what i want. i will test it a bit more and let you know if i need some further help. Thanks a lot :thumb:Quote:
Originally Posted by sr_jay
Pradeep1210, i will check your code shortly. However you both guys are brilliant. thank you so much !!