Solved

Send email based on birth month

Posted on 2015-01-16
15
148 Views
Last Modified: 2016-02-10
I am using Access 2013 and need help with send an email based on birth month.

I have a table with all employees and need a ( macro or vba ) button that when pressed will send a seperate email to each person for the current birth month. I also need to place some generic text and subject for each email.
0
Comment
Question by:CMILLER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40553943
using recordset..


Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
dim rs as dao.recordset, strText, strSubj, strTo
set rs=currentdb.openrecordset("select * from tblEmployees where month([birthdate])=Month(Now())")

do until rs.eof
   strTo=rs!Email
   strText="<some text>"
  strsubj="<subject>"

oMail.Body = strText
oMail.Subject = strsubj
oMail.To = strTo
oMail.Send


rs.movenext
loop

Set oMail = Nothing
Set oApp = Nothing
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40554030
<No Points wanted>
You may encounter the Outlook security popup.
If so, then you can use this utility to bypass/suppress it.
http://www.contextmagic.com/express-clickyes/
0
 

Author Comment

by:CMILLER
ID: 40554588
Having an issue at:  dim rs as dao.recordset, strText, strSubj, strTo
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:CMILLER
ID: 40554592
I fixed it. Added from the Ref Lib.

Now I get a run-time error 3131: syntax error in FROM clause
0
 

Author Comment

by:CMILLER
ID: 40554596
My table name has spaces. Created a temp local with no spaces in the table name.

Now I get an run-time error 287 at oMail.Send
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40554597
@CMILLER
without seeing what codes you  are using it will be difficult to know why you are getting the error
0
 

Author Comment

by:CMILLER
ID: 40554606
Option Compare Database
Option Explicit

Function ExportBirthMonthEmail()


Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
Dim rs As DAO.Recordset, strText, strSubj, strTo
Set rs = CurrentDb.openrecordset("select * from DCPDSMilitaryDataDOBEmail where month([DOB])=Month(Now())")

Do Until rs.EOF
strTo = rs!Email
strText = "<some text>"
strSubj = "<subject>"

oMail.Body = strText
oMail.Subject = strSubj
oMail.To = strTo
oMail.Send


rs.movenext
Loop

Set oMail = Nothing
Set oApp = Nothing

End Function
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40554613
try replacing

oMail.Send

with

oMail.Display
0
 

Author Comment

by:CMILLER
ID: 40554621
That worked and it sent an email but it only sent one email, which was to the last person in the table. it should have sent three seperate emails.
0
 

Author Comment

by:CMILLER
ID: 40554655
The user has decided that they want one email sent with all employees that have a birthday in the current.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40554734
try this then

Dim oApp As Outlook.Application
 Dim oMail As MailItem
 Set oApp = CreateObject("Outlook.application")
 Set oMail = oApp.CreateItem(olMailItem)
 Dim rs As DAO.Recordset, strText, strSubj, strTo
 Set rs = CurrentDb.openrecordset("select * from DCPDSMilitaryDataDOBEmail where month([DOB])=Month(Now())")

 strText = "<some text>"
 strSubj = "<subject>"

 Do Until rs.EOF
      strTo = strTo & ";" & rs!Email

 rs.movenext
 Loop
 
 strTo=mid(strTo,2)

 oMail.Body = strText
 oMail.Subject = strSubj
 oMail.To = strTo
 oMail.display


 Set oMail = Nothing
 Set oApp = Nothing
0
 

Author Comment

by:CMILLER
ID: 40560771
That Worked. Thanks!
0
 

Author Comment

by:CMILLER
ID: 40589095
Obrero,

Hi, quick question.

For the strText = "<some text>", How do I make it multiple paragraphs with spaces in between the paragraphs?

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40589145
strText="The quick brown fox" & vbcrlf & vbcrlf
strtext=strtext & "jump over the" & vbcrlf & vbcrlf
strtext=strtext & "lazy sleeping dog"
0
 

Author Comment

by:CMILLER
ID: 40589917
ahh....cool thanks!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question