Emailing from Access

[andrewrubie]

Hi All,

I currently develop/maintain a simple database for a user which
maintains information on job
candidates. The user(s) currently send emails to the candidates in
Outlook and copy and
paste the message body into a 'general archive' memo field in the
candidates record form.
They would like to avoid having to do the copying and be able to write
the email in and send
it from the access database and have the database automatically archive
the body and approp'
details from the email straight into the memo field.
I've read many threads in google groups plus Tony Teows faq and would
just like a fresh
opinion. I'm not afraid of extensive VBA if it'll work. How to manage
attachments?

Some questions
1) CDONTS: could it be done this way? I've used CDONTS a little in ASP.
But what about
adding attachments?

2) SEND OBJECT: I can't find any good code and at any rate Tony's FAQ
says it's extremely
limited.

3) Outlook: Might it be easier to hook into Outlook from Access, the
user fires up Outlook
from Access, completes the email fields, it sends and Access somehow
retrieves the email
field values from Outlook and archives them?

Any help greatly appreciated,

Regards,

Andy.

 

[Guest]

Hi Andy,

You mentioned "attachments" (plural) in item 1, but SendObject is limited to
sending one object (table, query, etc.) as an attachment. You cannot send
multiple attachments in one message, using SendObject.

You can create a bound form in Access that allows you to type the body of
the message, select recipients from a list box, etc. You can use the code
shown below to send multiple attachments, with a command button on your form
to run this function:

Option Compare Database
Option Explicit
Public Function SendMail(strRecipients As String, strSubject As String, _
Optional strBody As String, Optional strFilePath As String, _
Optional strFileExtension As String) As String
On Error GoTo ProcError
' Written by Tom Wickerath, May 7, 2006.
' Inputs:
' strRecipients: Required. Semicolon delimited string of recipients.
' strSubject: Required. Message subject.
' strBody: Optional. Body of the message.
' strFilePath: Optional. Valid path containing files to attach.
' strFileExtension: Optional. Allows one to send a particular file type.
'
' Returns a string to indicate success or failure.
'
' Notes:
' 1.) An invalid path will result in a message with no attached files.
' 2.) This is late bound code. It does not require a reference to the
' "Microsoft Outlook X.X Object Library". However, there is also no
' Intellisence to help you with editing the VBA code.
'
' Usage examples from Immediate Window:
‘ ?SendMail("(e-mail address removed);[email protected]", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "Reports","My message","C:\Temp")
Dim myObject As Object
Dim myItem As Object
Dim strFullPath As String
Dim strFileName As String
Dim strAttachPath As Variant
Dim intAttachments As Integer
Set myObject = CreateObject("Outlook.Application")
Set myItem = myObject.CreateItem(0)
With myItem
.Subject = strSubject
.To = strRecipients
If Len(Trim(strBody)) > 0 Then
.Body = strBody
End If
If Len(Trim(strFileExtension)) = 0 Then
strFileExtension = "*.*"
End If
If Len(strFilePath) > 0 Then
strFullPath = strFilePath & "\" & strFileExtension
If Len(Trim(strFullPath)) > 0 Then 'An optional path was
included
strFileName = Dir(strFullPath)
Do Until strFileName = ""
intAttachments = intAttachments + 1
strAttachPath = (strFilePath & "\" & strFileName)
.Attachments.Add (strAttachPath)
' Debug.Print strAttachPath
strFileName = Dir()
Loop
End If
End If
.Send
SendMail = "Message placed in outbox with " _
& intAttachments & " file attachment(s)."
End With
ExitProc:
‘Cleanup
On Error Resume Next
Set myItem = Nothing
Set myObject = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMail Function..."
SendMail = "A problem was encountered attempting to automate Outlook."
Resume ExitProc
Resume
End Function





Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[andrewrubie]

Hi Tom,

Thanks for your reply. If I intend to use strFilePath in your code to
indicate the attached files then how to I conjure up an 'attach files'
dialog for the user and retrieve the list of files they have attached?

Regards,

Andy

 

[Arvin Meyer [MVP]]

Last year I completed a major email project that included generating as many
as 1200 to 1500 purchase orders in Access, making them into PDFs, merging
them with as many as 2 additional PDFs of varying size (up to 40 pages),
saving the merged files on the server (creating whatever file structure was
necessary on the fly), then emailing them using smtp through an asp script.
At first I used Outlook and the Exchange Server located in another city,
directly, but we occasionally had problems with relaying, so I changed to
using smtp. We still use the Exchange Server to send out individual
reprints.

The code I used was an adaptation of my code at:

http://www.datastrat.com/Code/MultipleEmail.txt

I also used Win2PDF to create the PDFs (http://www.Win2PDF.com) and Steve
Arbaugh's PDF and Mail class:

http://www.groupacg.com/

 

[Penny]

Hi Tom,

Thanks for your reply. If I intend to use strFilePath in your code to
indicate the attached files then how to I conjure up an 'attach files'
dialog for the user and retrieve the list of files they have attached?

Regards,

Andy

 

[andrewrubie]

Tom and Arvin,

Tom: Thanks again. So your proposed solution is to use the windows
common
dialog api's to gather the paths of the attachments and Outlook
automation
to send the email!? If it's a networked FE/BE database, will the user
need
an Exchange Server network licence?

Arvin: Thankyou to. The code example you show is the Exchange Server
solution you use to send out individual reprints. Is that correct? When
you
say you changed to smtp, do mean CDONTS or is that someting entirely
different?

Both: Would a mix of API to get the attachment paths and CDONTS(smtp?)
mailing code do the trick?

Regards,

Andy.

 

[andrewrubie]

Thanks again Tom,

So you've suggested Outlook automation Tom, what advantages might that
have over CDONTS? Or vice versa.

Regards,

Andy.

 

[Penny]

Tom and Arvin,

Tom: Thanks again. So your proposed solution is to use the windows common
dialog api's to gather the paths of the attachments and Outlook automation
to send the email!? If it's a networked FE/BE database, will the user need
an Exchange Server network licence?

Arvin: Thankyou to. The code example you show is the Exchange Server
solution you use to send out individual reprints. Is that correct? When you
say you changed to smtp, do mean CDONTS or is that someting entirely
different?

Both: Would a mix of API to get the attachment paths and CDONTS(smtp?)
mailing code do the trick?

Regards,

Andy.

 

[Guest]

Sorry, but I don't have any experience using CDONTS, so I cannot offer an
answer to your question.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________