Emailing Multiple specific users using record set. (1 Viewer)

[pjawynn]

Good Evening

I am trying to find out is the below question is possible.

I have a form for mailing which lists the department which the mails go to. controlled by a tick box next to each department.

I have a table named mailings, with Columns NameID;Email;reportsto

With research I have been able to put together the following code which does loop through and adds all emails from the Email Column, but what I need to do is be able to add a where command like for a Dlookup to say lookup email where reportsto = accommodation. Can this be done, I have attempted to added the additional in the Accommodationmail = Accommodationmail & rst("Email","[reportsto] = ""Accommodation""") & ";" like you would for a dlookup but it does not work.

Example of code which is selecting all email address

Private Sub AddRecipients_Click()
'On Error GoTo ErrHandler

Dim objOutlook As Object
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim rst As DAO.Recordset
Dim Accommodationmail As String

If Me.AccomTick.Value = True Then

Set rst = CurrentDb.OpenRecordset("Mailings")

Do Until rst.EOF
Accommodationmail = Accommodationmail & rst("Email") & ";"
rst.MoveNext
Loop

Accommodationmail = Left(Accommodationmail, Len(Accommodationmail) - 1)

End If

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(Accommodationmail)
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
' Set objOutlookRecip = .Recipients.Add("")
' objOutlookRecip.Type = olCC

' Set the Subject, Body, and Importance of the message.
.Subject = "test"
.Body = "test"
.Importance = olImportanceHigh 'High importance
' Set objOutlookAttach = .Attachments.Add()
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
'If DisplayMsg Then
.Display
' Else
' .Save
' .send
' End If
End With
Set objOutlook = Nothing

Exit Sub

'ErrHandler:
MsgBox "There has been an Error Please check your inputted Data. If Problem Persists please email with the following error: " & " " & Err.Description & " " & Err.Number

Resume Exitsub
Exitsub:
Exit Sub
End Sub

Thank you

Pjawynn

 

[Mark_]

I would replace

Set rst = CurrentDb.OpenRecordset("Mailings")

with

Dim asSql as String ' String to hold SQL
Dim asMatch as String 'Value to match to

asMatch = "Accommodation"

asSql = "SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " &  asMatch & "'"

debug.print asSql 'Show EXACTLY what we are passing for debug purposes.

Set rst = CurrentDb.OpenRecordset(asSQL)

This way you can verify what you are looking for and you know what you should get. As you progress, you can change asMatch for what ever matches you need, or even move to using a query the returns ONLY what you are looking for.

 

[pjawynn]

Good Evening Mark_

Thanks for the quick reply.

Unfortunately all that I get when I run the script is that within the email the .To recipient now reads as

SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " & asMatch & "'"

No email address have been added from the column.

Kind Regards

pjawynn

 

[Cronk]

It looks like you have put

Accommodation=asSql

It should be, to fill it out with more detail

Dim asSql as String ' String to hold SQL
Dim asMatch as String 'Value to match to

asMatch = "Accommodation"

asSql = "SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " &  asMatch & "'"

debug.print asSql 'Show EXACTLY what we are passing for debug purposes.

Set rst = CurrentDb.OpenRecordset(asSQL)
Do Until rst.EOF
Accommodationmail = Accommodationmail & rst("Email") & ";"
rst.MoveNext
Loop

Accommodationmail = Left(Accommodationmail, Len(Accommodationmail) - 1)

The Where clause

WHERE mailings.reportsto = ' " &  asMatch & "'"

filters the recordset and only includes those with reportsto ="accommodation"

 

[pjawynn]

Thanks Cronk

But no I get an Invalid argument error on the line Accommodationmail = Left(Accommodationmail, Len(Accommodationmail) - 1)

I blanked that out for the time being as it is just deleting the space.

Then I get a run time error that there is no people in the .to mailings.

What I don't understand is if I put a message box after the

asSql = "SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " & asMatch & "'"

For example

msgbox asSql

I would expect to get back the information from the select command and all the email address connect to accommodation but what it returns is just

"SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " & asMatch & "'"

As if the select command is not gathering the information.

Any ideas

Thanks

pjawynn

 

[Cronk]

If asMatch has the value Accommodation then

asSql = "SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " & asMatch & "'"

has to lead to asSql having the text value

SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' Accommodation'

and either msgbox asSql, or debug.print asSql will give that text.

There is no reason why you would get an 'Invalid Argument' unless the string Accommodationmail was a null string, ie no records matched 'Accommodation'

 

[George Moore]

asSql = "SELECT mailings.Email FROM mailings WHERE mailings.reportsto = ' " & asMatch & "'"

Looks like there is a space in the criteria which will affect results, try

mailings.reportsto = '" & asMatch & "'"