Email management project

[stevekos07]

Hello all. I have already received some help on this in the "forms" forum, but I am looking for some more comprehensive answers for this one. I have been asked to develop an email management system in the database I manage. We currently use Outlook and Word merges for email management but we would prefer to have this managed more simply from Access.

I can of course email individual people easily from hyperlinks in the contact form, but I would like to also develop an email system that can be flexible enough to:

• email selected contacts from a "checked list".
• email all contacts with various attachments etc.

I don't expect anyone here to do the work for me, but is there a source of good information on how to develop a good email management system in Access? I am using both 2010 and 2016.

 

[NauticalGent]

Try these for starters:

http://mobile.databasejournal.com/features/msaccess/article.php/3822536/From-Access-to-Outlook.htm

http://mobile.databasejournal.com/f.../3827996/Working-With-Outlook-from-Access.htm

And finally:
http://mobile.databasejournal.com/f...91/Bulk-Batch-Email-From-Microsoft-Access.htm

I personally do not use any of these, my needs were much simpler, however I thought these utilities would be worth holding onto for future reference.

Best of luck!

 

[The_Doc_Man]

You can look up Outlook Application Objects but your best bet is to look up the VBA stuff from the context of Outlook. Then you can translate what it tells you to Access context easily enough by using an App Object.

Be warned that the Component Object Model's structural diagram is complex beyond all reason. I wanted to gag the first time I saw it, but patience and reading the documentation a couple of times brought me to the point that I could see what was going on. After that, the "hot mess" sort of cooled down again.

When I did mail-outs, I had to either open Outlook as a new App object or link to an existing Outlook app (i.e.already running) because Outlook does not like it if you have two copies of Outlook.EXE running at the same time. The second one always loses and you get nasty error traps.

Then it is a matter of navigating through the object model morass that is Outlook. However, all the resources I needed were available from the web. You have to connect to the MAPI space (message API) in order to manipulate messages. It is analogous to the Access Workspace but is an Outlook concept. Just print out the COM diagram and have it handy because it will tell you the names of what you want to manipulate via your Outlook App object.

You have to create various components of a message by first selecting the Messages collection and adding (creating) an empty message. Then, in context of that message, create the body, To, CC, Subject, and BCC lists as separate strings. You can add attachments. You can do things like digitally sign it or encrypt it based on some option settings. Eventually, you build the message by copying the strings to the appropriate message properties and then you send the message (msg-object.Send, of course...).

There used to be a wrinkle that after a successful send, you STILL sometimes got an error trap for error 285 or 287, I forget which offhand. But after days of research, I found that nobody knew WHY it did what it did, but that the error was one you could safely dismiss without further action. The message went out normally with the correct properties despite the error. Some authorities conjectured that it was an error trapped when Outlook was cleaning up after itself because something wasn't mapped correctly.

You will find that some of the nomenclature is a bit odd. Outlook doesn't show you the message in a window. It is in an Inspector. (Go figure...). You CAN look up things in the address book, but if you have e-mail addresses in Access, you don't need the address book for doodlum-squat.

So... if you have a list of addresses and a way to select intended recipients, you just create an empty string for To, CC, BCC, and then append the e-mail address and a semi-colon for each such recipient in the appropriate string. Then when done, load these to the .To, .CC, .BCC properties of the message. Load up .Subject as well.

I did that by stepping through a recordset of possible recipients and testing each one for inclusion or exclusion from whatever list was appropriate. If you have to keep track of the recipients for a given message, this method lets you add records at the time of sending so that you don't have to trust the ability to read back what you sent.

That ought to give you an idea of using Outlook to send messages to controlled address lists via Access automation. Trust me, this code will need some patience - but once you get it working right, DAMN it will feel good to succeed.

 

[Lightwave]

Regards grouping emails together here's some boiler plate code that largely does what Doc Man explains. You could set it up to pull whatever emails you want - one practical way to do this is to have tick boxes in a list and allow the user to select / unselect items. Then put the query into the below code as the source of the recordset - rsemail. Afraid I haven't ever done anything with attachments but there must be boiler plate code on the web for that. Below I was calling a query that sorted it in alphabetical order - that line of comment could be altered according to how you set up the query source.

The below code creates the email string and puts it into a variable called sendBCC - It was designed so that a standard form would appear on the screen and in the form there would be an onload event took that string and placed it in a field. You can pass that string directly to Outlook if you want but the principle is the same.

PublicFunction CreateGroupEmail()
On Error GoTo Err_CreateGroupEmail

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Please note all available e-mails are placed in BCC section of a new form in alphabetical person name order. If a person doesn't have a listed e-mail address he/she will be omitted", , "APPLICATIONNAME"

Dim MyDB As DAO.Recordset
Dim rsEmail as DAO.Recordset
Dim SendBCC as String

Set MyDB = OpenDatabase("\\SERVERNAME\DIRECTORYPATH\" & "TARGET.MDB")
Set rsEmail = MyDB.OpenRecordset("SELECT STATEMENT HERE")

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(![Email]) = False Then
SendBcc = SendBcc & rsEmail![Email] & ";"
MessageSubject = ""
End If
.MoveNext
Loop
End With

stDocName = "FORMTOOPEN"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Set MyDB = Nothing
Set rsEmail = Nothing
SendBcc = ""

Exit_CreateGroupEmail:
Exit Sub

Err_CreateGroupEmail:

If Err.Number = 2501 Then
MsgBox "The e-mail was cancelled without sending", , "APPLICATIONNAME"
Exit Sub

If Err.Number = 3734 Then
MsgBox "There are no Records Cancelling", , "APPLICATIONNAME"
Exit Sub

Else

MsgBox Err.Number

End If

End If

Resume Exit_CreateGroupEmail

End Function

 

[stevekos07]

Thanks everyone on this. At this stage the request is a 'would like to' rather than a 'must have', so I will avoid this for awhile. At the moment I am discussing what exactly their needs are so that we can work out a solution that is as simple and error-prone-free as possible.

I'm still climbing the learning-curve re coding for more complex procedures, so I'm not looking to get too bogged down with this one yet.

Most of the emails from the DB will be individual emails, which is straight forward, with the occasional mail campaign, which can be handled from within Word as an email merge. I have a few code samples to work with, so I'll keep studying up on this one, because it will come up as a deal-maker or breaker at some point. Cheers.

 

[HiTechCoach]

FMS has a product Total Access Emailer (Click Here)

I have not used it. I have used some of the FMS products and they are very good. AT least yu can take a peek and see what ios possible with Access.

My customer email a lot form my Access applications. I do offer Outlook automation but must people opt for the direct emailing though their SMTP server. Because of the volume, I now use a third-party control that handles the actual sending. I just pass it the parameters for the email. That allows me to concentrate on "what to send" not the "how to send". This also allows me to send emails with any email client installed,like outlook.