Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sending emails with access

Posted on 2005-05-02
6
Medium Priority
?
249 Views
Last Modified: 2010-05-18
Below is the code I am currently using, the problem I am running into is when I send the email I am receiving the error "To Many recipients". Is there a way to change my below code to stop and create 2 emails when the number of recipients is over a certain number? Or is there something with outlook that can change this....thanks in advance

Private Sub All_Drip_E_Mail_Click()

'******begin code******
Dim email, cc, subject, bcc As String
Dim objOutLook
Set objOutLook = CreateObject("OutLook.Application")

'**gathers information from your form.  this sets the string variable to your fields
email = Me!email
cc = Me!cc
subject = Me!subject
bcc = " "

Set Db = CurrentDb

Set rs = Db.OpenRecordset("All Drip E-mail (LOCK)", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
          If rs!emailaddress = "" Then
            If bcc = "" Then
                bcc = rs!emailaddress
            Else
                bcc = rs!emailaddress & "; " & bcc
            End If
        Else
            bcc = rs!emailaddress & "; " & bcc
        End If
   
    rs.MoveNext
Loop


'***creates an instance of Outlook
Set objOutLook = CreateObject("Outlook.application")
Set objEmail = objOutLook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
    .To = email
    .bcc = bcc
    .subject = subject
    .Display
   
End With


Exit Sub



End Sub
0
Comment
Question by:m7johnson
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:phileoca
ID: 13913373
you could add a counter in there, and once the counter reaches a certain number it pauses, sends the mail, then starts again from where you left off.

to do this, you should put the end of your function in it's own function and call it.

public Sub You_Got_Mail
'***creates an instance of Outlook
Set objOutLook = CreateObject("Outlook.application")
Set objEmail = objOutLook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
    .To = email
    .bcc = bcc
    .subject = subject
    .Display
   
End With

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Then the rest of your code. note my additions


Private Sub All_Drip_E_Mail_Click()

'******begin code******
Dim email, cc, subject, bcc As String
Dim objOutLook
Set objOutLook = CreateObject("OutLook.Application")
'my addition
Dim locCounter as Long
locCounter = 1

'**gathers information from your form.  this sets the string variable to your fields
email = Me!email
cc = Me!cc
subject = Me!subject
bcc = " "

Set Db = CurrentDb

Set rs = Db.OpenRecordset("All Drip E-mail (LOCK)", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
          If rs!emailaddress = "" Then
            If bcc = "" Then
                bcc = rs!emailaddress
            Else
                bcc = rs!emailaddress & "; " & bcc
            End If
        Else
            bcc = rs!emailaddress & "; " & bcc
        End If

        'Set send mail at X. This example X = 10
         If locCounter = 10 Then
              Call You_Got_mail
              'reset counter back to 0 and continue adding emails.
              locCounter = 0
         End If


   loccounter = locounter + 1
    rs.MoveNext
Loop



0
 
LVL 11

Expert Comment

by:phileoca
ID: 13913436
ooh one thing you'll need to do is after you exit your outside loop.

call the function again to send the rest of the mail, (say you had 11 people), you'll have 1 left, so call the function again after the loop and you should be good.
0
 

Author Comment

by:m7johnson
ID: 13940452
For some reason I am not getting loccounter to increase in value...any ideas?
0
 

Author Comment

by:m7johnson
ID: 13940494
ok I changed the lccounter name to just counter and now I can get the value to increase...however when the function runs, it seems to open multiple emails with no value in the bcc field......??
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14460716
PAQed with points refunded (350)

modulo
Community Support Moderator
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question