Mail merge based on check boxes

**jbs** · 09-16-2012, 10:04 PM

Hi,

I have an access database that has 10 items that can be checked if a certain criterea is met. I need a way to generate an email that inserts a sentence relating to each checked box if that box is checked.

Apologies if this is in the wrong area!

Thanks.

**Rod** · 10-08-2012, 04:15 AM

OK, it's an Office Automation enquiry but you need to give us more facts about the email medium. I assume that your email client is Outlook. Is this where you want to generate the email? I ask because I associate the term 'Mail Merge' with Word and it is possible that you are composing the email body in Word.

From your description so far, it's all possible but can be somewhat complex.

A simpler solution, if acceptable, is to compose the text in Access and then use the SendObject action (form or report). Use the composed text as the message text. Make the form or report as simple as possible - or you could make your message the report.

**joelmeaders** · 10-08-2012, 09:31 AM

This should work assuming you are using Outlook. It isn't the prettiest code but it works and you also won't get that annoying "Wait XX seconds and click OK if you want this program to send an email" security alert. You will need to have the Microsoft Office 12 (or above) reference checked in Code Editor -> Tools -> References and probably the Microsoft Outlook XX reference as well.

Paste the following code into any module.
Call the code from a button on the form using:
Call SendEmailAlert()

You can make this a lot more advanced if you need. Because of how it is configured below the user won't even know an email is being sent.

Code:

Private Sub SendEmailAlert()
On Error GoTo EmailError

Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

OutMail.Recipients.Add "EMAIL@EMAIL.COM"
OutMail.Recipients.Add "Youcanaddalotofthese@email.com"

OutMail.Subject = "EMAIL SUBJECT HERE"

strBody = "<html><body> Automated Email Alert from ________ <br><br>"

If Screen.ActiveForm!chkBoxName1 = True Then strBody = strBody & "They checked this box <br>"
If Screen.ActiveForm!chkBoxName2 = True Then strBody = strBody & "They also check this box <br>"
If Screen.ActiveForm!chkBoxName3 = False Then strBody = strBody & "They DID NOT check this box <br>"
If Screen.ActiveForm!chkBoxName4 = True Then strBody = strBody & "The check box is true <br>"

strBody = strBody & "</body></html>"

OutMail.HTMLBody = strBody

OutMail.Send
Set OutMail = Nothing

Exit Sub

EmailError:
     'ERROR TRAPPING GOES HERE
End Sub

Notes:
<br> = line break in html.
You can use most HTML tags like <b></b> for bold

**Rod** · 10-08-2012, 08:12 PM

Hi Joel,

A couple of observations.

... and probably the Microsoft Outlook XX reference as well.

The Outlook reference definitely needs to be checked to make the Outlook objects available.

Your use of CreateObject to reference Outlook creates a new hidden Outlook application. Whille this may be OK for thoroughly tested and robust code, it is not good for more fragile code during development and testing. I suggest that OutApp.Visible = True be included early in the code. Also I propose that the following is better when obtaining a reference to Outlook.

Code:

    On Error Resume Next
    Set OutApp= GetObject(, "Outlook.Application")
    Select Case Err.Number
    Case 0
        On Error GoTo OpenOutlookSession_Error
    Case 429
        On Error GoTo OpenOutlookSession_Error
        Set OutApp= CreateObject("Outlook.Application")
    Case Else
        GoTo OpenOutlookSession_Error
    End Select

This code will reference the existing Outlook application, if any, before attempting to create a new session. The dangers of having multiple copies of 'hanging' hidden applications are multifarious.

**joelmeaders** · 10-09-2012, 09:56 AM

That's brilliant, thanks! I never even thought about checking to see if it was already open to use. I am going to update some apps right now.

Mail merge based on check boxes

Thread Tools

Mail merge based on check boxes

Similar Threads

Mail Merge

Mail Merge

Queries Based On Check Boxes

Mail Merge

Mail merge

Posting Permissions