UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Email Using Form And A Query With Parameters, Access 2016    
 
   
wornout
post Feb 7 2018, 11:07 PM
Post#1



Posts: 1,172
Joined: 17-November 13
From: Orewa New Zealand


I have a form with a combobox " Names" 2 text boxs "subject" and "Body" and a button "Send"The combo box row source is 3 columns from a table "ClientID" "[First Name] & " " & [Last Name]" and "email Address" with the criteria as is not null.

I have a query " Bulk email" with
ClientID with criteria set as [Forms]![Email Form]![Names]
Email Address criteria set as Is not null
Name: [First Name] & " " & [Last Name]
[Forms]![Email Form]![Names] with criteria set as Is not null or Is Null and this column is not showing the tick has been removed this is so I can clear the combobox and bring up all the names and email address
I would like to ether pick one name and type in the subject box and type in the body box and send that as an email with my email address in the to box and the combobox name email address in the bcc address or clear the combobox and all names and email address will come up and it sends it to all of them in the bcc address box.
I had some code that worked fine untill I changed it to a query with parameters then it say to few parameters expected 1 I believe that I can not open a Recordset with parameters??? what other way can I do this?
The code I was using is below and debugs on this line Set rstEMail = MyDB.OpenRecordset("Select * From EmailBulk", dbOpenSnapshot, dbOpenForwardOnly). I feel I need to change this part
CODE
'Retrieve all E-Mail Addressess in EmailBulk
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From EmailBulk", dbOpenSnapshot, dbOpenForwardOnly)


Here is the whole code
CODE
Option Compare Database
Private Sub Command2_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset

Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)

'Retrieve all E-Mail Addressess in EmailBulk
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From EmailBulk", dbOpenSnapshot, dbOpenForwardOnly)

With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![Email Address] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------

With oMail
  .To = "m.cliff@xtra.co.nz"
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing;
  .Body = Forms![Email Form].Text0
  .Subject = Forms![Email Form].Text4
    .Display
End With

Set oMail = Nothing
Set oOutlook = Nothing

rstEMail.Close
Set rstEMail = Nothing

End Sub
Go to the top of the page
 
wornout
post Feb 7 2018, 11:36 PM
Post#2



Posts: 1,172
Joined: 17-November 13
From: Orewa New Zealand


Ok I got it to work by changing this
CODE
Dim qdf As QueryDef, prm As Parameter
Set Db = CurrentDb
Set qdf = Db.QueryDefs("EmailBulk")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rstEMail = qdf.OpenRecordset(dbOpenDynaset)


but now I would like to send each email individually so I know I need to do the email some where in the loop but not sure how

so code now
CODE
Private Sub Command2_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim qdf As QueryDef, prm As Parameter
Set Db = CurrentDb
Set qdf = Db.QueryDefs("EmailBulk")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rstEMail = qdf.OpenRecordset(dbOpenDynaset)
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)

'Retrieve all E-Mail Addressess in EmailBulk

With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![Email Address] & ";"
    
      .MoveNext
  Loop
End With
'--------------------------------------------------

With oMail
  .To = "m.cliff@xtra.co.nz"
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing;
  .Body = Forms![Email Form].Text0
  .Subject = Forms![Email Form].Text4
    .Display
End With

Set oMail = Nothing
Set oOutlook = Nothing

rstEMail.Close
Set rstEMail = Nothing

End Sub

This post has been edited by wornout: Feb 7 2018, 11:44 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 09:59 AM