Link to home
Start Free TrialLog in
Avatar of remmett70
remmett70Flag for United States of America

asked on

Streamline Outlook email process. Sending, to list exported from Access

Looking to improve bulk email process.

My users have an Access DB that they query to create a list of qualifying customers which they export to a textfile, the file has one email address per line.  Next they import the list into a customer contracts folder in outlook.  After that they have an Email Form they use which has the body of the email formatted, Select and insert all the recipients in as Bcc.  Finally sending the message.

I am looking for ideas on how to make this less manual for them.  Either something in either Outlook 2010 that will do the query through some type of macro or vb.  A separate Powershell or vb script that would send the email using the .txt file as the address source.  I could even do a small program in VB or C# (2005).  And lastly, in Access itself (not familiar with it).

Less user intervention the better.
Avatar of Neil Russell
Neil Russell
Flag of United Kingdom of Great Britain and Northern Ireland image

Why not just do a mailmerge?
Avatar of Neil Russell
Neil Russell
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jeffrey Coachman
Can you post a sample of your database as it stands now?
There is still a lot that is unknown here...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

Avatar of remmett70


I can't post the database.

There isn't any issue to troubleshoot.  Everything works exactly the way it was designed to.  I am just looking for options of possible ways to eliminate some steps in their process.

Change the Access DB to create the email
Leave Access as is and create a script or program to email from the text file created today.
Automate Outlook more.
Did you read through all of the steps I listed...
I only asked for a sample database, not the actual database with your actual data.

Again, without seeing how the DB is designed, it is hard to see what can be streamlined.

For example, why the need to import to Outlook?
What's in the email that it cannot be sent form Access?
"Select and insert all the recipients in as Bcc. " ...I this something you are doing manually and want to automate?

I hate to just throw out random suggestions.
"Email" in MS Office can be a complex topic

Perhaps you could tell us the overall purpose of this system...?

The DB contains all kinds of customer information.  From names, address, ph #, email, to complete account information, account history and transaction information.  The primary functions all work.

At some point in the past, they wanted a way to generate these lists.  the lists could be every customer who provided an email address, or those who exceed a certain amount of activity.  What criteria they choose.

I just can't see that there isn't a better way to do the job than they are using.  If they can push a button and generate a list, why not push that button and send the message without the manual steps.
Again, without a sample of the DB I am at a loss, I really don't want to make shots in the dark.
"if they can push a button and generate a list,"
That's my point, ...I have no idea what your source data looks like, or what this "text file" looks like...

There are just to many variables here that a sample db would answer a lot faster than me asking you 12 questions over the course of days.

You can output a query to a a text file by using code like this:
DoCmd.OutputTo acOutputQuery, "YourQuery", acFormatTXT, "C:\YourFile.txt"
...but here, I have no idea what "type" of text file you need.

You can also use:
DoCmd.TransferText acImportDelim, "YourExportSpec", "YourQuery", "C:\YourFile.txt"

But here again, The type of textfile is a mystery and also, you need an export specification... do you know how to create one?

You can send an email via outlook with code like this:
DoCmd.SendObject acSendNoObject, , , [RecipientEmail], , , [EmailSumbect], [EmailBodyText]

So perhaps that is what you are looking for...
So investigate thoes for a bit...

Other unanswered questions...

Is there other data in the file that need to be in the email?
What version of Outlook?
What about the outlook security pop-up?
Need attachments?
Need formatting in the email?
View email, or send immediately?
Seen one email at a time, or bulk?
What is your skill level with VBA?
..........And about 12 other questions that a sample would easily answer.
Again, "Sending Emails", and "making Lists" sounds simple, but there are a lot of factors to consider.

Again, did you read the steps I listed?
Yes or No?
I don't need your actual data, just a sample db that replicates the functionality...

I only can dedicate a certain amount of time to each question, let me know....

You are making this question more difficult than needed.  I am just looking for a list of options on different possible ways to accomplish the same task so I can look at them and determine the best way for my users and myself.  Whether within Access or outside within Outlook or some other scripting or programming.

As I stated originally, the textfile being created is just a simple list, one address per line file.  The addresses are imported into a contact list and used to populate an Outlook formatted Form.  Bcc is used for some level of privacy.  Looping and sending individual messages would be fine.

Outlook woud be 2010, we have Access 2007 and 2010, Could use VB or C# (VS2005).  Email with exchange currently 2003 but plan on upgrading in the near future.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

And just to be clear, I am not trying to frustrate you or make things more difficult than needed.

I see that you are a long time member here with plenty of solid interactions here.
You can also see that I am a longtime expert with the same.

It's just that with over 8 years of answering questions, we are always a bit weary of questions that state:
    "Help me make this better"
Again, lots of specifics are needed to really answer a question like this.
This is why posting sample databases has become commonplace in this zone.