Emailing in Access

Good afternoon experts,
I am creating an Access database that stores training dates for the hospital I am working at. The issue I am running into is with a "mass email". I want to be able to pull all the email addresses of personnel that is expired training. Something like:

qryTraining1:
SELECT EmailAddress From dbTraining WHERE training1 <= Date - 365

and with "qryTraining1", I want to open outlook with all those email addresses listed.
Thank you for your time.
V/R,
Shannon
LVL 1
ShannonCallahanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Sub mail()
Dim objOutlook As Object
Dim objEmail As Object
Dim EmlTo As String
Dim rst As DAO.Recordset
Set objOutlook = CreateObject("Outlook.application")
Set rst = CurrentDB.OpenRecordset("qryTraining1")
EmlTo = ""
Do While Not rst.EOF
    If rst!EmailAddress & "" <> "" Then
        If EmlTo = "" then
                        rst!EmailAddress
        else
              EmlTo = EmlTo & ";" & rst!EmailAddress
        End if
    end if
    rst.MoveNext
Loop
rst.close
With objEmail
            .To = EmlTo
            .Subject = "some subject" ' Your subject
            .body = "" 'You can fill message body here
            .display
End With
Set objEmail = Nothing
Set objOutlook = Nothing
Set rst = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Not sure what your exact difintion of "expired is, (training1 <= Date - 365?)
 ...but this worked for me:

SELECT YourTable.EmpID, YourTable.TrainingExp, IIf([TrainingExp]>Date(),"Expired","Still Valid") AS Expired
FROM YourTable
WHERE (((IIf([TrainingExp]>Date(),"Expired","Still Valid"))="Expired"));


Sample db attached
Database50.mdb
0
als315Commented:
Small error in my code:
If EmlTo = "" then
                        EmlTo = rst!EmailAddress  'Correct this line
        else
              EmlTo = EmlTo & ";" & rst!EmailAddress
End if

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ShannonCallahanAuthor Commented:
Thank you for the reply. Here is the following that I am trying to use with the correct verbage:

Function mail()
Dim objOutlook As Object
Dim objEmail As Object
Dim EmlTo As String
Dim rst As DAO.Recordset
Set objOutlook = CreateObject("Outlook.application")
Set rst = CurrentDb.OpenRecordset("ATL1Expired")
EmlTo = ""
Do While Not rst.EOF
    If rst![E-mail Address] & "" <> "" Then
        If EmlTo = "" Then
                        EmlTo = rst![E-mail Address]
        Else
              EmlTo = EmlTo & ";" & rst![E-mail Address]
        End If
    End If
    rst.MoveNext
Loop
rst.Close
With objEmail
            .To = EmlTo
            .Subject = "Test"
            .body = "Test"
            .display
End With
Set objEmail = Nothing
Set objOutlook = Nothing
Set rst = Nothing
End Function

Open in new window


From there I am using the macro "runcode" and assigned it to a button. However, the following is the error message I am getting:

"Object variable or WITH block variable not set"

Thanks again for the help.
V/R,
-Shannon-
0
Jeffrey CoachmanMIS LiasonCommented:
<Thank you for the reply.>
Who was this post intended for?
0
als315Commented:
Sorry, add this line:
Set objEmail = objOutlook.CreateItem(0)
before
With objEmail
0
ShannonCallahanAuthor Commented:
Thank you all for the help.
V/R,
Shannon Callahan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.