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
> Code I Have Send S To Email Address Altogether, Access 2016    
 
   
wornout
post Feb 8 2018, 01:45 AM
Post#1



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


The code below sends them as bulk to the Bcc I want separate emails
CODE
rivate 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
Go to the top of the page
 
JonSmith
post Feb 8 2018, 05:31 AM
Post#2



Posts: 3,443
Joined: 19-October 10



Oke, so a good route is to separate this out into two procedures. Have one procedure just do the code for sending the mail, give it an argument for strEmailAddress so you can pass an email address to it that it uses to send.

Then in the other sub do the looping through the email addresses like you already have, but instead of building a string of emails have it call the Send Mail proc you wrote above each time passing just the email address it found each time.

JS
Go to the top of the page
 
DanielPineault
post Feb 8 2018, 05:31 AM
Post#3


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



In that case, you're going to have to use a reusable e-mail function, such as http://www.devhut.net/2010/09/03/VBA-send-...ook-automation/ , and use your With rstEMail loop to call it for each e-mail address separately.

With the above function in place, you could then do something along the lines of
CODE
Private Sub Command2_Click()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rstEMail              As DAO.Recordset
    Dim qdf                   As DAO.QueryDef
    Dim prm                   As DAO.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)
    With rstEMail
        If .RecordCount <> 0 Then
            Do While Not .EOF
                'Send an e-mail to each record
                Call SendHTMLEmail(![Email Address], Forms![Email Form].Text4, , Forms![Email Form].Text0)
                .MoveNext
            Loop
        End If
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not rstEMail Is Nothing Then
        rstEMail.Close
        Set rstEMail = Nothing
    End If
    If Not prm Is Nothing Then Set prm = Nothing
    If Not qdf Is Nothing Then Set qdf = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: " & sModName & "\Command2_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub


As a side note, a few recommendations
  • Give your control meaningful name
  • Do not use spaces or any other special characters when naming things (objects, controls, ...)
  • Include error handling all your VBA procedures

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
wornout
post Feb 8 2018, 02:32 PM
Post#4



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


I have no idea what I am doing but here is what I have done.
I went to the link and copied the code and put it in a module. I then copied what you have given me and put it under a test button.
It debugs on
Call SendHTMLEmail(![Email Address], Forms![Email Form].Text4, , Forms![Email Form].Text0)
and says argument not an option
I have looked at heaps of code to do this and it is confusing and nothing seems to use the
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
bit so I am struggling to adapt anything
Go to the top of the page
 
DanielPineault
post Feb 8 2018, 04:43 PM
Post#5


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Try
CODE
Call SendHTMLEmail(![Email Address], Forms![Email Form].Text4, Forms![Email Form].Text0, True)


You can change the True to False if you don't want to first review the e-mail before it is sent.
You might also want to first check that you have a valid e-mail address before making the call.

The whole param section is for when your query includes parameters that need to be evaluated for it to run. One way or another, it doesn't hurt to have it there even if you don't have any, but it ensures that if you do, it all runs smoothly.


Lastly, you really have to give your control's meaningful names. Text0 and Text4 don't help you when it comes to coding and debugging! txt_EmailSubject and txt_EmailBody are clear and easy to understand, debug...Think about how much easier your code would be to understand. Think about when you need to look back on this code in 6 months how much easier your life would be to have names that speak to you.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th June 2018 - 04:44 AM