Solved

VBA Email from Array Access 2010

Posted on 2013-01-21
14
576 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Expert Comment

by:jcrozier21
Comment Utility
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
Comment Utility
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
Comment Utility
This looks great.  I'll test and respond in a bit.  Thanks.
0
 
LVL 1

Author Comment

by:CFMI
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This was tricky but it's working.  Thanks to all for your help (especially jcrozier21).
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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 fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now