Solved

Send email based on birth month

Posted on 2015-01-16
15
144 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 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