Avatar of remmett70
remmett70
Flag 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.
Microsoft AccessOutlookProgramming

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Neil Russell

Why not just do a mailmerge?
ASKER CERTIFIED SOLUTION
Neil Russell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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, ...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
remmett70

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

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
remmett70

ASKER
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 Coachman

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
remmett70

ASKER
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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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