Sending emails via outlook (1 Viewer)

Hi folks, hopefully you may have some ideas.
I would like to be able to send emails from access via outlook - thats the easy bit and I am able to let the user select the query (with the email address in) and set up the subject, body and attach documents. The difficulty that i'm having is that there are between 300 and 800 email recipients and so the emails are getting bounced back undelivered due to spam filtering. I want to be able to split the recipients into small batches of say 6-8 so that I will have a lot of emails ready to send that I can send though out the day/week. I'll include the code that i have to send the email in one large batch.

I have 2 methods of sending and am not sure how to proceed.
method 1
Private Sub btnSendEmail_Click()
On Error GoTo Err_btnSendEmail_Click
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

' this is for the address loop
Dim stDocName As String

Dim strSubject As String
Dim strYourEmailAddress As String
Dim strEmail As String
Dim rsSource As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rsSource = [Forms]![frmSendEmail]![cboSelectQuery].Value
rs.Open rsSource, cn
With rs
Do While Not .EOF

strEmail = strEmail & .Fields("Email") & ";"
.MoveNext
Loop
.Close
End With

strSubject = [Forms]![frmSendEmail]![txtSubject].Value
strYourEmailAddress = [Forms]![frmSendEmail]![txtYourEmailAddress].Value

DoCmd.SendObject acSendNoObject, , , strYourEmailAddress, , strEmail, strSubject, , True
Exit_btnSendEmail_Click:
Exit Sub

Err_btnSendEmail_Click:
MsgBox Err.Description
Resume Exit_btnSendEmail_Click
End Sub

method 2

Private Sub btnSendEmailAutomation_Click()
'open Outlook, attach zip folder or file, send e-mail
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim strYourEmailAddress As String 'address used in the TO of email
Dim strEmail As String 'address used in the BCC of email
Dim strAttachment As String
Dim strSubject As String
Dim strBody As String
Dim rsSource As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rsSource = [Forms]![frmSendEmail]![cboSelectQuery].Value ' to find the query selected from the combo box on the form
rs.Open rsSource, cn ' opens the query - list of email addresses
With rs
Do While Not .EOF 'loop through the whole list of addresses and adds them to the variable strEmail
strEmail = strEmail & .Fields("Email") & ";"
.MoveNext
Loop
.Close
End With

strAttachment = [Forms]![frmSendEmail]![txtAttachment].Value 'pulls the text from the "subject" text box on the form
strSubject = [Forms]![frmSendEmail]![txtSubject].Value 'pulls the text from the "subject" text box on the form
strYourEmailAddress = [Forms]![frmSendEmail]![txtYourEmailAddress].Value 'pulls the text from the "Your Email Address" text box on the form

Set appOutLook = CreateObject("Outlook.Application") 'opens MS Outlook
Set MailOutLook = appOutLook.CreateItem(olMailItem) 'Creates new email
With MailOutLook 'specifies information for each part of the email
.BodyFormat = olFormatRichText
.To = "myemailaddress@hotmail.com"
''.cc = "" 'CC not used in mailshot as this would let others see the addresses
.BCC = strEmail
.Subject = strSubject
.HTMLBody = strBody
.Attachments.Add (strAttachment) 'Finds and attaches the pdf document.
''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.Display
End With
End Sub

I have not used forums before but would really appreciate any help to split up the data set (email address's) into many smaller emails.

I have do do all of this from access and not use the mail merge option.

thanks to anyone who takes the time to read this.

RJ

Offhand, declare an Integer variable and start it at 1. Inside your loop, something like:


That's untested, but I think conceptually correct. You'd need to place things appropriate to the logic.

Thank you very much for the help, with that i'm almost there!

after a bit of messing about I got it to work so that it creates an email with only 7 bcc recipients I then have to save and close or send the email before the next email can be created.

I would ideally like to create all of the emails (would be between 7 and 100) and then queue them to send at regular intervals like every 20mins.

so now I think i'll look for the code to save and close each email - this should save it to the drafts folder of outlook automatically so that I have a load ready to send and then i'll find code that will send 1 email from the contents of the drafts folder and then wait 20mins before sending the next.

I'll update you on my progress

RJ

What's the code look like now? I normally use automation (your second method) and I can get multiple emails created and waiting for user action with .Display. I've never tried to save them unsent; let me know if you can't find it and I'll poke around.