?
Solved

VBA Email from Array Access 2010

Posted on 2013-01-21
14
Medium Priority
?
586 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
Make the most of your online learning experience.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

752 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