VBA Email from Array Access 2010

I have the code below taken from your answers repository.  There are several issues.
1) How do I send the email directly and avoid it being, "placed in Outlook Drafts folder?"
2) If I hard code ".To = ", I'm fine, however the "DLookup" fails.  What am I doing wrong?
3) How do I loop through the "tblEmailAddresses" so
    a) all addressees can be emailed and
    b) each adressee gets the attachment affiliated with its record from "tblEmailAddresses?" I imagine the "DLookup" would also be used here but of course if one failed I'm sure the next will also.  :)


Function TestSend()
DoCmd.SetWarnings False
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
     Set appOutLook = CreateObject("Outlook.Application")
     Set MailOutLook = appOutLook.CreateItem(olMailItem)
Dim ContactEM As String
Dim FirstnameC As String
Dim SourceTable As String
Dim CDb As Database, CRTb As DAO.Recordset
   Set CDb = CurrentDb
   Set CRTb = CDb.OpenRecordset("tblEmailAddresses")
        Dim ExportDir As String
        Dim NewRef

    NewRef = Format(Now(), "mmddyy")
    ExportDir = [Path]

Addressee = DLookup("EmailAddress", CRTb, "e-mail = 'y'")

            Set appOutLook = CreateObject("Outlook.Application")
            Set MailOutLook = appOutLook.CreateItem(olMailItem)
            With MailOutLook
            .BodyFormat = olFormatHTML
            .To = Addressee
            .Subject = "Account Transfer Detail Report"
            .HTMLBody = "User,   " & "<BR>" & "<BR>" & _
            "Please find attached the latest Account Transfer Detail Report.  " & "<BR>" & "<BR>" & _
            "Feel free to contact me or Konrad Berk if you have any questions. " & "<BR>" & "<BR>" & _
            (vbCr & vbLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "Thank you for a prompt response to this matter.") & "<BR>" & "<BR>" & _
            "Joe Shmoe " & "<BR>" & _
            "x4099"
            .Attachments.Add ExportDir & NewRef & "_RA_CR1.pdf"
            .Close 0
            End With
            CRTb.MoveNext
        Set MailOutLook = Nothing
           
DoCmd.SetWarnings True
    Beep
    MsgBox "An email was placed in your Outlook Drafts folder."
   
End Function
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
 
jcrozier21Commented:
Try this for DAO
Dim rst As DAO.Recordset

Set rst = DBEngine(0)(0).OpenRecordset("(SELECT * from tblEmailAddresses where e-mail = 'y')")
    
do while rst.EOF = false
'Email set up code
.To = rst!EmailAddress
'Email set up and send
rst.movenext
loop

rst.Close
Set rst = Nothing

Open in new window


EDIT: However you should be able to use either without harm. Are you getting an error? What is it?
0
 
Patrick MatthewsCommented:
If you want to send it, then replace:

            .Close 0

with:

            .Send

As for sending emails in a loop, please have a look at my article here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

It includes a sample Access file in which I demonstrate how to do just that.
0
 
jcrozier21Commented:
DLookup("EmailAddress", CRTb, "e-mail = 'y'")

Open in new window

Will return multiple results, so you will have to use a recordset, or more specific where clauses.

It seems at first glance to me that you want to loop through all records in tblEmailAddresses that have "e-mail = 'y'" and send an email to the address in the record.

If thats the case I'd go with a structure like:

1. Open recordset containing all records email='y'
2. Do while recordset.EOF = false
3. Set email properties and send email
4. recordset.movenext
5. Loop
6. Notify user of completion.

If you need help with any of those steps let me know :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CFMIFinancial Systems AnalystAuthor Commented:
matthewspatrick,  I got the send vs close idea.  thanks.  I looked at your article, see some of the things you're doing (and know it's good) but am somewhat overwhelmed.  In particular, I see some global statements which look like I'm missing a lot of preliminary work.  Do I really need all that?

