Emailing Documents via Access

[hcabs99]

Hi All

I have just built a database which i need to send various files via email to users. I've used functionality which i already have built in another database (and works fine) but for some reason, it is not working on this one

' SendSites
'
'------------------------------------------------------------
Function SendSites()

Set MySet = New ADODB.Recordset
MySet.Open "WandTEmail(ToSend)", CurrentProject.Connection, adOpenStatic

Do Until MySet.EOF
[Forms]![SendData].[QBucket].Value = MySet![Bucket]
[Forms]![SendData].[QEmail].Value = MySet!




DoCmd.SendObject acQuery, "SendW&TEmail", "MicrosoftExcel(*.xls)", [Forms]![SendData].[QEmail], "", "", "InsertEmail Text", False, ""


MySet.MoveNext
Loop

End Function

I have built a form called SendData and created two labels called QBucket & QEmail
I have built a Table called WandTEmail(ToSend) containing Bucket & Email
I have built a query called endW&TEmai which is linked to the form using the field Bucket and the following criteria [Forms]![SendData].[QBucket]

When I run this i get the following error

Run - time error '-2147217900 (8004oe14)':
Invalid SQL Statement; expected 'DELETE' INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'


Any ideas folks? I have checked and double checked everything and it all seems to be set up correctly, but i'm sure i've missed something simple!

 

[Trevor G]

Im not sure what if anything is wrong with your statement, but if it helps you can download a database which emails from my website. I posted some instructions to someone who wanted to use it, so I have placed them below: http://www.wuit.co.uk/downloads.htm The download should be the first one you see. I hope it can assist you.


Here is a sample database which uses a form and looks at query, see if this can help you. The following information was created to help someone else with regards sending emails.

I created a simple database which will look at entries and anything that has a status of “over due” will send an individual email to the person who is assigned as long as you have an email address of course.

I have attached the code to a form so when you open the database open the form “frmEmailSample” the code is behind the command button, what you will have to be aware of is as follows:

In your database you will have to set the reference in VBA to use Outlooks code, so use Alt & F11 then go to the Tools Menu and selected references, ensure that Microsoft DAO 3.6 and Microsoft Outlook Object Library have been ticked.

That will allow the code to run when you copy it across, but you will obviously have a little work to do yourself ! I am using 2 text boxes on the form to allow you to send a topic and also the subject itself, you can amend them to be the field names you want to use. If you want the code to run from when you open the database you should copy the code into a New Module sheet (again in VBA go to Insert and Module, copy the code in and change the Private Sub at the top to a FUNCTION like this)

Function SendEmail ()
Code goes in between

End Function

Lastly for now, although this will send emails into outlook, you will receive a warning you are about to send an email, this will happen repeatedly which is very annoying so to get around this you can download some third party software (Free and safe) called YesNo here is the link, (I use this a lot in many different companies I develop for).

 

[hcabs99]

Hi Trevor

Many thanks for your feedback, As it happens i was already using the datapig technology code to do all this, have used it before with great success, but on this database, its not happening!

I've managed to drill down to where the error seems to be

It is on the first line below and if i hover over the MySet![Bucket] Section i get the Invalid SQL Statement message as i mentioned in my first post

[Forms]![SendFile].[QBucket].Value = MySet![Bucket]
[Forms]![SendFile].[QEmail].Value = MySet!

If i run the query from below (DeliveryATPSend) manually it doesn't return any data

DoCmd.SendObject acQuery, "DeliveryATPSend"

However If i remove the criteria "[Forms]![SendData].[QBucket]" from the query, i get the results i expected returned. So my assumption was that i had created something on the form incorrectly as it doesnt seem to be able to match Qbucket with Bucket because if it did, it should return the same data if i run the query manually .

On the form "SendFile" I have created 2 Text Boxes and named them QBucket & QEmail.

Help! I am completely stuck!

 

[revans]

"[Forms]![SendData].[QBucket]" is a property of the SendData form. If the form is bound the property might also be a bound field.

"[Forms]![SendData]![QBucket]" is a field on the SendData form. This field may be bound or unbound.

Your query is looking for a property of the form which might not exist if the field is not a bound field.

I don't know if that will fix your problem or help you in any way. I just know that sometimes I get hung up on the syntax that works perfectly fine behind a form when it's included in a query.

hth,

Rich

 

[hcabs99]

"[Forms]![SendData].[QBucket]" is a property of the SendData form. If the form is bound the property might also be a bound field.

"[Forms]![SendData]![QBucket]" is a field on the SendData form. This field may be bound or unbound.

Your query is looking for a property of the form which might not exist if the field is not a bound field.

I don't know if that will fix your problem or help you in any way. I just know that sometimes I get hung up on the syntax that works perfectly fine behind a form when it's included in a query.

hth,

Rich

Hi Rich

I've finally sussed it, it was because of the following command

MySet.Open "WandTEmail(ToSend)", CurrentProject.Connection, adOpenStatic

It didnt like having brackets in the table name. I renamed the table to remove the brackets, amended this code and it worked instantly! Very annoying that it was something so simple and it beat me for so long!

Thanks for all your help

Cheers

Phil

 

[SydneyGeek]

Maybe the brackets in the table name got Access looking for a function with parameters...

I stay away from any punctuation and higher ASCII characters in table and field names -- especially brackets, braces, apostrophes, because they cause all sorts of random parsing and runtime errors. The only one I use is the underscore, instead of a space between words.

Denis