Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007

Posted on 2013-06-09
10
Medium Priority
?
341 Views
Last Modified: 2013-06-11
My OS is win 7 prof 64 bit and I use Access 2007 and Outlook 2007.  My email account is MSN and my internet is Comcast.
Once a while I do an email broadcast to my club members of 150 members but many times a couple of the emails sit in the Outbox not being sent, and i am told that there is a relay limit of how many emails that can be sent.
My emails are stored in Access 2007, and i would like to enquire if there is an easy way to run a query that will create groups for Outlook 2007, each group to contain only 20 emails.
This is because the database gets updated periodically.  Thank u.
0
Comment
Question by:jegajothy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 23

Expert Comment

by:tailoreddigital
ID: 39233653
FYI,

Using MailChimp,  you can have a list of up to 2000 members and send 12000 emails a month for free,

http://mailchimp.com/

http://mailchimp.com/pricing/free/
0
 
LVL 21
ID: 39233682
If you are sending email to fast for your ISP then what I do is put a short pause between each email. This is also called "metering" or "throttling". Basically you slow down the sending rate.

Here is the code I use:
**Place in a standard code module
Option Compare Database
Option Explicit
Private Declare Sub APISleep Lib "kernel32" Alias "Sleep" (ByVal lngMilliseconds As Long)

Public Function Sleep(lngSeconds As Long) As Boolean
  If lngSeconds > 0 Then
    Call APISleep(lngSeconds * 1000)

    Sleep = True
  End If

End Function

Open in new window


Example Usage:
' Pause 20 seconds
Sleep 20

Open in new window


You will have to do some testing to see how long the "Sleep" needs to be between emails.
0
 

Author Comment

by:jegajothy
ID: 39233692
in response to HiTechCode, thank u, that was what I was looking for.
i am thinking of increasing the delay to say 50, would this put my pc to 'Sleep' mode when it sees no activity? Or what is the safe number to put, since I am the only user on my pc and I can leave it on the whole day without interruption.
 But please explain where do i put the code, i.e. in the Word mail merge document or where?  Thank u for the solution. With best wishes and regards.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 21
ID: 39233798
For this to work you would use a loop to read the records in the database and send one email at a time.   After the email is send you would "Sleep" before going to the next record and sending.

You will also want o go to the Power Options in hte Control Panel  and make sure your PC will not hibernate.

How are you sending the emails now?
0
 

Author Comment

by:jegajothy
ID: 39236573
In response to TheHiTechCoach, I am trying to switch to Word mail merge feature of the costs involved for using iContacts.  I am also researching MailChimp which is free, but looks a learning curve and it would take me a couple of days to get the hang of it.
Meanwhile, please let me know where i stick your code, is it in the Word docuemnt where the email is being merged, and also how to do it.  Thank u for your response.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 39239016
Meanwhile, please let me know where i stick your code

The code goes in Access.  As far as I know it will not work inside a Word mail merge process.  If you must use Word mail merge then you are back to writing Access code to select groups of records and performing multiple merges. You will still need to "Sleep"  between groups so that you are not sending to fast.  

I send lots of emails from Access.  I do not use Word mail merge to send email. I do it all from Access.  No Word required. This allows my to send email from a machine without Office Installed. Only the Access runtime is required.

The issue with sending from your PC with Access, Word/Outlook, or Outlook is that most ISPs and Web hosting services have a limit to how many email you can send per hour. This is to try to stop spammers. If you are sending out through your web site, check with your hosting provider to see if the limit is 150/hour. If it is, see if  they may increase it to 500 per hours and your problem is solved.

If you are hosting your own mail server, Like with  VPS or dedicated  server hosting, there is generally no limits.  That can get expensive That is why people go with Email Marketing services like Mailchinp, ConstantCotanct, etc.  They can be cheaper that doing it yourself. It is all based on your volume. In your case you should be able to do it all yourself.

An alternative to Mailchimp:  http://www.constantcontact.com
0
 

Author Comment

by:jegajothy
ID: 39239373
In response to TheHiTechCoach, thank u for your suggestion.  I have one more Q, and that is where I do stick the line Sleep = 20 in the code.  is it before sleep = True?  
The reason why I am using Word mail merge is of the learning curve whenever I try mail list vendors.  Also some have limitations like no attachments, but I will explore the others too.  Thank u for your suggestions.
0
 
LVL 21
ID: 39239472
The reason why I am using Word mail merge is of the learning curve whenever I try mail list vendors.
What I was attempting to point out is that it is possible to do it all with just Access 2007. No Word merge required.
0
 
LVL 21
ID: 39239482
I have one more Q, and that is where I do stick the line Sleep = 20 in the code.  is it before sleep = True?  

Place the following in a standard code module named modSleep

Option Compare Database
Option Explicit
Private Declare Sub APISleep Lib "kernel32" Alias "Sleep" (ByVal lngMilliseconds As Long)

Public Function Sleep(lngSeconds As Long) As Boolean
  If lngSeconds > 0 Then
    Call APISleep(lngSeconds * 1000)

    Sleep = True
  End If

End Function

Open in new window


To use the above function somewhere is your  Access code you add just a single line of code to call it.

Example:
Sleep 20

Open in new window

0
 

Author Closing Comment

by:jegajothy
ID: 39239637
Excellent response and well answered.  Thank u very much.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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