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
> Emailing From Form And Query, Access 2016    
 
   
wornout
post Feb 7 2018, 07:19 PM
Post#1



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


Oh dear I have put this under reports was supposed to be under forms and I dont know how to change it

I have a form called Email Form on the form I have a combo box " Names" and 2 text boxs " Subject" and "Body" I have 1 button " Send" The combo box row source is from the table clients and has ClientID and [First Name] & " " [Last Name]

I have a query "BulkEmail"
it has 4 columns ClientID, Email Address, Name: [First Name] & " " & [Last Name], [Forms]![Email Form]![Names] ( this one is not ticked so does not show)
in criteira for Email Address I have is Not Null
in criteira for Client ID I have [Forms]![Email Form]![Names]
in criteria for the column thats not ticked I have is null and next line down is not null
This is so I can clear the combo box and the query brings up all clients with email address

Now I tested the below code with just a text box and typing in a name and it worked but now I have put a combo box in there it keeps de bugging saying to few parameters expected 1 and highlights
Set rstEMail = MyDB.OpenRecordset("Select * From [EmailBulk]", dbOpenSnapshot, dbOpenForwardOnly)
and I have no idea why The query runs correctly

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)


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].Body
  .Subject = Forms![Email Form].Subject
    .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, 07:27 PM
Go to the top of the page
 
Doug Steele
post Feb 7 2018, 07:35 PM
Post#2


UtterAccess VIP
Posts: 22,004
Joined: 8-January 07
From: St. Catharines, ON (Canada)


IIRC, you cannot use a parameter like [Forms]![Email Form]![Names] with OpenRecordset unless you resolve the parameter.
Go to the top of the page
 
wornout
post Feb 7 2018, 07:37 PM
Post#3



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


I am not sure what you mean.
I have run the code from the button when query is closed and when it is open I still get the same error
Is that what you mean
Go to the top of the page
 
wornout
post Feb 7 2018, 08:32 PM
Post#4



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


Is there a better code I can use?
Go to the top of the page
 
Doug Steele
post Feb 8 2018, 08:43 AM
Post#5


UtterAccess VIP
Posts: 22,004
Joined: 8-January 07
From: St. Catharines, ON (Canada)


It doesn't matter whether or not the query is already open. When you go to open the recordset, it's automatically going to assume it isn't open. (This is a good thing: the data in the underlying table could have changed since you opened the query, and by assuming it has to rerun the query, you're guaranteed the most current data)

I have to make an assumption here. In your description, you said I have a query "BulkEmail", but in your code, you've got Set rstEMail = MyDB.OpenRecordset("Select * From [EmailBulk]", dbOpenSnapshot, dbOpenForwardOnly). In what follows, I assume the query's actually EmailBulk.

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 qdfEMail As DAO.QueryDef
Dim prmEMail As DAO.Parameter
  
  Set oOutlook = CreateObject("Outlook.Application")
  Set oMail = oOutlook.CreateItem(0)
  
  Set MyDB = CurrentDb
  Set qdfEMail = MyDB.QueryDefs("EMailBulk")
  For Each prmEMail In qdfEMail.Parameters  
    prmEMail = Eval(prmEMail.Name)
  Next prmEMail
  Set rstEMail = qdfEMail.OpenRecordset(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].Body
    .Subject = Forms![Email Form].Subject
    .Display
  End With
  
  Set oMail = Nothing
  Set oOutlook = Nothing
  
  Set prmEMail = Nothing
  Set qdfEMail = Nothing
  rstEMail.Close
  Set rstEMail = Nothing
  Set MyDB = Nothing

End Sub


BTW, two comments about your code.
  1. Do yourself a HUGE favour, and name all your controls. Seeing Private Sub cmdSendEmail_Click() is far more meaningful than Private Sub Command2_Click()
  2. From the fact that you only have Option Compare Database at the top of your code, I'm assuming you haven't told VBA to insist that you've declared all variables. While it may seem like a pain to have to, let me assure you it can save hours of debugging time when you've inadvertently mistyped a variable name in your code!

For the second point, while in the VB Editor, select Options from the Tools menu. On the Editor tab, ensure Require Variable Declaration is checked. This will cause the line Option Explicit to be written at the top of each new module. You will, however, have to go and add it manually to all existing modules.

