Group emails from Access to Outlook

[DB Novice]

I'm sure this is already here somewhere, but haven't been able to find it...

I need to send out an email from Access through Outlook, using all of the email addresses listed in "Email" in a query.

(And, a related question: In my db, there are some families where the husband and wife use the same email address, so that email address appears twice in the query results--once under the husband's name and once with the wife's name. Is there a way to get the query to eliminate one of them, so I don't send two email messages to the same address?)

[HansV]

Do you want to send individual messages to each unique email address, or do you want to send one message to all email addresses (for example using BCC if you don't want recipients to see the others)?

[xps35]

Use SELECT DISTINCT in your query to eliminate duplicates.

[DB Novice]

I would like to send the same message to all in the "Email" list, and, yes, I would like to use the BCC.

[HansV]

As xps35 suggested, use something like

SELECT DISTINCT EmailAddress FROM TableName

to return unique email addresses. You can then use code like this:

Code: Select all

Sub SendEmail()
    Dim sql As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim adr As String
    Dim objOL As Object
    Dim objMsg As Object
    ' SQL to get unique email addresses
    sql = "SELECT DISTINCT EmailAddress FROM TableName"
    Set dbs = CurrentDb
    ' Open recordset
    Set rst = dbs.OpenRecordset(Name:=sql)
    ' Loop through the records
    Do While Not rst.EOF
        'Concatenate email addresses
        adr = adr & ";" & rst.Fields(0)
        rst.MoveNext
    Loop
    ' Close the recordset
    rst.Close
    ' Remove the first ;
    adr = Mid(adr, 2)
    ' Start Outlook
    Set objOL = CreateObject(Class:="Outlook.Application")
    ' Create message
    Set objMsg = objOL.CreateItem(0) ' 0 = olMailItem
    ' Set recipient to yourself
    objMsg.To = "your own email address"
    ' Set BCC
    objMsg.BCC = adr
    ' Set subject
    objMsg.Subject = "This is the subject"
    ' Set message body
    objMsg.Body = "This is a test of the emergency broadcasting system"
    ' Display the message
    objMsg.Display
End Sub

The code works best if Outlook is already running.

[DB Novice]

Thanks! I will work on that later today and see if I can get it going. I appreciate the help!

[DB Novice]

Error with this code

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

User-defined type not defined

So, I tried adding Microsoft DAO 3.6 Object Library in the References...


And then I get this error:
"Error in loading DLL"

Running Microsoft® Access® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20188) 64-bit on Windows 10

[HansV]

You should set a reference to the Microsoft Office 16.0 Access database engine Object Library. That is the DAO library for recent versions of Access.

[DB Novice]

You should set a reference to the Microsoft Office 16.0 Access database engine Object Library. That is the DAO library for recent versions of Access.

That's what I was attempting when I got the second error loading the DLL:

So, I tried adding Microsoft DAO 3.6 Object Library in the References...


And then I get this error:
"Error in loading DLL"

I'm trying to track that down and fix that, but am open to ideas on how...

[HansV]

DAO 3.6 is not the same as Microsoft Office 16.0 Access database engine Object Library!

[DB Novice]

DAO 3.6 is not the same as Microsoft Office 16.0 Access database engine Object Library!

Sorry! I read what I expected you to say...

The Microsoft Office 16.0 Access Object Library is already listed in available references...

[HansV]

There is no

Microsoft Office 16.0 Access Object Library

Do you mean the

Microsoft Access 16.0 Object Library

or the

Microsoft Office 16.0 Access database engine Object Library

[DB Novice]

OK--maybe I should learn to read more accurately... (note my username )

Sorry for being so dense!

But it all seems to working now. Thanks so much!

I may try my hand at setting up different queries to create different email groups one of these days...

But I appreciate your help and your patience immensely!

[DB Novice]

So, I am using that code now for emails, but wondered if there is a way to set a particular font and size for the email, and perhaps a background image for the email as well?

These are the last lines of the code...I'm guessing there would be more settings that could be applied?

objMsg.To = "so-and-so@such-and-such.net"
' Set BCC
objMsg.BCC = adr
' Set subject
objMsg.Subject = "Class"
' Set message body
objMsg.Body = ""
' Display the message
objMsg.Display

[HansV]

objMsg.Body will use the default font. If you want to format the text, you have two options:

1) Use objMsg.HTMLBody instead of objMsg.Body. You set this to a text string with HTML code, for example

Code: Select all

 objMsg.HTMLBody = "<HTML><BODY><FONT FACE=Calibri SIZE=14pt><P>This is the first line</P>" & _
        "<P>This is <B>bold</B> and this is <I>italic</I></P></FONT></BODY></HTML>"

2) You can use Word as mail editor. You'll have access to most Word VBA commands to assemble the body of the message, so you must be familiar with Word VBA.

[DB Novice]

Thanks, Hans!