• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Send email based on birth month

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
CMILLER
Asked:
CMILLER
  • 9
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
CMILLERAuthor Commented:
Having an issue at:  dim rs as dao.recordset, strText, strSubj, strTo
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
CMILLERAuthor Commented:
I fixed it. Added from the Ref Lib.

Now I get a run-time error 3131: syntax error in FROM clause
0
 
CMILLERAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
@CMILLER
without seeing what codes you  are using it will be difficult to know why you are getting the error
0
 
CMILLERAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
try replacing

oMail.Send

with

oMail.Display
0
 
CMILLERAuthor Commented:
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
 
CMILLERAuthor Commented:
The user has decided that they want one email sent with all employees that have a birthday in the current.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
CMILLERAuthor Commented:
That Worked. Thanks!
0
 
CMILLERAuthor Commented:
Obrero,

Hi, quick question.

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

Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
strText="The quick brown fox" & vbcrlf & vbcrlf
strtext=strtext & "jump over the" & vbcrlf & vbcrlf
strtext=strtext & "lazy sleeping dog"
0
 
CMILLERAuthor Commented:
ahh....cool thanks!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now