ASP - send cdo e-mail with database data

Author

send cdo e-mail with database data

joeyjoejnr

2007-08-06, 6:56 pm

hi,

i've got a database in ms access. using ASP (vbscript) and IIS. I can
send text e-mails through ASP but i want to attach a file from the
database to the e-mail when it is sent. can anyone give me any hints
on how to get the data from access, attach it to the e-mail and send
it on? any help would be awesome

Jim Rodgers

2007-08-07, 6:56 pm

"joeyjoejnr" wrote:

> hi,
>
> i've got a database in ms access. using ASP (vbscript) and IIS. I can
> send text e-mails through ASP but i want to attach a file from the
> database to the e-mail when it is sent. can anyone give me any hints
> on how to get the data from access, attach it to the e-mail and send
> it on? any help would be awesome
>

You may have multiple questions there, and I'm not sure the
main one is an ASP question, but here goes.

Here's a link that may answer your question about attachments:

http://www.microsoft.com/mspress/bo...pchap/3449.aspx

Here is a link to MS where you can search for fast answers to
questions like these:

http://technet.microsoft.com/en-us/default.aspx

If you want to create a file to attach, you must say exactly
what type of file you need. Excel? Word? Text?

You may need to create a file system object to save strings
as a text file. You can put stuff into strings from your ADO
recordsets from your Access database (or any database).
This is a basic problem in VBScript and ADO. Maybe this is
not best addressed in this particular newsgroup.

If you need help here, try these:

http://www.microsoft.com/communitie...ipting.vbscript

http://www.microsoft.com/communitie...t.public.access

If you want to extract data "AS DATA," put it in a file and
send it to another technical person to be imported into another
database, then you might want to create a "comma-separated
values" (CSV) textfile, a tab-delimited text file, or an XML file
from an ADO recordset of that data you want to send.

Here is my standard library snippet for creating an XML file
from an ADO recordset:

<% Option Explicit %>
<% Response.Buffer = TRUE %>
<% Response.CacheControl = "no-cache" %>
<% Response.AddHeader "Pragma", "no-cache" %>
<% Response.Expires = -1
'====================
%>

<%
'====================
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Whatever Page</title>
</head>
<body>
<%
'
'====================
Dim Cnxn
Set Cnxn = Server.CreateObject("ADODB.Connection")
Cnxn.Mode = adModeReadWrite
Cnxn.CursorLocation = adUseClient
Cnxn.Open Server.MapPath("/mdb_directory/MyAccessFile.mdb")
'====================
Dim rsMyData
Set rsMyData = Server.CreateObject("ADODB.Recordset")
rsMyData.Open
'====================
If Not rsMyData.EOF
rsMyData.Save "F:\DataDirectory\MyXmlDataFile.xml", adPersistXML
End If
'====================
rsMyData.Close
Set rsMyData = Nothing
'====================
Cnxn.Close
Set Cnxn = Nothing
'====================
'
%>
</body>
</html>

And, finally, here is my standard library snippet for CDO that
includes an attachment (this is a whole asp file):

<% Option Explicit %>
<% Response.Buffer = TRUE %>
<% Response.CacheControl = "no-cache" %>
<% Response.AddHeader "Pragma", "no-cache" %>
<% Response.Expires = -1
'====================
%>

<%
'====================
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Whatever Page</title>
</head>
<body>
<% '
'====================
'
' Send e-mail to customer@customer-company.com...
' by connecting to port 25 of the SMTP server
'
Dim iMsg, iConf, Flds, sHost, strHTML
'
sHost = "smtp.whoever-my-isp.net"
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
'
' set the CDOSYS configuration fields to use port 25 on the SMTP server
'
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
sHost

..Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With
'
' build HTML for message body
'
strHTML = "<html><head></head>"
strHTML = strHTML & "<body style=""background-color: #d0d0d0; color:
#002080; font-family: Verdana, Tahoma, Arial, Helvetica, sans-serif;
font-size: 13px;"">" & vbCrLf
strHTML = strHTML & "Hey there," & vbCrLf
strHTML = strHTML & " " & vbCrLf
strHTML = strHTML & "Blah, blah, blah" & vbCrLf
strHTML = strHTML & " " & vbCrLf
strHTML = strHTML & "Cheers, " & vbCrLf
strHTML = strHTML & "Your mother." & vbCrLf
strHTML = strHTML & "</body></html>"
'
' apply the settings to the message and send it
'
With iMsg
Set .Configuration = iConf
.To = sContact_Email
.From = "no-reply@whoevermycompany.com"
.BCC = "out-chron@whoevermycompany.com"
.Subject = "Thank you for your request."
.HTMLBody = strHTML
.AddAttachment "F:\FamilyDocs\YourSpringBudget.xls"
.Send
End With
'
' cleanup of variables
'
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
'
'====================
'
%>
</body>
</html>

joeyjoejnr

2007-08-16, 6:56 pm

