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 VerySimpleSendM ailWithCDOSampl e()
' requires reference to cdosys.dll

Dim iCfg As CDO.Configurati on
Dim iMsg As CDO.Message

Set iCfg = New CDO.Configurati on
Set iMsg = New CDO.Message

With iCfg.Fields
..Item(cdoSendU singMethod) = cdoSendUsingPor t
..Item(cdoSMTPS erverPort) = 25
..Item(cdoSMTPS erver) = "SMTP.SomeDomai n.Com"
..Item(cdoSMTPA uthenticate) = cdoBasic
..Item(cdoSendU serName) = "UserName"
..Item(cdoSendP assword) = "PassWord"
..Item(cdoSendE mailAddress) = "Some One<So*****@Som eDomain.Com>"
..Update
End With

With iMsg
..Configuration = iCfg
..Subject = "Whatever"
..To = "Re*******@Some OtherDomain.com "
..TextBody = CurrentProject. Connection.Exec ute("SELECT * FROM
FirstTable").Ge tString(adClipS tring, , 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 SendReportAsHTM L( _
ByVal ReportName As String, _
ByVal SMTPServer As String, _
ByVal SendUserName As String, _
ByVal SendPassword As String, _
ByVal SendEmailAddres s 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("C DO.Configuratio n")
Set iMsg = CreateObject("C DO.Message")

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

DoCmd.OutputTo acOutputReport, ReportName, acFormatHTML, HTMLFullPath

HTMLFullPath = Dir$(TempDirect ory & Skelton & "*.html")
While Len(HTMLFullPat h) <> 0
HTMLFullPath = TempDirectory & HTMLFullPath
FileNumber = FreeFile()
Open HTMLFullPath For Binary As #FileNumber
Buffer = String(LOF(File Number), 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.microso ft.com/cdo/configuration/sendusing") = 2
..Item("http://schemas.microso ft.com/cdo/configuration/smtpserverport" )
= 25
..Item("http://schemas.microso ft.com/cdo/configuration/smtpserver") =
SMTPServer
..Item("http://schemas.microso ft.com/cdo/configuration/smtpauthenticat e")
= 1
..Item("http://schemas.microso ft.com/cdo/configuration/sendusername") =
SendUserName
..Item("http://schemas.microso ft.com/cdo/configuration/sendpassword") =
SendPassword
..Item("http://schemas.microso ft.com/cdo/configuration/sendemailaddres s")
= SendEmailAddres s
..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 ***