MS Access Send To recipient (not as attachment but as HTML)

[Sean]

Hi... I want to use the macro/sendobject (or any other
procedure) to send the contents of a table (very small, ~5
rows/columns) as an Outlook message body, not as an
attachment. Access 2000 will not allow "Send To Mail
Recipient" (as opposed to "send as attachment") for
tables. Needless to say, every other MS application
(Word, Excel, etc) let's me send anything I want as a
message body. Outlook lets me import anything as a
message body; I can drag and drop the table from Access to
Outlook as a message body. Any ideas on how to automate
this task? (by the way, the "macro" to send the table as
an email body would run once a day...I don't want the
recipients to have to open a tiny text attachment every
day) Thanks!!!

[pietlinden]

obvious answer - automate outlook.
http://www.amazecreations.com/datafa...utlookmail.asp

[Sean]

Mate this works fine for mail if you are sending an attachment but if I
want to send the dataset in a mail as part of the body of the message

[Lyle Fairfield]

If you have Windows 2000 or greater, if you have not removed your ADO
references, if you add a CDO reference , if you make the replacements
in code carefully, if you correct the line breaks inserted by
newsreaders this is likely to do what you describe as wanting to do
silently and fusslessly:

Public Sub VerySimpleSendMailWithCDOSample()
' requires reference to cdosys.dll

Dim iCfg As CDO.Configuration
Dim iMsg As CDO.Message

Set iCfg = New CDO.Configuration
Set iMsg = New CDO.Message

With iCfg.Fields
..Item(cdoSendUsingMethod) = cdoSendUsingPort
..Item(cdoSMTPServerPort) = 25
..Item(cdoSMTPServer) = "SMTP.SomeDomain.Com"
..Item(cdoSMTPAuthenticate) = cdoBasic
..Item(cdoSendUserName) = "UserName"
..Item(cdoSendPassword) = "PassWord"
..Item(cdoSendEmailAddress) = "Some One<So*****@SomeDomain.Com>"
..Update
End With

With iMsg
..Configuration = iCfg
..Subject = "Whatever"
..To = "Re*******@SomeOtherDomain.com"
..TextBody = CurrentProject.Connection.Execute("SELECT * FROM
FirstTable").GetString(adClipString, , vbTab, vbNewLine, "")
..Send
End With

Set iMsg = Nothing
Set iCfg = Nothing

End Sub

[Lyle Fairfield]

Sean
You have asked me about this by e-mail. I have lost your e-mail
address.
You have pointed out that what you REALLY want to do is send a report
as html.
When Access outputs reports as HTML is does so as multiple files, one
for each page of the report.
The following Procedure tries to deal with those conditions. I have
tested it only with the report "Products By Category" in the Northwinds
Database.
It's very doubtful if this procedure will be sufficient in all cases or
even the majority of cases. But it's code; it can be modified to meet
specific needs.
---
In another thread some posters have indicated a problem with connecting
to the SMTP server. My only suggestion is to check the UserName
requirements; with Yahoo only the username is required, while with many
other servers the entire e-mail address is required. Beyond that lie
questions about smtp, the internet, firewalls etc. These are also
beyond the scope of this question.
---

Option Explicit

Public Sub SendReportAsHTML( _
ByVal ReportName As String, _
ByVal SMTPServer As String, _
ByVal SendUserName As String, _
ByVal SendPassword As String, _
ByVal SendEmailAddress As String, _
ByVal Subject As String, _
ByVal Recipients As String)

Dim Buffer As String
Dim Position As Long
Dim FileNumber As Integer
Dim HTML As String
Dim HTMLFullPath As String
Dim iCfg As Object
Dim iMsg As Object
Dim Skelton As String
Dim TempDirectory As String
Dim Truncate As Long

Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")

Skelton = Format(Now(), "mmmddyyyyhhnnss")
TempDirectory = Environ$("temp") & "/"
HTMLFullPath = TempDirectory & Skelton & ".html"

DoCmd.OutputTo acOutputReport, ReportName, acFormatHTML, HTMLFullPath

HTMLFullPath = Dir$(TempDirectory & Skelton & "*.html")
While Len(HTMLFullPath) <> 0
HTMLFullPath = TempDirectory & HTMLFullPath
FileNumber = FreeFile()
Open HTMLFullPath For Binary As #FileNumber
Buffer = String(LOF(FileNumber), vbNullChar)
Get #FileNumber, , Buffer
Close #FileNumber
Position = InStr(Buffer, "</TABLE>")
While Position <> 0
Truncate = Position
Position = InStr(Truncate + 1, Buffer, "</TABLE>")
Wend
HTML = HTML & Left(Buffer, Truncate + 7)
HTML = HTML & "<hr>"
Kill HTMLFullPath
HTMLFullPath = Dir$()
Wend

With iCfg.Fields
..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25
..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
SMTPServer
..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
..Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
SendUserName
..Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
SendPassword
..Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= SendEmailAddress
..Update
End With
With iMsg
..Configuration = iCfg
..Subject = Subject
..To = Recipients
..HTMLBody = HTML
..Send
End With

Set iMsg = Nothing
Set iCfg = Nothing

Close

End Sub

[Sean Hayden]

Hey Lyle,
Thanks very much re this.
It worked fine after as you say a few adaptions
Cheers Mate

*** Sent via Developersdex http://www.developersdex.com ***