On Aug 7, 4:36 pm, Jim Rodgers <JimRodg...@discussions.microsoft.com>
wrote:
> "joeyjoejnr" wrote:
>
>
> You may have multiple questions there, and I'm not sure the
> main one is an ASP question, but here goes.
>
> Here's a link that may answer your question about attachments:
>
> http://www.microsoft.com/mspress/bo...pchap/3449.aspx
>
> Here is a link to MS where you can search for fast answers to
> questions like these:
>
> http://technet.microsoft.com/en-us/default.aspx
>
> If you want to create a file to attach, you must say exactly
> what type of file you need. Excel? Word? Text?
>
> You may need to create a file system object to save strings
> as a text file. You can put stuff into strings from your ADO
> recordsets from your Access database (or any database).
> This is a basic problem in VBScript and ADO. Maybe this is
> not best addressed in this particular newsgroup.
>
> If you need help here, try these:
>
> http://www.microsoft.com/communitie...s/default.as...
>
> http://www.microsoft.com/communitie...s/default.as...
>
> If you want to extract data "AS DATA," put it in a file and
> send it to another technical person to be imported into another
> database, then you might want to create a "comma-separated
> values" (CSV) textfile, a tab-delimited text file, or an XML file
> from an ADO recordset of that data you want to send.
>
> Here is my standard library snippet for creating an XML file
> from an ADO recordset:
>
> <% Option Explicit %>
> <% Response.Buffer = TRUE %>
> <% Response.CacheControl = "no-cache" %>
> <% Response.AddHeader "Pragma", "no-cache" %>
> <% Response.Expires = -1
> '====================
> %>
> 
> <%
> '====================
> %>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
> <html xmlns="http://www.w3.org/1999/xhtml">
> <head>
> <title>Whatever Page</title>
> </head>
> <body>
> <%
> '
> '====================
> Dim Cnxn
> Set Cnxn = Server.CreateObject("ADODB.Connection")
> Cnxn.Mode = adModeReadWrite
> Cnxn.CursorLocation = adUseClient
> Cnxn.Open Server.MapPath("/mdb_directory/MyAccessFile.mdb")
> '====================
> Dim rsMyData
> Set rsMyData = Server.CreateObject("ADODB.Recordset")
> rsMyData.Open
> '====================
> If Not rsMyData.EOF
> rsMyData.Save "F:\DataDirectory\MyXmlDataFile.xml", adPersistXML
> End If
> '====================
> rsMyData.Close
> Set rsMyData = Nothing
> '====================
> Cnxn.Close
> Set Cnxn = Nothing
> '====================
> '
> %>
> </body>
> </html>
>
> And, finally, here is my standard library snippet for CDO that
> includes an attachment (this is a whole asp file):
>
> <% Option Explicit %>
> <% Response.Buffer = TRUE %>
> <% Response.CacheControl = "no-cache" %>
> <% Response.AddHeader "Pragma", "no-cache" %>
> <% Response.Expires = -1
> '====================
> %>
> 
> <%
> '====================
> %>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
> <html xmlns="http://www.w3.org/1999/xhtml">
> <head>
> <title>Whatever Page</title>
> </head>
> <body>
> <% '
> '====================
> '
> ' Send e-mail to custo...@customer-company.com...
> ' by connecting to port 25 of the SMTP server
> '
> Dim iMsg, iConf, Flds, sHost, strHTML
> '
> sHost = "smtp.whoever-my-isp.net"
> Set iMsg = CreateObject("CDO.Message")
> Set iConf = CreateObject("CDO.Configuration")
> Set Flds = iConf.Fields
> '
> ' set the CDOSYS configuration fields to use port 25 on the SMTP server
> '
> With Flds
> .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
> cdoSendUsingPort
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
> sHost
>
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
> = 10
> .Update
> End With
> '
> ' build HTML for message body
> '
> strHTML = "<html><head></head>"
> strHTML = strHTML & "<body style=""background-color: #d0d0d0; color:
> #002080; font-family: Verdana, Tahoma, Arial, Helvetica, sans-serif;
> font-size: 13px;"">" & vbCrLf
> strHTML = strHTML & "Hey there," & vbCrLf
> strHTML = strHTML & " " & vbCrLf
> strHTML = strHTML & "Blah, blah, blah" & vbCrLf
> strHTML = strHTML & " " & vbCrLf
> strHTML = strHTML & "Cheers, " & vbCrLf
> strHTML = strHTML & "Your mother." & vbCrLf
> strHTML = strHTML & "</body></html>"
> '
> ' apply the settings to the message and send it
> '
> With iMsg
> Set .Configuration = iConf
> .To = sContact_Email
> .From = "no-re...@whoevermycompany.com"
> .BCC = "out-ch...@whoevermycompany.com"
> .Subject = "Thank you for your request."
> .HTMLBody = strHTML
> .AddAttachment "F:\FamilyDocs\YourSpringBudget.xls"
> .Send
> End With
> '
> ' cleanup of variables
> '
> Set iMsg = Nothing
> Set iConf = Nothing
> Set Flds = Nothing
> '
> '====================
> '
> %>
> </body>
> </html>

hi,

thanks for your help, really really useful. I just need to attach
simple text just to let the person know which record in the database
they are referring to.

again, thanks for your help

Sponsored Links