Solved

VBA Email from Array Access 2010

Posted on 2013-01-21
14
582 Views
Last Modified: 2013-02-24
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
0
Comment
Question by:CFMI
14 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 38803095
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
 
LVL 3

Expert Comment

by:jcrozier21
ID: 38803100
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
 
LVL 1

Author Comment

by:CFMI
ID: 38803183
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:jcrozier21
ID: 38803263
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
 
LVL 3

Expert Comment

by:jcrozier21
ID: 38803279
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
 
LVL 1

Author Comment

by:CFMI
ID: 38803300
This looks great.  I'll test and respond in a bit.  Thanks.
0
 
LVL 1

Author Comment

by:CFMI
ID: 38805754
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
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 100 total points
ID: 38805824
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
 
LVL 1

Author Comment

by:CFMI
ID: 38806175
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
 
LVL 3

Expert Comment

by:jcrozier21
ID: 38811958
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
 
LVL 1

Author Comment

by:CFMI
ID: 38812568
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
 
LVL 3

Accepted Solution

by:
jcrozier21 earned 300 total points
ID: 38812684
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
 
LVL 1

Author Comment

by:CFMI
ID: 38814637
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
 
LVL 1

Author Closing Comment

by:CFMI
ID: 38923406
This was tricky but it's working.  Thanks to all for your help (especially jcrozier21).
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question