Solved

Access 2007

Posted on 2013-06-09
10
328 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now