Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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