Attached File  Option_Explicit.jpg ( 44.66K )Number of downloads: 0
Go to the top of the page
 
wornout
post Feb 8 2018, 02:59 PM
Post#6



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


Its now been decided to send them as separate emails but all at once.
so my combo box "Names" I can pick 1 person and query just brings up that 1 person or if I leave blank it brings up all people with email address.
So this code will be wrong as it puts them all in BCC.
I have been looking on line for a suitable code but I can not find anything that uses the query def part for my query.
Can you help?
Go to the top of the page
 
wornout
post Feb 8 2018, 03:29 PM
Post#7



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


Ok I have this code below

My button is not named as it is only a test button anyway
this debugs on
Set rst = myQueryDef.OpenRecordset(strQuery, dbOpenDynaset) and says Run time error 3421 Data type conversion error


CODE
Private Sub Command9_Click()

   Dim strConn As String
Dim myQueryDef As QueryDef
Dim rst As DAO.Recordset
Dim oOApp_001 As Outlook.Application
Dim oOApp_001_B As Outlook.Application
Dim oOMail_001 As Outlook.MailItem
Dim eTotal As Long
Dim distro As String
Dim i As Long
Dim strQuery As String


Set oOApp_001 = CreateObject("Outlook.Application")
Set oOMail_001 = oOApp_001.CreateItem(olMailItem)

'q below just calls all recs from table with emails

strQuery = "EmailBulk"
Set myQueryDef = CurrentDb.QueryDefs("EmailBulk")
Set rst = myQueryDef.OpenRecordset(strQuery, dbOpenDynaset)

'This DCOUNT will count the number of records and put it in eTotal
eTotal = DCount("[EMail Address]", "myQueryDef")

'Sets the email distro to blank
distro = ""

'Starts a for loop for the number of email addresses in the table
For i = 1 To eTotal
'concats each name together with;
distro = distro & ";" & rst.Fields("[EMail Address]")
'In the loop, need to move to the next record to concat email address
'If trys to move pass last rec, then cause error, so checked for last rec before moving
If i <> eTotal Then
rst.MoveNext
End If

Next i

With oOMail_001
'now use the variable disto with all the email names in the to field of outlook
.To = distro
.Body = "Please find attached."
.Display
End With


rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Go to the top of the page
 
wornout
post Feb 8 2018, 03:44 PM
Post#8



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


Ok maybe I can by pass the query and do the query in VBA but I dont know how the query csql is
SELECT Client.ClientID, Client.[Email Address], [First Name] & " " & [Last Name] AS Name, Client.[First Name]
FROM Client
WHERE (((Client.[Email Address]) Is Not Null) AND (([Forms]![Email Form]![Combo6]) Is Null)) OR (((Client.ClientID)=[Forms]![Email Form]![Combo6]) AND (([Forms]![Email Form]![Combo6]) Is Not Null));
how do I change that for VBA then I could use a common email code
Go to the top of the page
 
Doug Steele
post Feb 8 2018, 07:37 PM
Post#9


UtterAccess VIP
Posts: 22,004
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Easiest would be

CODE
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
  
  If IsNull(Forms("Email Form")!Combo6) Then
    strSQL = "SELECT Client.ClientID, Client.[Email Address], [First Name] & ' ' & [Last Name] AS Name, Client.[First Name] " & _
      "FROM Client " & _
      "WHERE Client.ClientID=" & [Forms]![Email Form]![Combo6]
  Else
    strSQL = "SELECT Client.ClientID, Client.[Email Address], [First Name] & ' ' & [Last Name] AS Name, Client.[First Name] " & _
      "FROM Client " & _
      "WHERE Client.[Email Address] Is Not Null"
  End If
  
  Set db = CurrentDb
  Set rstEMail = db.OpenRecordset(strSQL, dbOpenSnapshot, dbOpenForwardOnly)

My previous comment about naming controls applies to the combo box too. While you may know that Combo6 contains clients, will someone else who has to maintain the database. And while you may know it today, will you remember 3 months from now when you need to do maintenance again?

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2018 - 06:51 AM