Automated e-mailing 2

[chunk22]

I apologise for being the novice that I am, I have only been using Access and VBA for a few month. I have a database that needs a report to be e-mailed out automatically, without the user have to click send.

I have Outlook 2003 / Exchange and Access 2003. I have managed to get the e-mail to open but I would like it to send automatically. Please see code below, can anyone help?

[Private Sub cmdSend_Click()
On Error GoTo Err_cmdSend_Click

Dim stDocName As String

stDocName = "Skateboarders_rpt"
DoCmd.SendObject acReport, stDocName, , "user@Domain.com", , , "Test", "This is a Test"

Exit_cmdSend_Click:
Exit Sub

Err_cmdSend_Click:
MsgBox Err.Description
Resume Exit_cmdSend_Click]

Thanks

Chunk22

 

[tonyflavell]

Hi there

Try:- DoCmd.SendObject acReport, stDocName, , "user@Domain.com", , , "Test", "This is a Test",False

Tony

 

[HarleyQuinn]

With regard to the Automated sending, how often to you want to accomplish this??

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Please ingnore my last post, I had completely misread the OP. Sorry

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Also if you include an OutPut format it won't ask you to specify that before the mail is sent e.g.

DoCmd.SendObject acReport, stDocName,[red]"HTML"[/red], "user@Domain.com", , , "Test", "This is a Test",False

You will also now get the (rather annoying) Outlook security box for your users to click...

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[tonyflavell]

Harleyquinn is correct.

However I researched this months ago and found the following bit of freeware at:

http://www.contextmagic.com/ftp/

called "Express ClickYes" which my users have been using for 6 months without conflicts or problems. It runs in background and obligingly clicks the "Yes" button whenever the Outlook Security box appears.

Only useful ideas!

Tony

 

[HarleyQuinn]

ClickYes is a useful program. However if used in a business environment it can be (and has been by my company) classed as a potential security risk. This is because it does what it says on the tin basically, it clicks yes to dialog boxes that appear on the screen.

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[tonyflavell]

True, but it can be enabled and disabled using code before and after the event in the latest version. I have found that it only clicks the Outlook security prompt, not other prompts to which "Yes" is one of the answers.

See:-

http://www.contextmagic.com/express-clickyes/

Regards

Tony

 

[HarleyQuinn]

I'll be honest, I was not against using ClickYes (I have in several programs) by opening it in code and then closing it on the close of the application by killing the ClickYes process id (also in code). As long as it is used properly by the users that is fine. However, users are inevitably users...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[tonyflavell]

True....True.....True. Wouldn't our job be easier without them!!!!

Tony

 

[HarleyQuinn]

- I know exactly what you mean...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[chunk22]

Many Many thanks for your replies. I have been on to the web site you suggested and see what you can insert the code to enable the clickyes. Where would I need to post this, again please excuses my ignorance, I'm still learning.

Chunk22

 

[tonyflavell]

Hi there

Open a module or start a new module in Access. Enter the following exactly:-

Private Declare Function RegisterWindowMessage _
        Lib "user32" Alias "RegisterWindowMessageA" _
        (ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" _
        Alias "FindWindowA" (ByVal lpClassName As Any, _
        ByVal lpWindowName As Any) As Long

Private Declare Function SendMessage Lib "user32" _
        Alias "SendMessageA" (ByVal hwnd As Long, _
        ByVal wMsg As Long, ByVal wParam As Long, _
        lParam As Any) As Long

Now change your code to include the enable ClickYes before you send the email and then disable it once sent.

Private Sub cmdSend_Click()
Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long
On Error GoTo Err_cmdSend_Click

uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")

' Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)

' Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)

    Dim stDocName As String

    stDocName = "Skateboarders_rpt"
    DoCmd.SendObject acReport, stDocName, , "user@Domain.com", , , "Test", "This is a Test", True

' Send the message to Suspend ClickYes
Res = SendMessage(wnd, uClickYes, 0, 0)
    
Exit_cmdSend_Click:
    Exit Sub

Err_cmdSend_Click:
    MsgBox Err.Description
    Resume Exit_cmdSend_Click]

End Sub

This should produce the result you want! Let me know how you get on.

Just ideas!

Regards

Tony

 

[a98dsu98sad8sua]

You can also use sendkeys to send the keys ALT + S to send the email when it appears onscreen, this works rather well and isn't visible most of the time!

HTH

Toby

 

[tonyflavell]

I have just seen an error in my code above. It was:-

    stDocName = "Skateboarders_rpt"
    DoCmd.SendObject acReport, stDocName, , "user@Domain.com", , , "Test", "This is a Test", True

It should be:-

    stDocName = "Skateboarders_rpt"
    DoCmd.SendObject acReport, stDocName, , "user@Domain.com", , , "Test", "This is a Test", False

Regards

Tony

 

[chunk22]

I have tried the above and it returns nothing, no action is carried out. I added a watch to the cmdSend() function and it 'can't compile module' any further ideas?

Chunk22

 

[HarleyQuinn]

Paste the code (excluding the private sub... and end sub line into the event procedure of a command button on your form. That should do the trick for you.

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[tonyflavell]

ClickYes must be running in background for it to work!

Tony

 

[chunk22]

So are you saying that first I need to have the code as follows:

'Private Sub cmdSend_Click()- Not included

Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long
On Error GoTo Err_cmdSend_Click

uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")

' Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)

' Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)

Dim stDocName As String

stDocName = "Skateboarders_rpt"
DoCmd.SendObject acReport, stDocName, ".rtf", "ccampbell@theoracle.com", , , "Test", "This is a Test", False

' Send the message to Suspend ClickYes
Res = SendMessage(wnd, uClickYes, 0, 0)

Exit_cmdSend_Click:
Exit Sub

Err_cmdSend_Click:
MsgBox Err.Description
Resume Exit_cmdSend_Click
'End Sub - Not included

And also have the software running?

I'm a little confussed?

 

[HarleyQuinn]

Create a button on your form called Command1.
Go to the event procedure for the click event of command1.
It will look like this in the code window

Private Sub Command1_Click
End Sub

Paste the code you now have (excluding the bits you have marked as - Not Included).
Start your program.
Start ClickYes.
Click Command1 on your form. See what happens.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations