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

remmett70
remmett70 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Neil RussellTechnical Development Lead

Commented:
Why not just do a mailmerge?
Technical Development Lead
Commented:
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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, ...post 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.

JeffCoachman
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

remmett70Network Manager

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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...?

JeffCoachman
remmett70Network Manager

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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?
.ett
...etc
.....ett
..........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, ...so let me know....


JeffCoachman
remmett70Network Manager

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
To just loop and send individual messages from Access, use code similar to this:

'----------------------------------------------------------------------------------
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourTable")

rst.MoveFirst
  Do While Not rst.EOF
    DoCmd.SendObject acSendNoObject, , , rst!yourEmailField, , , "Email Subject Text", "Email message body Text", False
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing
'----------------------------------------------------------------------------------

The code snippets I posted on creating textfiles should be useful as well

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
remmett70,

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.

;-)

JeffCoachman

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial