appc
asked on
How do I send an email from Access to each recipient in a query?
I would like to send a standardized email to each recipient of a query.
I assume my code should be something like:
For each "recipient" of "query x"
send email
Thanks!
I assume my code should be something like:
For each "recipient" of "query x"
send email
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
matthewspatrick,
How would you modify this code to get multiple recipients on the TO line? Does the string need to have a certain format or do you need a loop to add each name seperately?
Thanks
How would you modify this code to get multiple recipients on the TO line? Does the string need to have a certain format or do you need a loop to add each name seperately?
Thanks
ASKER
I have a problem with the DAO.Recordset part. Apparently this type of variable is not recognized by my
Access.
Access.
appc ... go to Tools - References while your code module is open and set a reference to the Microsoft DAO 3.6 Object Library. Then the DAO.Recordset variable will be recognized.
To answer my own question: For multiple recipients in the same .TO line, use the following format:
strRecipient = "EmailName1" & "; " & "EmailName2" & "; " & "EmailName3" & "; " & "EmailName4"
To answer my own question: For multiple recipients in the same .TO line, use the following format:
strRecipient = "EmailName1" & "; " & "EmailName2" & "; " & "EmailName3" & "; " & "EmailName4"
ASKER
That is my code right now:
Public Function Sendemails()
Dim olApp As Object
Dim olook As Object
Dim olMsg As Object
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT BranchEscEmailAddresses FROM [NPL Report Calculation for emails]"
Set rs = CurrentDb.OpenRecordset(st rSql)
Set olook = CreateObject("Outlook.Appl ication")
Do Until rs.EOF
Set olMsg = olApp.CreateItem(0)
With olMsg
.To = rs![BranchEscEmailAddresse s]
'.CC = "y@z.com"
'.BCC = "z@z.com"
.Subject = "subject"
.Body = "body"
' .Attachments.Add "c:\folder\subfolder\foo.x ls"
.Send
End With
rs.MoveNext
Loop
Set olMsg = Nothing
Set olApp = Nothing
rs.Close
Set rs = Nothing
End Function
and when I try and run the function I get the following error message:
Image.bmp
Public Function Sendemails()
Dim olApp As Object
Dim olook As Object
Dim olMsg As Object
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT BranchEscEmailAddresses FROM [NPL Report Calculation for emails]"
Set rs = CurrentDb.OpenRecordset(st
Set olook = CreateObject("Outlook.Appl
Do Until rs.EOF
Set olMsg = olApp.CreateItem(0)
With olMsg
.To = rs![BranchEscEmailAddresse
'.CC = "y@z.com"
'.BCC = "z@z.com"
.Subject = "subject"
.Body = "body"
' .Attachments.Add "c:\folder\subfolder\foo.x
.Send
End With
rs.MoveNext
Loop
Set olMsg = Nothing
Set olApp = Nothing
rs.Close
Set rs = Nothing
End Function
and when I try and run the function I get the following error message:
Image.bmp
If you want to send more professional emails such as HTML pages with embedded graphics, avoid the limitations of Outlook/MAPI, easily personalize each email, and attach linked objects such as tables/reports, etc. filtered for each contact, consider our Total Access Emailer product.
It's a commercial product and can use any table or query as its data source for the list of emails to send out. There's a free demo, so you can use that if the commercial option isn't viable.
More info here: http://www.fmsinc.com/MicrosoftAccess/Email.asp
It's a commercial product and can use any table or query as its data source for the list of emails to send out. There's a free demo, so you can use that if the commercial option isn't viable.
More info here: http://www.fmsinc.com/MicrosoftAccess/Email.asp
appc,
The code looks fine. Your screen shot says nothing about what was in your OnClick property for that form.
I typically just embed that code in the Click event sub for the commandbutton I use on the form.
Patrick
The code looks fine. Your screen shot says nothing about what was in your OnClick property for that form.
I typically just embed that code in the Click event sub for the commandbutton I use on the form.
Patrick
ASKER
ASKER
The field BrEmails contains email addresses.
For example one record will be: x@domain.com; y@domain.com
And again "email' is a query.
But I still get the error message...
Emails.bmp
For example one record will be: x@domain.com; y@domain.com
And again "email' is a query.
But I still get the error message...
Emails.bmp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Patrick,
First of thanks a lot for all your help. I will not include spaces anymore. :)
I have set a reference to Microsoft DAO and as you can see on the last screenshot I changed the field and query names to get rid of the spaces.
But I still get the debugger. Is it because of the variable type of strsql? I have no clue..
First of thanks a lot for all your help. I will not include spaces anymore. :)
I have set a reference to Microsoft DAO and as you can see on the last screenshot I changed the field and query names to get rid of the spaces.
But I still get the debugger. Is it because of the variable type of strsql? I have no clue..
ASKER
I guess this will help a little.
Error.bmp
Error.bmp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The reference is set, and I tried SELECT BrEmails FROM email from Access and it worked...
ASKER
Why would it say "too few parameters. Expected 1"??.....
appc,
If the table or column name weren't being recognized, then Jet would think that you were passing a parameter,
and with no value for that parameter you would see that message.
The attached file shows the approach working, albeit in a vastly simplified database.
Patrick
Q-25131500.mdb
If the table or column name weren't being recognized, then Jet would think that you were passing a parameter,
and with no value for that parameter you would see that message.
The attached file shows the approach working, albeit in a vastly simplified database.
Patrick
Q-25131500.mdb
Something else to try: save that query, and give it a name like qryBrEmails.
Then change the OpenRecordset call to use the name of the query instead of a SQL statement.
Then change the OpenRecordset call to use the name of the query instead of a SQL statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The code for this emailing process automatically sends the emails.
What if I would like to just open the emails and then manually send them like a regular email so I can make modifications?
What if I would like to just open the emails and then manually send them like a regular email so I can make modifications?
Public Function Sendemails()
Dim oLook As Object
Dim oMail As Object
Dim rs As DAO.Recordset
Dim strSql As String
Set oLook = CreateObject("Outlook.Application")
strSql = "SELECT BrEmails FROM email"
Set rs = CurrentDb.OpenRecordset(strSql)
Do Until rs.EOF
Set oMail = oLook.CreateItem(0)
With oMail
.To = rs![BrEmails]
'.CC = "y@z.com"
'.BCC = "z@z.com"
.Subject = "subject"
.Body = "body"
' .Attachments.Add "c:\folder\subfolder\foo.xls"
.Send
End With
rs.MoveNext
Loop
Set oMail = Nothing
Set oLook = Nothing
rs.Close
Set rs = Nothing
End Function
ASKER
Thanks a lot! I'll try it right now.