jcrozier21, If I need to use a recordset, I thought the recordset defined above was evident.  Didn't I "1. Open CRTb recordset containing all records email='y' as tblEmailAddresses?"
For 2-6, I figure I'll need to accomplish #1 first so the first item in the loop works.
0
 
jcrozier21Commented:
You have
Set CRTb = CDb.OpenRecordset("tblEmailAddresses")

Open in new window

but this does not specify that the 'email' field must equal 'y', so the results are no good. We need to open it with only the records with email='y' so that we can step through each record safely. The most important thing here is that you replace
Addressee = DLookup("EmailAddress", CRTb, "e-mail = 'y'")

Open in new window

with
Addressee = CRTb!EmailAddress

Open in new window


This will refer to the emailaddress of the current record in the recordset we are stepping through (CRTb). Better yet, get rid of Addressee variable altogether and just use CRTb!EmailAddress.
0
 
jcrozier21Commented:
We should have something looking like this
   Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.Open "(SELECT * from tblEmailAddresses where e-mail = 'y')"

do while rst.EOF = false
'Email set up code
.To = rst!EmailAddress
'Email set up and send
rst.movenext
loop

Open in new window

0
 
CFMIFinancial Systems AnalystAuthor Commented:
This looks great.  I'll test and respond in a bit.  Thanks.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
jcrozier21, I'm still working through your code.  

1) Since the database code has been written using DOA, I am trying to make this email event compliant with that.  So now I'm fitting your ADO into DAO.  Can this be done simply?

2) I want to develop this concept further, how can I make this one-to-one "email address-to-report" relationship into a many-to-many?  In other words, if I want to sent multiple attachments IN ONE EMAIL to multiple recipients?
0
 
Luke ChungPresidentCommented:
This is a reference to a commercial email addin product in case you're interested or someone who's looking at this doesn't want to do any programming.

Our Total Access Emailer program, http://www.fmsinc.com/MicrosoftAccess/Email.asp does what you're seeking and will automatically send personalized, individual emails to each recipient using field values in the message and attaching existing files, or filtered reports/data for each person. Multiple reports can be attached as PDF files. They can also be zipped and password protected if you need extra security. It uses SMTP to bypass Outlook and the MAPI security limitations.

A free trial is available: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

The Professional Version includes a VBA interface and royalty-free runtime library if you want to automate and share it with others. Hope this helps.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
With my above comments notwithstanding I'm still working on replacing
     Addressee = DLookup("EmailAddress", CRTb, "e-mail = 'y'") with                    
     with
     Addressee = CRTb!EmailAddress

This sounds funky to me because the 'email' field must equal 'y'.  I'm confused.
0
 
jcrozier21Commented:
I had a day off, sorry for the delay.

We are tossing out Addressee = DLookup("EmailAddress", CRTb, "e-mail = 'y'") because we now have a recordset that only contains records with e-mail='y', and now all we need to do is refer to the EmailAddress field in the current record of the recordset.

When we define the recordset, we set the rules at that point.

Once we have done that, we step through each record with a loop, completing actions with reference to the current record in the recordset, then at the end of the loop we say 'NEXT!' and we do it all again with the NEXT record in the recordset.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
rst.Open "(SELECT * from tblEmailAddresses where e-mail = 'y')"  That is fulfilling your recordset that only contains records with e-mail='y'.  I get that.  The problem may revolve around ADO vs DAO.  Am I off base with this?  If not, I think some code within this db might be DAO.  I know clearly I have a DAO reference library installed.  Can I switch up without harm?
0
 
CFMIFinancial Systems AnalystAuthor Commented:
We're getting close.

Error on
Set rst = DBEngine(0)(0).OpenRecordset("(SELECT * from tblEmailAddresses where e-mail = 'y')")

Run-time Error '3061':
Too few parameters. Expected 2.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
This was tricky but it's working.  Thanks to all for your help (especially jcrozier21).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.