Email .txt files from access Module

[sherylnance]

Hi All,

I am creating individual files from my access database to be distibuted to 75 different people. I have the file creation automated and would like to incorporate the email distribution in this process. Does anyone have a suggestion as to how I should go about doing this? I am using Access 2003 and Outlook 2003.

psuedo code: (access module)

locNme = select location_name from location
EmailRcp = Select emailAddr from location where location name = locNme
loop
build file
email file
end loop


Any thoughts?

Thanks in advance for every suggestion!

 

[CEH]

Have you looked at the "SendObject" method?
DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
I use this on several apps... On one I replace the "To" with the results of a function that pulls email addresses from a table. There are about ten or twelve I mail to at any giving time. I have a form to add or edit the email list. Works well.

 

[MarkK]

Here's free code from Microsoft. Sends mail directly without the need to pipe things through Outlook.

Public Sub SendMail()
   Const cdoSendUsingPort = 2
   Dim iMsg
   Dim iConf
   
   Set iMsg = CreateObject("CDO.Message")
   Set iConf = CreateObject("CDO.Configuration")
   
   ' Set up various fields in the configuration object
   With iConf.Fields
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YourSMTPServerHere"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 30
       .Update
   End With
   
   ' Apply the settings to the message.
   With iMsg
       Set .Configuration = iConf
       .To = "<you@yourmail.com>"
       .From = "<me@mymail.com>"
       .Subject = "This is a test CDOSYS message (Sent via Port 25)"
       .TextBody = "Your text body here"
       .AddAttachment "c:\YourFileHere.txt"
       .Send
   End With
   
   ' Clean up variables.
   Set iMsg = Nothing
   Set iConf = Nothing
               
End Sub

I've referenced "Microsoft CDO For Exchange 2000 Library" in Windows XP. Check out more details in the object browser.

 

[CEH]

Lagbolt.....
On the "iMsg.To" portion....... Does this require both comma and <> around email addresses when using more then one or just comma's?

 

[MarkK]

Looks like a comma "," delimiter between addresses in the To, CC, and BCC fields, but there seems to be a lot of latitude assigning strings to these properties.
The actual format of a single address is converted to

"Test Data" <test@data.com>

If you assign "<test@data.ca>" to the Msg.To property it gets converted to

"test@data.ca" <test@data.ca>

If you make this assignment

iMsg.To = "you@yourmail.com;him@hismail.uk"

The property value becomes

"you@yourmail.com" <you@yourmail.com>, "him@hismail.uk" <him@hismail.uk>

 

[CEH]

Looks like I'll have to play with it and see... I have one I use with the SendObject method that places the results of a function (that gathers emails from a table and adds the comma's) in the "To"..... This looks like it might work well too... Maybe better. I'll post it if it does.

 

[CEH]

Well, tried it....... Getting error message........
Run-time error ' -2147220975 (80040211)':
"Meassage could not be sent to SMTP server transport error code was 0x80040217 The server response was not available."

Any ideas?

 

[MarkK]

This line:

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YourSMTPServerHere"

Have you changed it to your SMTP server?

 

[Moniker]

Here's how to do it by going through Outlook, which provides a lot more flexibility.

 

[CEH]

Yep, changed it. Was at work so I tried it also from home. Stops in the same place on "Debug" highlights on ".Send"