How can I send an Outlook email based on a list of emails in an Access query? (1 Viewer)

malfiscious

Registered User.
Local time
Today, 22:17
Joined
Feb 15, 2013
Messages
24
Hi, I've setup a selection form that returns a specific list of email addresses in a query.

What I can't work out is how to press a button and have the email addresses populate the To: field in a blank Outlook email - I don't need any bells or whistles.

I thought it would be simple but I'm really struggling to find an answer? Any help is much appreciated.
 

Minty

AWF VIP
Local time
Today, 22:17
Joined
Jul 26, 2013
Messages
10,355
Probably the easiest route is to create the email object from access - something like;

Code:
    Dim OutApp As Object
    Dim OutMail As Object
       '-- Standard Email Variables
    Dim Variable_To As String
    Dim Variable_Subject As String
    Dim Variable_Body As String
    Dim Variable_AttachmentFile As String

Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
     With OutMail
        .Display
    End With
        
    With OutMail
    
        .To = Variable_To
        .CC = ""
        .BCC = ""
        .Subject = Variable_Subject
        .MailItem.ReplyRecipients.Add = Variable_ReplyTo
        .SentOnBehalfOfName = Variable_SentFrom
        .Attachments.Add (Variable_AttachmentFile)
        .Body = Variable_Body 
        .Display   'or use .Send
        .ReadReceiptRequested = False
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

Obviously this has a lot of added functionality for your purposes - but you get the idea I hope.
 

malfiscious

Registered User.
Local time
Today, 22:17
Joined
Feb 15, 2013
Messages
24
Thanks for the quick reply

Possibly a daft question, but how do I assign the Variable_To so that it takes the entire query list please?

Thanks
 

Minty

AWF VIP
Local time
Today, 22:17
Joined
Jul 26, 2013
Messages
10,355
Are they all receiving the same email?
If so you can use a concatenation call to put them all into the BCC field. (You don't want everyone's email address on show to everyone in your list)

Have a look at Allen Browne Excellent Web Site for an example of a VBA function that can perform this for you.

If not or you want to send individual emails you would need to loop around the outlook code and insert each name into a individual email.
 

malfiscious

Registered User.
Local time
Today, 22:17
Joined
Feb 15, 2013
Messages
24
Yes, I want them all to receive the same email - the To: field is fine since it'll always be an internal email

You're code has worked great but I'm still stuck on adding the email addresses

I've had a look at the site you linked but it's beyond me to be honest

I have a field called 'E-Mail' in a table called 'Email_List' and I need a semi-colon ";" separator

If there's an idoit proof method for this I'd be grateful
 

Minty

AWF VIP
Local time
Today, 22:17
Joined
Jul 26, 2013
Messages
10,355
One way or another you will need to create a semicolon separated list. You could do this in your query, but someone has done the hard work for you.

This module although a little complex will be invaluable once you have it set up - Create a new Module in VBA (Call it BasFunctions or similar) Then paste the following;
Code:
Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSQL As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    'Initialize to Null
    ConcatRelated = Null
    
    'Build SQL string, and get the records.
    strSQL = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSQL = strSQL & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

Then call this function in a new query based on your Table. In the field line (top line) of the new query window create an output To_Email_Addresss: ConcatRelated("E-Mail","EMail_List",,,"; ")

This should give you exactly what you need.
 

malfiscious

Registered User.
Local time
Today, 22:17
Joined
Feb 15, 2013
Messages
24
Thanks for that

I'd actually reached that point and thought I'd gone horribly wrong somewhere

The problem at this stage is that the query won't accept the syntax - it returns the following message

"The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or identifier."

I'm pretty sure it's related to the commas sitting in the 3rd and 4th arguments

This is where I'm stumped because the function says they're optional

Thanks for all your help Minty
 

Minty

AWF VIP
Local time
Today, 22:17
Joined
Jul 26, 2013
Messages
10,355
Hi - Apologies I may have had the syntax a little off - and in fact you could add this in the email code directly once you have tested the results in a query;

Code:
Dim sTest as String

sTest = ConcatRelated("E-Mail","[EMail_List]","","","; ") 
To_Email_Addresss = sTest
 

malfiscious

Registered User.
Local time
Today, 22:17
Joined
Feb 15, 2013
Messages
24
Hi Minty, thanks again

Just a quick good news/bad news update

Good news is that it works perfectly when I run against the table that holds the source data - so I'm really happy that it's working the way you said it should

Bad news is that it won't work with the query data

One long winded solution I've put together for now is to make the query append to a new table before running the function and then run a delete query once the email addresses are added

It works - fortunately I'm only dealing with a couple of hundred contacts so this should be ok - feels a bit messy though

Thanks again for all your help
 

Users who are viewing this thread

Top Bottom