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

x
?
Solved

Word 2007 - mail merge

Posted on 2013-06-13
8
Medium Priority
?
506 Views
Last Modified: 2013-06-20
My OS is win 7 prof 64 bit and I use office 2007.
i use Word's mail merge feature to email thru Outlook announcements and since it is a small amount of about 150 emails, thus my decision not to use vendors like iContacts, etc.
My first attempt hit a relay problem when the emails were sitting in my Outlook's Outbox not being sent even after 1 day.
To merge the email data, i run a simple query to extract the email field from the Access database.
To overcome this relay problem I am thinking of putting a sleep command, so that after sending each email, there will be a pause of say like 30 seconds before sending out the next email.  
If this can be done, can the Experts please advise, where and how should I insert the code.  Should i put it in the Word's merge document, or in the query in Access  or in Outlook.  
The code that I have which I am not sure whether will work or not is as follows :
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

I will then call it with the syntax Sleep 20
Hope the experts can please  suggest a solution. 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
  • 3
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39247038
If you are using the built-in mail-merge then I don't see how it is possible to do what you ask.  The process is invisible. There is nowhere to add your own code.
0
 

Author Comment

by:jegajothy
ID: 39247916
in response to peter57r, is it possible to put at the query level at Access 2007/
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39248001
If the query calls a function which includes a sleep process then you would get a delayed result, but I would expect the query result to be delayed totally and so nothing released to the mailmerge until the query was complete..  I can't be sure of this, so it might be worth a try.

If you build the sleep function into a standard module and ask for a parameter - which does not have to be used in the function- then you could call the function in the query using something like...
mywait:mysleepfunction(recordID)

you must use a field from the current record as a parameter otherwise Access will not call the function for each record, but just once before any records are processed.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jegajothy
ID: 39248217
in response to peter57r, i have the following function in my module in Access,
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

What is the syntax to use to pass my database field, named ; Email
so that it will sleep for 20 seconds after processing each record.

My SQl statement for my query is ;
SELECT [Email] & " " & ";" AS E
FROM [Seniors Club]
WHERE ((([Email] & " " & ";") Is Not Null));

thank u.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 39248391
Your code does not satisfy the rules I set out in my reply.
The function is not referring to anything in the current record, so it will not be processed for each record.  Access will see that it is not dependent on anything and just run the function once at the beginning of the query.

You need to add a parameter...
Public Function Sleep(lngSeconds As Long, dummy as variant) As Boolean
  If lngSeconds > 0 Then
    Call APISleep(lngSeconds * 1000)

    Sleep = True
  End If

End Function

Then you call it in your query...

SELECT [Email] & " " & ";" AS E, sleep(5,Email) as mydummy
FROM [Seniors Club]
WHERE ((([Email] & " " & ";") Is Not Null));


As I have said , I don't know if this will help at all.  I don't know why a valid mailmerge would have the problem you have described.
0
 

Author Comment

by:jegajothy
ID: 39251825
In response to peterS7r, i went to an existing  module and placed your function there, and then in the SQl of the query the syntax u gave.
but when i ran it I got the error undefined sleep function in expression.
Hope u do not mind giving me the steps where to stick this function.
I hit Create, then on Module, and i selected module1, and placed the code there and saved it and then went to the query and inserted the SQL statement.
Looks like I am doing something wrong as it does not seem to see the function.
0
 

Author Comment

by:jegajothy
ID: 39251859
In my above response, my apologies, I made a type in the word "Sleep' that is why i got that error.
But after correcting it, the error is now :
Wrong number of arguments used with function in query expression 'sleep(5,Email)'

and this is the SQl statement that I used :

SELECT [Email] & " " & ";" AS E, sleep(5,[Email]) AS mydummy
FROM [Seniors Club]
WHERE ((([Email] & " " & ";") Is Not Null));

Hope some sharp eyed Guru can spot the boo boo that i had made.
Thank u.
0
 

Author Closing Comment

by:jegajothy
ID: 39264695
thank u
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

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