UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> I Need Design Advice - Outlook, Word, Or Access?, Office 2010    
 
   
MargaretBartley
post Sep 4 2017, 04:58 PM
Post#1



Posts: 134
Joined: 4-July 05



I have a newsletter with a bit less than 1200 subscribers.
I've been writing the newsletter in Word using Mailmerge with the Access mailing list. Easy-peasy.

We've been using a private mail server, but that person is moving, and we're going to have to either buy the services of a commercial mailer (MailChimp, ConstantContact, etc) or use my ISP.

I've tried the commercial mailing solution, and I hate it. Browser-based newsletter creation is extremely slow and frustrating and trying to maintain the database on their server along with my own Access list is a nightmare.

I'm comfortable with VBA and Office Automation, but I need to find code samples, I don't have all the properties and methods memorized. Also, I've never tried to automate MailMerge, but I'm assuming it's fairly straight-forward.

My ISP limits me to 200 emails per half hour. Which means I need to hit the Send key every half hour for 3 hours. I talked to my ISP, and they said it would be OK (their algorithm would not block me) if I created six email accounts, and ran 199 emails through each of them. It's simple enough for me to build six MailMergeTables instead of one master one.

So I'm trying to figure out the best approach. I'm using Outlook in internet mode - I connect through my ISP, not through Exchange.

One problem is that when the emails are being sent, in Outlook, I can't do anything else in the user interface. I would not be able to change the default sender until all the emails from the first sender had been sent.

I'm thinking the most simple solution is to write something in Outlook that
*opens the Word document
*sets the MailMerge DNS data connection to the correct Access table
*changes the default Outlook sender
*intiates MailMerge Send action.

I'm not sure if this would be a synchronous activity - do I have to wait for all the first documents to be sent to Outlook before changing the data link?
Do I have to wait until all the first 199 messages are sent before changing the default sender?

Or maybe forget about changing the default Sender, put in a timer, and send the next batch 40 minutes later?

I'm thinking about doing this manually, and recording a macro, but sending emails from the user interface is different from sending them through code, and I'm not sure that I could replicate the process manually.

Any advice?

Thank you.
Go to the top of the page
 
GroverParkGeorge
post Sep 4 2017, 05:10 PM
Post#2


UA Admin
Posts: 31,209
Joined: 20-June 02
From: Newcastle, WA


Hi Margaret.

Have you considered the FMS Emailer Program? It's not inexpensive, I know, but I'm pretty sure it would handle your requirements.

--------------------
Go to the top of the page
 
MargaretBartley
post Sep 4 2017, 11:38 PM
Post#3



Posts: 134
Joined: 4-July 05



If I have to go to a third party product, so be it, but I'd like to explore doing it myself, first.
I don't see that the FMS product will get around the issue of only being able to send 200 emails per sender account per half hour, which is the main issue.
Go to the top of the page
 
MargaretBartley
post Sep 4 2017, 11:42 PM
Post#4



Posts: 134
Joined: 4-July 05



I looked at SamLogic Multimailer, and it looks like the stuff I've already tried, and hated it. I like the control Word gives me, and I hate building newsletters in my browser. And I certainly don't want a third party handling my mailing list, and my subscribe and unsubscribe. I already have a member database for other purposes, and I only want to have to update email and subscription data in one place - in the Access database on my laptop.

Maybe I'm misunderstanding the description....
Go to the top of the page
 
jleach
post Sep 5 2017, 02:49 AM
Post#5


UtterAccess Editor
Posts: 9,808
Joined: 7-December 09
From: Staten Island, NY, USA


How about an SMTP provider that doesn't limit you like your ISP?

SendGrid, for example, can push out hundreds (thousands, hundreds of thousands) of emails in bulk, fully automated, no UI garbage to wade through, no Outlook.

We have a client that sends 200-800 emails daily (all at once) and has been for years. We fully build and send the emails in Access

We used the CDO library for that one, but these days there's probably a rest API you could submit to as well - CheekyBuddah has a good CDO example here at the Code Archive, which includes support for attachments. Rest APIs are usually easy enough once you get past authentication, except maybe for attachments (upload files to a web service is a bit tricky), so if you're wanting to send Word documents, you can build them from data in your Access db and take one route or the other, I'd guess.

