hi Khalid,
in the code example, move :
Set appOut = CreateObject("Outlook.Application")
to BEFORE the loop. If sPathFileAttachmentis is the same each time, that should come out of the loop too-- and anything else that doesn't change
include MemberID in the field list -- then you can keep track of it with a variable. After variables are all assigned for the current record, do:
rst.MoveNext
(so this gets moved too)If the memberID is different, than process the email. If it is not, add addresses to sMailTo. HOWEVER, it would be better to add a Yes/No field to the email addresses table and only pull one of them.
respectfully,
crystal
~ have an awesome day ~
On 7/7/17 4:59 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] wrote:
John-
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,RegardsKhalid
---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.RecordsetSet db = CurrentDbSet 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.MoveNextLoop
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)
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: crystal 8 <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
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