Multiple Emails from Access (1 Viewer)

[noboffinme]

Hi

I have a database that will send emails through Outlook (Office 2007) based on a queries results.

I want to limit the number of recipients to be added to each new email so it doesn't exceed the allowed number.

There could be 100s of recipients & I'd like to split them up.

Has anyone developed any code that can do this?

I've found that initiating the email with the below will open the first one;
-----------------------------------------------------
Set outapp = CreateObject("Outlook.Application")
outapp.Session.Logon
Set outmail = outapp.CreateItem(0)
Set cn = CurrentProject.Connection
-----------------------------------------------------

Next I use a loop to add the recipients but after about 70 recipients I want to open another e-mail to add the next 70 recipients & so on.

Any suggestions? Thanks

 

[pbaldy]

Increment a variable inside your loop. When it hits 70, send the email and create another one.

 

[noboffinme]

Thanks but I've done that, I want to know how to open a second e-mail on top of the first one & add the next 70 recipients.

I want to keep the emails open until the user looks at them & sends them on.

Currently as I use the below;

Set outmail = outapp.CreateItem(0)

This creates the first e-mail & adds the recipients, but how do I create the next & subsequent emails - can I use CreateItem(0) again to open a new email?

 

[noboffinme]

I discovered that I can use Set outmail = outapp.CreateItem(0)
multiple times to open new e-mails, thanks for your help.

 

[noboffinme]

Hi Again

Sorry, I should clarify my answer in case anyone wants to use this.

The below loop will create a string of email addresses with an ';' between each.
-----------------------------------------------------------------------
With rst
Do While Not .EOF

str_email = str_email & .Fields("org_generic_email_address") & ";"

.MoveNext
Loop
.Close
End With
-----------------------------------------------------------------------

I limit that loop to a number eg; 70 email addresses & then open Outlook as per below to paste the addresses in;

-----------------------------------------------------------------------

With outmail
.Display 'or use .Send
.To = str_email
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.body = "Hello"
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")

End With

str_email = Left(str_email, Len(str_email) - 1)
------------------------------------------------------

I then start another loop of emails & open a new Outlook email over & over till all are entered. hth

 

[darbid]

I would deal with your To string first, then deal with your emails.

Thus I would have an array you can check how to do these kinds of variables.

Then in your loop of recipients you add 70 to Array(0) - then 70 to Array(1) - then 70 to Array(2) etc

Then you take this array and create another loop based on the size or number (X) in the array.

Here you make an email > add to the TO = Array(0) then send - make an email > add to the TO = Array(1) etc.