There's other bulk SMTP providers besides SendGrid, but that's the only one that comes to mind at the moment (and for the few others I've used, SendGrid has done better).

hth

--------------------
Go to the top of the page
 
MargaretBartley
post Sep 5 2017, 04:11 PM
Post#6



Posts: 134
Joined: 4-July 05



QUOTE
You could look at a trial version & see whether you still hate it!

That's a good idea.
Go to the top of the page
 
MargaretBartley
post Sep 5 2017, 04:24 PM
Post#7



Posts: 134
Joined: 4-July 05



QUOTE
SendGrid, for example, can push out hundreds (thousands, hundreds of thousands) of emails in bulk, fully automated, no UI garbage to wade through, no Outlook.


So, how does that work with Word? As it is now, each newsletter is personalized with a message stating current membership status (please renew, thanks for supporting, membership to expire soon, etc), with the sent-to email address at the bottom, so if they email back to unsubscribe, I know what address the newsletter was mailed to, which is not always the address the unsubscribe message is sent from.

All that is in the Word document that is being sent as an email. The email address table is generated each time the newsletter is sent. It looks like I'd have to delete the address on SendGrid, and then resend the table of addresses every time, if I want to keep that personal message functionality? Would that kind of wipe out the analytics benefit (which I don't get now, but would really like!).
Go to the top of the page
 
JHolm
post Sep 5 2017, 05:11 PM
Post#8



Posts: 73
Joined: 7-July 15
From: BC Canada


If your ISP says you won't get blocked if you can use 6 email addresses to send 199 emails each, you can try using CDO and just skip Outlook entirely. I've never automated mail merge from Access, but if you can figure that out, you could try sending the email using CDO (lot's of code samples out there). If you have the 6 emails addresses you're sending from in a table you could just change the From address every 199th iteration of the loop you're using to send the emails.

Cheers,

Jeff
Go to the top of the page
 
jleach
post Sep 6 2017, 03:17 AM
Post#9


UtterAccess Editor
Posts: 9,808
Joined: 7-December 09
From: Staten Island, NY, USA


Sorry, I see you're using Mail Merge. I'm not sure what that process is myself (I've never done it before), but if you can configure the SMTP server for SendGrid (which I would imagine you can do... what does MailMerge use as a mechanism to send?), then you can configure the sendgrid SMPT server credentials there.

---------- Original reply below ------------



With SendGrid, you don't have to store any addresses with them. You don't have to do anything except send an email through their servers.

I'm not sure how you're doing it now, but presumably something like this:

CODE
Set msg = new Outlook.MailMessage
msg.Body = [whatever]
msg.Send


With SendGrid, you could replace that and use CDO and do something like this instead:

CODE
' prep my documents...
SendViaSendGrid("pathToWordDoc")
' ... do whatever after it's sent ....

(naturally, the code in the SendViaSendGrid would be set the smtp server address, username, password and other SMTP settings (same as setting up an account in Outlook, really), but you don't actually have to "integrate" with SendGrid at all beyond putting in their SMTP Server settings.

SendGrid's address database comes from your usage. If you send an email to joe@schmoe through their SMTP server, it's going to note that and you have it for use in their analytics. You don't have to maintain a database on their end, they pick it off the usage of your account.

The reason I thought SendGrid would be a good choice is that you shouldn't have to change anything of how you handled the original private email server method of doing things, except the part where you actually send the email (swap it out with SendGrid's credentials instead). All of the rest of the process should be able to stay the same.

(with that said, you haven't fully explained the working pieces to your process, so I'm assuming there's a fairly distinct "send email" part of it, or at least a "drop off to the private email server" part, so if that part of the code is not coupled to the rest of the process, you can swap it out and a call to send via sendgrid instead...)

hth

--------------------
Go to the top of the page
 
GroverParkGeorge
post Sep 6 2017, 06:57 AM
Post#10


UA Admin
Posts: 31,209
Joined: 20-June 02
From: Newcastle, WA


Margaret. I have automated sending email from Access using CDO via GMail. It's not hard, although not so easy.

If you'd like, you can contact me for a copy of it.

That wouldn't avoid your problem of "flooding" their server and possibly getting blacklisted, though. You would have to write the VBA to avoid that, as you've previously noted.

--------------------
Go to the top of the page
 
MargaretBartley
post Oct 26 2017, 01:47 PM
Post#11



Posts: 134
Joined: 4-July 05



Just as a follow-up. My ISP limits my outgoing emails to 200 in a half hour, but my upload speed is so slow, that it takes more than 30 minutes to send out 200 newsletters!

So, it turns out it wasn't a problem after all. That's pretty funny.
Go to the top of the page
 
GroverParkGeorge
post Oct 28 2017, 07:52 AM
Post#12


UA Admin
Posts: 31,209
Joined: 20-June 02
From: Newcastle, WA


I'm glad to hear it worked out okay.

Continued success with the project.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 10:17 AM