?
Solved

VBA Email from Array Access 2010

Posted on 2013-01-21
14
Medium Priority
?
591 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 400 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 1200 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Introduction to Processes
Loops Section Overview
Suggested Courses

839 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