data stored in Access export to Email

Question

Hello Access Friends,

My emailaddresses arestored inan Accessdatabase(field[E-mail]and I want toautomaticallyexport them as astring (the addresseswith a comma)to Eudora(myemail program) inthe TO. Various methods have been tried,but failed! Or the string was composed of "x"times the same address or an error 2105 occurred (when using theDoCmd.GoToRecord, , acGoTo, t orDoCmd.GoToRecord ,  ,acNext) saying "could not locate the specified record "or error 2499" you cannot use the action GoToRecord with an object in design mode "

  Who can recommend the right solution

  Many thanks in advance  Who can recommend the right solution

  Many thanks in advance

WiBoAntwerp

Answer 1

1. Which methods have been tried?

2. Why are you using the DoCmd.GoToRecord command? Have you tried the DoCmd.SendObjects command? It might be more applicable.

3. Were you attempting to run code or macro commands in form design mode?

Answer 2

Hallo Lawrence,

I made the project in VBcode, and tried several commands without success.

First I made a form with all the data in it, then a query that selected the data with email addresses, and left the possibility with a y/n field ([actiefEmail]) to change the selection for a collective mail.

I intended to link al the email addresses together in a long string separated by comma’s to use in the TO       From EUDORA.

Therefore I used (tried) all the known possibilities like DoCmd.SelectObject acForm, DoCmd.SelectObject acQuery, DoCmd.SelectObject, etc …

DoCmd.GoToRecord , , acGoTo …, DoCmd.GoToRecord , , acNext …

Even tried the put the addresses in an array …

And got somewhere like

   For t = 1 to n                        ‘n = number of records

       If [ActiefEmail] = True Then

            NaamAan = NaamAan & "," & Me![Mail1]

or

            EmailArray(t) = Me![Mail1]

        End If

        t = t + 1

    Next

No success, or I received only t-times the first emailaddress separated by comma’s or I got a number of different errors (like 2499 and 2105) according to the combinations I made to succeed!

I hope you understand my problem, and appreciate your help!

THANKS

WiBoAntwerp

Answer 3

Before we go further, my previous post should have said use the DoCmd.SendObject command not DoCmd.SendObjects.

Please research the DoCmd.SendObject command. Not the DoCmd.SelectObject command as you indicated in your post. VBA Help will give you the various parameters. You can also search in this forum or on the web and find some answers. SendObject, not SelectObject.

Answer 4

Hello Lawrence

There is no problem what so ever with the DoCmd.SendObject command. That’s running all right.

As I explained before, my only problem is to create a long string with all the email addresses separated by comma.

I made a form based on a query that selects all the records that have an email address, no problem, but which statement and what parameters do I use to create that string?

The code, called by pressing a button in the form with the query still active:

    NaamAan = [Mail1]

    For t = 1 To n

        DoCmd.GoToRecord , , acGoTo, t

        If [ActiefEmail] = -1 Then

            NaamAan = NaamAan & "," & Me![Mail1]

        End If

        t = t + 1

    Next t

returns only  the first email address and

DoCmd.GoToRecord , , acNext

Gives an error (nr 2105)

Do you have any suggestion in resolving that problem?

Most obliged, and thank you

WiBoAntwerp

Answer 5

Hi WiBoAntwerp

I hope the following may be what you are looking for.

The following loops through the recordset picks up each email address for nurses in Northwest region i was a bit lazy with this and made a query specific to the NorthWest, i could have applied a flag in a query that held all nurses from all regions  anyway i hope you get something out of it.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmail As String
Dim strLinkCriteria As String
    Me!SearchNurseRegion.Form.Filter = "[NorthWest] =true"
    Me!SearchNurseRegion.Form.FilterOn = True
   
   
Set db = CurrentDb
Set rs = db.OpenRecordset("qryEmailNorthWest", dbOpenDynaset)
With rs
    Do While Not .EOF
        strEmail = strEmail & .Fields("HomeEmail") & ";"
        .MoveNext
    Loop
    .Close
End With
    strEmail = Left(strEmail, Len(strEmail) - 1)
   
    DoCmd.SendObject _
    , _
    , _
    , _
    , _
    , ("" & strEmail) _
    , _
    , _
    , _
    True
Trap:
If Error = 2501 Then
End If

---

David

Answer 6

Hi Dave,

Thanks a lot for your reaction, which was very useful!

I reprogrammed my project in the way you suggested, but I was not as successful as I thought I would be. (Don’t misunderstand, not your fault, I don’t blame you!)

I work with Access 2003 and don’t think the command     Dim rs As DAO.Recordset          is known (resulting in compilation error )      so I used  

 Dim rs As Recordset .

I think that is the reason for (next) error 13 (types don’t match) on line

    Set rs = db.OpenRecordset(stQueryName),  the parameter  dbOpenDynaset gives compilation error

Then I tried     With rs            and there occurred error 81!

And .MoveNext gave error 421.

So you see, I have to study the codes again but still I’m grateful for you help and I hope you will suggest more things that will lead to a solution!

Thanks,

 WiBoAntwerp

Answer 7

hI WiBoAntwerp

the reason you are getting the error is you need to assign DAO as a reference..

this code is used in a 2003 access database

Setps to assign a reference

1. open the database

2. go into design view of the form

3. open up the vb code window

4. goto tools - Refrences

5. search for DAO 3.6 or higher

6. tick close - reopen database

I have attached a image...

let me know how you go

---

David

Answer 8

Hi David,

You gave me a golden tip! I didn’t know about assigning DAO as a reference (I’m not so good in system software at all). But most of the errors are gone now! Thanks to you!

Only the final solution is absent, have a look at the VBcode :

Set db = CurrentDb

 Set rs = db.OpenRecordset(stQueryName, dbOpenDynaset)

 With rs

         .MoveFirst

            Do While Not .EOF

            If Me![ActiefEmail] = -1 Then

                NamenBCC = NamenBCC & "," &  Me![Mail1]

            End If

            .MoveNext

Loop

 End With

    DoCmd.SendObject , "", , stAan, , NamenBCC, stOnderwerp, stBericht, True

The result is that the first address in the database is repeated all the way long, conclusion  the move statement does not work.

Can you see the reason?

I also tried the other way:

        For t = 1 To n

            If Me![ActiefEmail] = -1 Then

                NamenBCC = NamenBCC & "," &  Me![Mail1]

            End If

            DoCmd.GoToRecord  acGoto, t

        Next t

and also with

   DoCmd.GoToRecord  acNext

Without success !

Hopefully you see the mistake

Thanks a lot anyway, I appreciate your remarks

WiBoAntwerp

Answer 9

Hi,

I am not sure what you are trying

the following Code works perfect... it looks at the query where there is a column name "HomeEmail" yours is (ActiefEmail) it loops through picks up all the email address and applys them to the bcc column of the outlook email

you can apply a fixed email address into the To: of the Docmd as shown further down

If some people are active email and some are not then

make your record set

strSQL = "Select * from qryName Where  qryName.[ActiefEmail]= -1"

Set db = CurrentDb
Set rs = db.OpenRecordset("qryEmailNorthWest", dbOpenDynaset)
With rs
    Do While Not .EOF
        strEmail = strEmail & .Fields("ActiefEmail") & ";"
        .MoveNext
    Loop
    .Close
End With
    strEmail = Left(strEmail, Len(strEmail) - 1)
   
    DoCmd.SendObject _
    , _
    , _
    , _
    , _
    , ("" & strEmail) _
    , _
    , _
    , _
    True
Trap:
If Error = 2501 Then
End If

---

Are you saying that you want to add 1 email in the To: and the remaining emails in BCC ?

If so

the Docmd should help here

 DoCmd.SendObject _
    , _
    , _
    , _ (This where you can add a default email like [e-mail])
    , _
    , ("" & strEmail) _ (This is the BCC of your email)
    , _
    , _
    , _

---

David

Answer 10

Hi again David,

I thick we go in the good direction; I managed to have several results by applying you suggestions depending on the position of the lines.

I use my own address in the TO and put the addresses to whom I want to mail to in BCC because of privacy reasons.

Still I don’t understand very well every construction, it’s quit new to me. I don’t find many explanations in the manuals.

Where do I place (before or after which statement?)

strSQL = "Select * from stQueryNameWhere  stQueryName.[ActiefEmail]= -1"

and what is the meaning of:  make your record set ?

 My code looks now like this:

  Set db = CurrentDb

   Set rs = db.OpenRecordset(stQueryName, dbOpenDynaset)

   strSQL = "Select * from stQueryName Where stQueryName.[ActiefEmail]= -1"

        With rs

        Do While Not .EOF

            NamenBCC = NamenBCC & .Fields("ActiefEmail") & ","

            .MoveNext

        <place>Loop</place>

        .Close

    End With

But the statement .Fields("ActiefEmail") returns “true” so after the statements  .MoveNext and .Loop the NamenBCC is  “ true,true,true,true,……” and it should be the string of (in my case) 61 ActiefEmails. The number is correct!

Can I use your knowledge again to find the final solution? Thanks in advance,

Most obliged,

WiBoAntwerp

Answer 11

Ok

I can only think at this stage that your NamenBCC is not defined correctly

it should be

Dim NamenBCC As String...meaning that your email address in the column (field) ActiefEmail back at the source table is a datatype of text

at the top of your code it should read

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NamenBCC As String

and what is the meaning of:  make your record set ?

means open a set of records in memory and make the fields available to me in this module

as below

Set rs = db.OpenRecordset(stQueryName, dbOpenDynaset)

---

David

Answer 12

Hello David, 

Sorry but I’m not yet satisfied with my results, although I did just what you said to do, the Dim-statements are correct, the code you sent me is very well studied!

I rather think it’s the path to the query, the program doesn’t find!

The application is part of a huge project and is made of a main form with two sub forms in it, one with the list of names and the possibility to inactivate the emailaddress, created by a query “qryMailtoInvullen”. The button to proceed to formation of the string is on the main form.

In the second sub form some more choices (still to program).

During the tryout of one of the possible solutions I got the error 3078, file stQueryName not found! That was new, so I think that the problem situates there.

And I got an error 91 to, saying that there is no block or object variable defined.

These are my lines as so far:

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL, stQueryName, ActiefEmail As String

    Dim NamenBCC,  stBericht, stOnderwerp, stAan As String

   

    stAan = “my mailingaddress”                               ‘ to:

    stQueryName = "qryMailtoInvullen"

    stOnderwerp = "………………………..                          ’subject:

    stBericht = "………………………………………”                ‘body

    DoCmd.SetWarnings False

    Set db = CurrentDb

    strSQL = “Select * stQueryName where stQueryName![ActiefEmail]= -1"

  

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

               

    With rs

        Do While Not .EOF

            NamenBCC = NamenBCC & .Fields("ActiefEmail") & ","

            .MoveNext

        <place>Loop</place>

        .Close

    End With

   

    NamenBCC = Left(NamenBCC, Len(NamenBCC) - 1)

   

    DoCmd.SendObject , "", , stAan, , NamenBCC, stOnderwerp, stBericht, True

Or do I have to add the path like:

             [Forms]![frmStuurEmailCollectief]![sfrmEmailCriterium].[form]![ActiefEmail]

Can you fInd any mistakes?

Best regards, and many thanks for your input, I realy appreciate it!

   WiBoAntwerp                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

Answer 13

Hi,

Firstly

is stQueryName the name of your query

if not replace stQueryName with your qryname

replace as this

strSQL = "Select * from qryMailtoInvullen Where  qryMailtoInvullen.[ActiefEmail]= -1"

Set db = CurrentDb
Set rs = db.OpenRecordset("strSQL", dbOpenDynaset)

---

David

Answer 14

Hi David,

Late last night I discovered that the line

  strSQL = “Select * stQueryName where stQueryName![ActiefEmail]= -1"

Should  be:

strSQL = "Select * from " & stQueryName & " where " & stQueryName & ".[ActiefEmail]= -1"

but  still gives ‘ True, True, True, True, True, True, True, ……’ as a result.

and the line

Set rs = db.OpenRecordset("strSQL", dbOpenDynaset)

Gives error 3078: File strSQL not found

The solution is probably to find in the statement “Select  * from … where …. “

I will try to look further in one or the other manual.

What is in your opinion the right the statement?

Kind regards

WiBoAntwerp

Answer 15

Hi David,

I've got it!!! I‘ve got it!!!

The crucial line is :

            NamenBCC = NamenBCC & .Fields("mail1") & ","                             'like i alredy did some mails ago but in a wrong way

And not

            NamenBCC = NamenBCC & .Fields("ActiefEmail") & ","

You set me on the wright track with the Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) statement and the sentence

Make your record set

strSQL = “Select * from qryName where  qryName.[ActiefEmail]= -1”

I am very pleased with that result and I’m very grateful to you!

You have been a great help!

WiBoAntwerp

Answer 16

You are very welcome

This forum has been a great help to me as well, so it is good to give back

Regards

---

David