Send object from Access through SMTP server 5

[LLBP]

I can email a query by scheduling a command in scheduled tasks that runs a macro to send object. The only stipulation is that I have to be logged in with Outlook open for it to work. I want to run the task on a server with SMTP installed without having to be logged in and be able to send the message from a certain email address. I have some VB code that will send a message throught the SMTP server but not an attachment. I'm using MS Access 2000 on Windows 2K. Can any one help?

...later post to other forum

I found a solution (if you call it that): I had to use a scheduled task to open a macro, which runs a module, which exports the query results to a file and runs a batch file, the batch file runs a vb script that sends the query results to designated recipients using SMTP server.

THERE MUST BE A BETTER WAY!

The VB script would not run in Access and I couldn't run the .VBS from Access.

LD.vbs file:

Set objMail = CreateObject("CDONTS.NewMail&quot


objmail.to = "x@gaf.com"
objmail.from = "ContactAdmin@gaf.com"
objmail.subject = "Daily Contact Report"
objmail.attachfile "\\burga2ks02 _
\csd\qryContactNeeded-LD.xls"

objmail.send

Set objMail = Nothing

------------------------------------------------------------
Access function:

'Outputs query results to xls and runs a batch file that _
'runs a vbs script to send the file to the appropriate user.


Function SendObject()
On Error GoTo SendObject_Err

If (DCount("*", "qryContactNeeded-LD&quot > 0) Then
DoCmd.OutputTo acQuery, "qryContactNeeded-LD", "MicrosoftExcel(*.xls)", _
"\\burga2ks02\csd\qryContactNeeded-LD.xls", False, ""
Call Shell("e:\customerservicedatabase\LD.bat", 6)
End If

SendObject_Exit:
Exit Function

SendObject_Err:
MsgBox Error$
Resume SendObject_Exit

End Function
------------------------------------------------------
Your help is much appreciated,

Brent

 

[VBslammer]

Why don't you just use the built-in SendObject to send an attached Excel file?

DoCmd.SendObject(acSendQuery, "MyQuery", acFormatXLS, "bigboss@company.net", , , "Here's the spreadsheet", , False)

VBSlammer

Unemployed in Houston, Texas - but accepting stars

as payment for helpful posts

 

[LLBP]

This needs to be done unattended. The SendObject function prompts for Outlook profile if not open already and it sends it from whomever is logged in. It is currently running on a server as a scheduled event.

 

[VBslammer]

Here's another idea. If you can reference this library:

Microsoft CDO for Exchange 2000 Library

Then you can configure and send a CDO message from within Access:

Sub SendCDO()
  Dim email As New CDO.Message
  
  With email
    .AddAttachment "C:\CDOTest.xls"
    .HTMLBody = &quot;<H4>See attached file</H4>&quot;
    .From = &quot;joeblow@ev1.net&quot;
    .To = &quot;bigboss@company.net&quot;
    .Subject = &quot;Here is the spreadsheet&quot;
    
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing&quot;)[/URL] = 2
    
    'Name or IP of Remote SMTP Server
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver&quot;)[/URL] = &quot;smtp.ev1.net&quot;
    
    'Type of authentication, NONE, Basic (Base64 encoded), NTLM
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/authenticate&quot;)[/URL] = cdoBasic
    
    'Your UserID on the SMTP server
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername&quot;)[/URL] = &quot;xxxxxxxxx&quot;
    
    'Your password on the SMTP server
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword&quot;)[/URL] = &quot;xxxxxxxxx&quot;
    
    'Server port (typically 25)
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport&quot;)[/URL] = 25
    
    'Use SSL for the connection (False or True)
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpusessl&quot;)[/URL] = False
    
    'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
    .Configuration.Fields.Item _
    (&quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout&quot;)[/URL] = 60
    
    .Configuration.Fields.Update
    '==End remote SMTP server configuration section==
    
    .Send
  End With
End Sub

VBSlammer

Unemployed in Houston, Texas - but accepting stars

as payment for helpful posts

 

[hwkranger]

This was a helpful post VB.

Randall Vollen
National City Bank Corp.