Sorry, needs a MoveNext:
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qryHappyBirthday")
' If got a record,
If Not rst.EOF Then
' grab the first email
sMailTo = rst!MemberEmail
Else
' Display message
MsgBox "No birthdays today."
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If
' Go to the second one
rst.MoveNext
' Now loop for the rest
Do Until rst.EOF
sMailTo = sMailTo & ", " & rst!MemberEmail
rst.MoveNext
Loop
sSubject = "Happy Birthday"
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jul 8, 2017, at 11:13 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
Thanks again for modifying the code. I did as you said. All is OK except on the To:` box in Outlook i am getting email address twice for a single email address, also it is in the following pattern:
After couple of seconds it changes to:
Is it because of line:
sMailTo = sMailTo & ", " & rst!MemberEmail <----------- ? OR is it OK
I used Click Event on a command button on form "Notification3" footer to call this Sub
Call EmailOutlook
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
Get rid of the rst.MoveNext and Loop at the end and change the start to:
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qryHappyBirthday")
' If got a record,
If Not rst.EOF Then
' grab the first email
sMailTo = rst!MemberEmail
Else
' Display message
MsgBox "No birthdays today."
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If
' Now loop for the rest
Do Until rst.EOF
sMailTo = sMailTo & ", " & rst!MemberEmail
rst.MoveNext
Loop
sSubject = "Happy Birthday"
... etc.
Yes, I suppose you could call this from a macro or from the Click event of a command button on a form.
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Jul 7, 2017, at 23:59, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
This is great and working, I didn't sent mail to that email, because we are still working on it, like inserting C.C etc;
I had only one member whose birthday was today on 08/07/2017. In order to check what happens if there is birthday for more than one member, i added myself as member and set my date of birth as 08/071955 and put my email address.
While i run the module pressing F5 button i observed that it is sending separate mails to both email addresses.
Could all email addresses be inserted in To: box of Outlook instead of sending separate emails to them.
OR you suggest to send separate emails as the code is now ?
By the way at present there are 995 members and i assume at the most maximum of 10 members may have same birthday, in this case present code is also feasible.
And now finally, to run this module should i use a macro ?
Thanking you,
Regards
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
What's so hard about adding code to open the query and loop through the records?
Sub EmailOutlook()
'strive4peace
'use early binding to develop
' Dim appOut As Outlook.Application _
, oMsg As Outlook.MailItem
'use late binding to deploy
Dim appOut As Object _
, oMsg As Object
Dim sPathFileAttachment As String _
, sSubject As String _
, sBody As String _
, sMailTo As String
Dim db As DAO.Database, rst As DAO.Recordset
sMailTo = rst!MemberEmail '<=== I NEED HERE EMAIL ADDRESSES TO BE FILLED ON THE RESULTS OF
' "qryHappyBirthday"
sSubject = "Happy Birthday"
sBody = "Your message..."
sPathFileAttachment = "C:\Users\tanwe\Desktop\Today for sending email.txt" 'or whatever this is '<== This is ' an example, i may attach here some image.
'sPathFileAttachment = "c:\path\file.ext" 'or whatever this is
Set appOut = CreateObject("Outlook.Application")
Set oMsg = appOut.CreateItem(0) '0=olMailItem
With oMsg
'.Importance = olImportanceHigh
.To = sMailTo
'.CC = "cc email address"
'.BCC = "cc email address"
.Subject = sSubject
.Body = sBody
If sPathFileAttachment <> "" Then
.Attachments.Add sPathFileAttachment
End If
' If you want to edit before sending
.Display
'otherwise, to just send without looking...
'.Send
End With
rst.MoveNext
What's so hard about adding code to open the query and loop through the records?
Sub EmailOutlook()
'strive4peace
'use early binding to develop
' Dim appOut As Outlook.Application _
, oMsg As Outlook.MailItem
'use late binding to deploy
Dim appOut As Object _
, oMsg As Object
Dim sPathFileAttachment As String _
, sSubject As String _
, sBody As String _
, sMailTo As String
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qryHappyBirthday")
Do Until rst.EOF
sMailTo = rst!MemberEmail '<=== I NEED HERE EMAIL ADDRESSES TO BE FILLED ON THE RESULTS OF
' "qryHappyBirthday"
sSubject = "Happy Birthday"
sBody = "Your message..."
sPathFileAttachment = "C:\Users\tanwe\Desktop\Today for sending email.txt" 'or whatever this is '<== This is ' an example, i may attach here some image.
'sPathFileAttachment = "c:\path\file.ext" 'or whatever this is
Set appOut = CreateObject("Outlook.Application")
Set oMsg = appOut.CreateItem(0) '0=olMailItem
With oMsg
'.Importance = olImportanceHigh
.To = sMailTo
'.CC = "cc email address"
'.BCC = "cc email address"
.Subject = sSubject
.Body = sBody
If sPathFileAttachment <> "" Then
.Attachments.Add sPathFileAttachment
End If
' If you want to edit before sending
.Display
'otherwise, to just send without looking...
'.Send
End With
rst.MoveNext
Loop
Proc_Exit:
On Error Resume Next
'release object variables
Set appOut = Nothing
Set oMsg = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " EmailOutlook"
Resume Proc_Exit
Resume
End Sub
On Jul 7, 2017, at 9:25 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi All,
I need to send birthday wish to members whom birthday occurs on the current date. In order to get members birthday on the current date i have a query "qryHappyBirthday" with Sql:
SELECT Members.MembershipType, Members.MembershipTitle, Members.MemberID, Members.SurName, Members.GivenName, Members.GenderStatus, Members.DesignationSOC, Members.Dob, Members.PlaceOfBirth, tblContactNumbers.TelephoneResidence, tblContactNumbers.MobileNo1, tblElectronicContacts.MemberEmail
FROM (Members INNER JOIN tblContactNumbers ON Members.MemberID = tblContactNumbers.MemberID) INNER JOIN tblElectronicContacts ON Members.MemberID = tblElectronicContacts.MemberID
WHERE (((Month([Dob]))=Month(Date())) AND ((Day([Dob]))=Day(Date())));
I have got a module "mod_EmailOutlook" with code:
Option Compare Database
Option Explicit
Sub EmailOutlook()
'strive4peace
'use early binding to develop
' Dim appOut As Outlook.Application _
, oMsg As Outlook.MailItem
'use late binding to deploy
Dim appOut As Object _
, oMsg As Object
Dim sPathFileAttachment As String _
, sSubject As String _
, sBody As String _
, sMailTo As String
sMailTo = "someone@..." '<=== I NEED HERE EMAIL ADDRESSES TO BE FILLED ON THE RESULTS OF
' "qryHappyBirthday"
sSubject = "Happy Birthday"
sBody = "Your message..."
sPathFileAttachment = "C:\Users\tanwe\Desktop\Today for sending email.txt" 'or whatever this is '<== This is ' an example, i may attach here some image.
'sPathFileAttachment = "c:\path\file.ext" 'or whatever this is
Set appOut = CreateObject("Outlook.Application")
Set oMsg = appOut.CreateItem(0) '0=olMailItem
With oMsg
'.Importance = olImportanceHigh
.To = sMailTo
'.CC = "cc email address"
'.BCC = "cc email address"
.Subject = sSubject
.Body = sBody
If sPathFileAttachment <> "" Then
.Attachments.Add sPathFileAttachment
End If
' If you want to edit before sending
.Display
'otherwise, to just send without looking...
'.Send
End With
Proc_Exit:
On Error Resume Next
'release object variables
Set appOut = Nothing
Set oMsg = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " EmailOutlook"
Resume Proc_Exit
Resume
End Sub
~~~~~~~~~~~~~~~~
Above module after pressing F5 key works perfect, it opens Outlook. I've tested it by putting my own email address and sending and received mail send from Outlook.
I assume (May be i'm wrong) there must be something like DO WHILE EOF and LOOP in the above code, but i have no knowledge in Access/VBA about this.
When all this is set, how i will call this module to run ?. I need to put a button on my Continuous form "Notification3" to call this module. This form displays the output of query "qryHappyBirthday"
Help required please.
Regards,
Khalid
Proc_Exit:
On Error Resume Next
'release object variables
Set appOut = Nothing
Set oMsg = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " EmailOutlook"
Resume Proc_Exit
Resume
End Sub
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
On Jul 7, 2017, at 9:25 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi All,
I need to send birthday wish to members whom birthday occurs on the current date. In order to get members birthday on the current date i have a query "qryHappyBirthday" with Sql:
SELECT Members.MembershipType, Members.MembershipTitle, Members.MemberID, Members.SurName, Members.GivenName, Members.GenderStatus, Members.DesignationSOC, Members.Dob, Members.PlaceOfBirth, tblContactNumbers.TelephoneResidence, tblContactNumbers.MobileNo1, tblElectronicContacts.MemberEmail
FROM (Members INNER JOIN tblContactNumbers ON Members.MemberID = tblContactNumbers.MemberID) INNER JOIN tblElectronicContacts ON Members.MemberID = tblElectronicContacts.MemberID
WHERE (((Month([Dob]))=Month(Date())) AND ((Day([Dob]))=Day(Date())));
I have got a module "mod_EmailOutlook" with code:
Option Compare Database
Option Explicit
Sub EmailOutlook()
'strive4peace
'use early binding to develop
' Dim appOut As Outlook.Application _
, oMsg As Outlook.MailItem
'use late binding to deploy
Dim appOut As Object _
, oMsg As Object
Dim sPathFileAttachment As String _
, sSubject As String _
, sBody As String _
, sMailTo As String
sMailTo = "someone@..." '<=== I NEED HERE EMAIL ADDRESSES TO BE FILLED ON THE RESULTS OF
' "qryHappyBirthday"
sSubject = "Happy Birthday"
sBody = "Your message..."
sPathFileAttachment = "C:\Users\tanwe\Desktop\Today for sending email.txt" 'or whatever this is '<== This is ' an example, i may attach here some image.
'sPathFileAttachment = "c:\path\file.ext" 'or whatever this is
Set appOut = CreateObject("Outlook.Application")
Set oMsg = appOut.CreateItem(0) '0=olMailItem
With oMsg
'.Importance = olImportanceHigh
.To = sMailTo
'.CC = "cc email address"
'.BCC = "cc email address"
.Subject = sSubject
.Body = sBody
If sPathFileAttachment <> "" Then
.Attachments.Add sPathFileAttachment
End If
' If you want to edit before sending
.Display
'otherwise, to just send without looking...
'.Send
End With
Proc_Exit:
On Error Resume Next
'release object variables
Set appOut = Nothing
Set oMsg = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " EmailOutlook"
Resume Proc_Exit
Resume
End Sub
~~~~~~~~~~~~~~~~
Above module after pressing F5 key works perfect, it opens Outlook. I've tested it by putting my own email address and sending and received mail send from Outlook.
I assume (May be i'm wrong) there must be something like DO WHILE EOF and LOOP in the above code, but i have no knowledge in Access/VBA about this.
When all this is set, how i will call this module to run ?. I need to put a button on my Continuous form "Notification3" to call this module. This form displays the output of query "qryHappyBirthday"
Help required please.
Regards,
Khalid
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar