Sending e-mail from Access using, maybe, an API

[Jesse]

I want to send e-mail from my Access application and include a subject
and body. I have code to do this from Outlook but I don't know what
e-mail applications my end users will be using. Some programs I've
seen (WinZip, Internet Explorer) open up a MailItem window with some
custom subject and body and don't care what e-mail program you are
using. It looks like the applications are using some king of API call
that provides the default e-mail program with information like SendTo,
Subject, Body, Attachment, etc. Am I right in the assumption that this
is an API call (or something similar)? If I'm right, can someone
provide me with some sample code or direct me approprietly?

Thanks

 

[Douglas J. Steele]

No, it's not really an API call, but the built-in SendObject method provides
a generic capability.

Take a look at the Microsoft Access Email FAQ Tony Toews has at
http://www.granite.ab.ca/access/email.htm

 

[Douglas J. Steele]

No, it's not really an API call, but the built-in SendObject method provides
a generic capability.

Take a look at the Microsoft Access Email FAQ Tony Toews has at
http://www.granite.ab.ca/access/email.htm

 

[Jesse]

Haven't tried it before beacause on the limitations it said that only
255 characters could be sent as the message. I prepared a function and
was able to send an e-mail with 1879 characters in the body. This is
the module:

Option Compare Database
Option Explicit
'2006-05-23
'Jesse Avilés
'Sends message using default e-mail application using the send object
method

'Public Enum jasMsgFormat
' jasDAP = acFormatDAP
' jasHTML = acFormatHTML
' jasRTF = acFormatRTF
' jasTXT = acFormatTXT
' jasXLS = acFormatXLS
'End Enum

Public Function jasSendMessage(strTo As String, _
strSubject As String, _
strBody As String, _
Optional bolEdit As Boolean = False, _
Optional strFormat As String =
acFormatTXT) As Boolean
' Comments : Uses send object to send email from default
application
' Parameters: strTo: string containing address(es) to send the
message. Multiple addresses can be separated by a semicolon (
' strSubject: message subject
' strBody: message body
' bolEdit: False (default) to send message immediately,
True to open message and let user modify it further
' jasMsgFormat: Format in which the message will be
sent. Default is plain text
' Returns : True if function was succesful
' Created : 23 May 2006 04:49 JA
' Modified :
' --------------------------------------------------

On Error GoTo ErrorHandler
jasSendMessage = False

DoCmd.SendObject _
ObjectType:=acSendNoObject, _
OutputFormat:=strFormat, _
To:=strTo, _
Subject:=strSubject, _
MessageText:=strBody, _
EditMessage:=bolEdit

jasSendMessage = True
ExitHandler:
Exit Function

ErrorHandler:
MsgBox _
Prompt:="Error " & Err.Number & vbNewLine & Err.Description &
vbNewLine & "In SendObjectMessage."
Resume ExitHandler
End Function


As you can see, I tried setting up an Enum to use the acFormatType
constants but the code wouldn't compile. I receive error type mismatch
and it highlights "jasDAP =". This is the first time I tried doing an
enumeration and I seem to follow what is written on the help file
(using Access 2k2, Win XP). Any help on how I can make this work?
Thanks.

 

[Jesse]

Haven't tried it before beacause on the limitations it said that only
255 characters could be sent as the message. I prepared a function and
was able to send an e-mail with 1879 characters in the body. This is
the module:

Option Compare Database
Option Explicit
'2006-05-23
'Jesse Avilés
'Sends message using default e-mail application using the send object
method

'Public Enum jasMsgFormat
' jasDAP = acFormatDAP
' jasHTML = acFormatHTML
' jasRTF = acFormatRTF
' jasTXT = acFormatTXT
' jasXLS = acFormatXLS
'End Enum

Public Function jasSendMessage(strTo As String, _
strSubject As String, _
strBody As String, _
Optional bolEdit As Boolean = False, _
Optional strFormat As String =
acFormatTXT) As Boolean
' Comments : Uses send object to send email from default
application
' Parameters: strTo: string containing address(es) to send the
message. Multiple addresses can be separated by a semicolon (
' strSubject: message subject
' strBody: message body
' bolEdit: False (default) to send message immediately,
True to open message and let user modify it further
' jasMsgFormat: Format in which the message will be
sent. Default is plain text
' Returns : True if function was succesful
' Created : 23 May 2006 04:49 JA
' Modified :
' --------------------------------------------------

On Error GoTo ErrorHandler
jasSendMessage = False

DoCmd.SendObject _
ObjectType:=acSendNoObject, _
OutputFormat:=strFormat, _
To:=strTo, _
Subject:=strSubject, _
MessageText:=strBody, _
EditMessage:=bolEdit

jasSendMessage = True
ExitHandler:
Exit Function

ErrorHandler:
MsgBox _
Prompt:="Error " & Err.Number & vbNewLine & Err.Description &
vbNewLine & "In SendObjectMessage."
Resume ExitHandler
End Function


As you can see, I tried setting up an Enum to use the acFormatType
constants but the code wouldn't compile. I receive error type mismatch
and it highlights "jasDAP =". This is the first time I tried doing an
enumeration and I seem to follow what is written on the help file
(using Access 2k2, Win XP). Any help on how I can make this work?
Thanks.

 

[Jesse]

The sent message show the header information of the e-mail:

Cliente_quiere_inspecci=F3n_?=
=?iso-8859-1?Q?de_s?=
Date: Tue, 23 May 2006 05:38:16 -0400
Message-ID: <000801c67e4c$a319a920$22063242@jesseol12ekq0w>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869


Why would it be doing this?

 

[Jesse]

The sent message show the header information of the e-mail:

Cliente_quiere_inspecci=F3n_?=
=?iso-8859-1?Q?de_s?=
Date: Tue, 23 May 2006 05:38:16 -0400
Message-ID: <000801c67e4c$a319a920$22063242@jesseol12ekq0w>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869


Why would it be doing this?

 

[Douglas J Steele]

Why would it be doing what? SendObject will use whatever MAPI-compliant
e-mail application it finds on your machine. Whatever appears in the
"plumbing" of the e-mail comes from that client, not from Access.

 

[Douglas J Steele]

Why would it be doing what? SendObject will use whatever MAPI-compliant
e-mail application it finds on your machine. Whatever appears in the
"plumbing" of the e-mail comes from that client, not from Access.

 

[Jesse]

So in this case, Outlook is the one that actually inserts the header
information in the body of the email. I'll have to do some more tests
to check what's going on. Anyways, any advice on the Enum problem
presented above? TIA.

 

[Jesse]

So in this case, Outlook is the one that actually inserts the header
information in the body of the email. I'll have to do some more tests
to check what's going on. Anyways, any advice on the Enum problem
presented above? TIA.

 

[Douglas J. Steele]

Sorry, no idea. Why do you need an enum?

 

[Douglas J. Steele]

Sorry, no idea. Why do you need an enum?

 

[david epsom dot com dot au]

Enum values have to be numbers (long).

acFormatDAP is a text string:
"Microsoft Access Data Access Page (*.html)"

(david)


Haven't tried it before beacause on the limitations it said that only
255 characters could be sent as the message. I prepared a function and
was able to send an e-mail with 1879 characters in the body. This is
the module:

Option Compare Database
Option Explicit
'2006-05-23
'Jesse Avilés
'Sends message using default e-mail application using the send object
method

'Public Enum jasMsgFormat
' jasDAP = acFormatDAP
' jasHTML = acFormatHTML
' jasRTF = acFormatRTF
' jasTXT = acFormatTXT
' jasXLS = acFormatXLS
'End Enum

Public Function jasSendMessage(strTo As String, _
strSubject As String, _
strBody As String, _
Optional bolEdit As Boolean = False, _
Optional strFormat As String =
acFormatTXT) As Boolean
' Comments : Uses send object to send email from default
application
' Parameters: strTo: string containing address(es) to send the
message. Multiple addresses can be separated by a semicolon (
' strSubject: message subject
' strBody: message body
' bolEdit: False (default) to send message immediately,
True to open message and let user modify it further
' jasMsgFormat: Format in which the message will be
sent. Default is plain text
' Returns : True if function was succesful
' Created : 23 May 2006 04:49 JA
' Modified :
' --------------------------------------------------

On Error GoTo ErrorHandler
jasSendMessage = False

DoCmd.SendObject _
ObjectType:=acSendNoObject, _
OutputFormat:=strFormat, _
To:=strTo, _
Subject:=strSubject, _
MessageText:=strBody, _
EditMessage:=bolEdit

jasSendMessage = True
ExitHandler:
Exit Function

ErrorHandler:
MsgBox _
Prompt:="Error " & Err.Number & vbNewLine & Err.Description &
vbNewLine & "In SendObjectMessage."
Resume ExitHandler
End Function


As you can see, I tried setting up an Enum to use the acFormatType
constants but the code wouldn't compile. I receive error type mismatch
and it highlights "jasDAP =". This is the first time I tried doing an
enumeration and I seem to follow what is written on the help file
(using Access 2k2, Win XP). Any help on how I can make this work?
Thanks.

 

[david epsom dot com dot au]

Enum values have to be numbers (long).

acFormatDAP is a text string:
"Microsoft Access Data Access Page (*.html)"

(david)


Haven't tried it before beacause on the limitations it said that only
255 characters could be sent as the message. I prepared a function and
was able to send an e-mail with 1879 characters in the body. This is
the module:

Option Compare Database
Option Explicit
'2006-05-23
'Jesse Avilés
'Sends message using default e-mail application using the send object
method

'Public Enum jasMsgFormat
' jasDAP = acFormatDAP
' jasHTML = acFormatHTML
' jasRTF = acFormatRTF
' jasTXT = acFormatTXT
' jasXLS = acFormatXLS
'End Enum

Public Function jasSendMessage(strTo As String, _
strSubject As String, _
strBody As String, _
Optional bolEdit As Boolean = False, _
Optional strFormat As String =
acFormatTXT) As Boolean
' Comments : Uses send object to send email from default
application
' Parameters: strTo: string containing address(es) to send the
message. Multiple addresses can be separated by a semicolon (
' strSubject: message subject
' strBody: message body
' bolEdit: False (default) to send message immediately,
True to open message and let user modify it further
' jasMsgFormat: Format in which the message will be
sent. Default is plain text
' Returns : True if function was succesful
' Created : 23 May 2006 04:49 JA
' Modified :
' --------------------------------------------------

On Error GoTo ErrorHandler
jasSendMessage = False

DoCmd.SendObject _
ObjectType:=acSendNoObject, _
OutputFormat:=strFormat, _
To:=strTo, _
Subject:=strSubject, _
MessageText:=strBody, _
EditMessage:=bolEdit

jasSendMessage = True
ExitHandler:
Exit Function

ErrorHandler:
MsgBox _
Prompt:="Error " & Err.Number & vbNewLine & Err.Description &
vbNewLine & "In SendObjectMessage."
Resume ExitHandler
End Function


As you can see, I tried setting up an Enum to use the acFormatType
constants but the code wouldn't compile. I receive error type mismatch
and it highlights "jasDAP =". This is the first time I tried doing an
enumeration and I seem to follow what is written on the help file
(using Access 2k2, Win XP). Any help on how I can make this work?
Thanks.

 

[Jesse]

I just wanted to be able to let the user of the function select one of
the five acFormatType options. As the code stands right now, on the
format of the message to be sent, I ask the user for a string instead
of providing the user with a selection of appropriate values. If you
look at the code above, you will see that the Enum is commented out
because I receive a type mismatch error. However, I think that I will
not do this in a seoarate module since, except for providing
success/failure value I'm not adding anything new or that may simplify
the use of SendObject.

Sent a couple more e-mails, and the header information only appears
when the message is sent as text. I have used RTF and HTML as the
format to send and the e-mail header information does not appears. The
subject line still gets the encoding (iso-8859-1) inserted before the
actual subject text. I'm using Outlook XP. David, thanks for the info
on the Enum. Looking at some code from the Access Developers Handbook,
noticed, for the first time, that everyting is a number.

 

[Jesse]

I just wanted to be able to let the user of the function select one of
the five acFormatType options. As the code stands right now, on the
format of the message to be sent, I ask the user for a string instead
of providing the user with a selection of appropriate values. If you
look at the code above, you will see that the Enum is commented out
because I receive a type mismatch error. However, I think that I will
not do this in a seoarate module since, except for providing
success/failure value I'm not adding anything new or that may simplify
the use of SendObject.

Sent a couple more e-mails, and the header information only appears
when the message is sent as text. I have used RTF and HTML as the
format to send and the e-mail header information does not appears. The
subject line still gets the encoding (iso-8859-1) inserted before the
actual subject text. I'm using Outlook XP. David, thanks for the info
on the Enum. Looking at some code from the Access Developers Handbook,
noticed, for the first time, that everyting is a number.

 

[Jesse]

Ok, I think I figured out why Outlook was including the header message
in the e-mail. This code is the one I just fixed/tried:

Private Function fctEmailMyNote()
' Comments :
' Parameters:
' Returns : -
' Created : 21 May 2006 18:34 JA
' Modified :
' --------------------------------------------------

On Error GoTo ErrorHandler
Dim strRecipient As String
Dim strBody As String
Dim strSubject As String
Dim lngFirstEnter As Long

strRecipient = DLookup("txtEmail", "tblContacts", "txtContactID =
'" & Me.lutxtContactID & "'")

lngFirstEnter = InStr(Me.meNDescription, Chr$(13))
strSubject = Left(Me.meNDescription, lngFirstEnter - 1)
strBody =
"------------------------------------------------------------" &
vbNewLine
strBody = strBody & "Client: " & Me.lutxtContactID & " - " &
DLookup("Name", "qryContactsList", "txtContactID = '" &
Me.lutxtContactID & "'") & (" - " + DLookup("txtCompany",
"tblContacts", "txtContactID = '" & Me.lutxtContactID & "'")) &
vbNewLine
strBody = strBody & "Proposal: " & Me.lutxtProposalID & vbNewLine
strBody = strBody & "Project: " & Me.lutxtProjectID & vbNewLine
strBody = strBody & "Invoice: " & Me.lutxtInvoiceID & vbNewLine
strBody = strBody & "Date: " & Format(Me.dtNDate, "yyyy-mmmm-dd") &
vbNewLine
strBody = strBody &
"------------------------------------------------------------" &
vbNewLine
strBody = strBody & Me.meNDescription

DoCmd.SendObject _
ObjectType:=acSendNoObject, _
OutputFormat:=acFormatHTML, _
To:=strRecipient, _
Subject:=strSubject, _
MessageText:=strBody, _
EditMessage:=True, _
TemplateFile:=CurrentProject.Path & "\EmailHTMLTemplate.html"

ExitHandler:
Exit Function

ErrorHandler:
MsgBox _
Prompt:="Error " & Err.Number & vbNewLine & Err.Description &
vbNewLine & "In fctEmailMyNote."
Resume ExitHandler
End Function

strSubject on my first tries looked for the first 60 characters of the
field meNDescription. Those characters included the "Enter" key.
After using the InStr function to find where the first "Enter" was and
then selecting the string left of where the first "Enter" was, the
message sent from Outlook looks the way is supposed to be. One thing I
should mention here is that Outlook has no respect for the format you
want it to be sent. It will always send as the defined default (in my
case plain text). I believe I can tell Mr. Tony that the 255 character
limitation in the message is no longer true (maybe he can change it in
his web page). Thanks all.

 

[Jesse]

Ok, I think I figured out why Outlook was including the header message
in the e-mail. This code is the one I just fixed/tried:

Private Function fctEmailMyNote()
' Comments :
' Parameters:
' Returns : -
' Created : 21 May 2006 18:34 JA
' Modified :
' --------------------------------------------------

On Error GoTo ErrorHandler
Dim strRecipient As String
Dim strBody As String
Dim strSubject As String
Dim lngFirstEnter As Long

strRecipient = DLookup("txtEmail", "tblContacts", "txtContactID =
'" & Me.lutxtContactID & "'")

lngFirstEnter = InStr(Me.meNDescription, Chr$(13))
strSubject = Left(Me.meNDescription, lngFirstEnter - 1)
strBody =
"------------------------------------------------------------" &
vbNewLine
strBody = strBody & "Client: " & Me.lutxtContactID & " - " &
DLookup("Name", "qryContactsList", "txtContactID = '" &
Me.lutxtContactID & "'") & (" - " + DLookup("txtCompany",
"tblContacts", "txtContactID = '" & Me.lutxtContactID & "'")) &
vbNewLine
strBody = strBody & "Proposal: " & Me.lutxtProposalID & vbNewLine
strBody = strBody & "Project: " & Me.lutxtProjectID & vbNewLine
strBody = strBody & "Invoice: " & Me.lutxtInvoiceID & vbNewLine
strBody = strBody & "Date: " & Format(Me.dtNDate, "yyyy-mmmm-dd") &
vbNewLine
strBody = strBody &
"------------------------------------------------------------" &
vbNewLine
strBody = strBody & Me.meNDescription

DoCmd.SendObject _
ObjectType:=acSendNoObject, _
OutputFormat:=acFormatHTML, _
To:=strRecipient, _
Subject:=strSubject, _
MessageText:=strBody, _
EditMessage:=True, _
TemplateFile:=CurrentProject.Path & "\EmailHTMLTemplate.html"

ExitHandler:
Exit Function

ErrorHandler:
MsgBox _
Prompt:="Error " & Err.Number & vbNewLine & Err.Description &
vbNewLine & "In fctEmailMyNote."
Resume ExitHandler
End Function

strSubject on my first tries looked for the first 60 characters of the
field meNDescription. Those characters included the "Enter" key.
After using the InStr function to find where the first "Enter" was and
then selecting the string left of where the first "Enter" was, the
message sent from Outlook looks the way is supposed to be. One thing I
should mention here is that Outlook has no respect for the format you
want it to be sent. It will always send as the defined default (in my
case plain text). I believe I can tell Mr. Tony that the 255 character
limitation in the message is no longer true (maybe he can change it in
his web page). Thanks all.