Code to send Reports via Outlook

[Guest]

Looking for VBA to send Reports via Outlook.

Plus, can VBA be used to send Reports via Outlook Express?

TIA - Bob

 

[strive4peace]

Hi Bob,

You can use SendObject, which will use your default eMail program

'========================================= Email
'SendObject
'[objecttype]
'[, objectname]
'[, outputformat]
'[, to]
'[, cc]
'[, bcc]
'[, subject]
'[, messagetext]
'[, editmessage]
'[, templatefile]
'------------------------------------ eMailObject
'send an object using the DEFAULT Email program
'
Sub eMailObject ( _
pSendType as Long, _
pObjectName As String, _
pEmailAddress As String, _
pFriendlyName As String, _
pBooEditMessage As Boolean, _
pWhoFrom As String)

'Email attachment to someone
'and construct the subject and message

'example useage:
' on the command button code to process a report -->
' eMailObject _
3, _
"SonglistReport", _
"(e-mail address removed)", _
"Original Songs from an upcoming Star", _
false, _
"Susan Manager"

'PARAMETERS
'pSendType -->
' acSendReport = 3
' filter property need be saved
' acSendForm = 2
' the active form filter will be respected
' acSendQuery = 1
' ... etc
'pObjectName --> "qrySonglist"
'pEmailAddress --> "(e-mail address removed)"
'pFriendlyName --> Original Songs from an upcoming Star"
'pBooEditMessage --> true if you want to edit message
' before mail is sent
' --> false to send automatically
'pWhoFrom --> "Susan Doe"

'you can substitute acFormatSNP
' --> acFormatHTML
' --> acFormatRTF
' --> acFormatXLS
' --> acFormatTXT
' etc

on error goto Err_proc

DoCmd.SendObject _
pSendType, _
pObjectName, _
acFormatSNP, _
pEmailAddress _
, , , pFriendlyName _
& Format(Now(), " ddd m-d-yy h:nn am/pm"), _
pFriendlyName & " is attached --- " _
& "Regards, " _
& pWhoFrom, _
pBooEditMessage

Exit_proc:
Exit Sub

Err_proc:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " eMailObject"

'press F8 to find problem and fix
'comment or remove next line when code is done
Stop : Resume

Resume Exit_proc

End Sub

'~~~~~~~~~~~~~~~~~~~~~

and, if you want to use criteria, you will need to save the report
filter before you send it

'~~~~~~~~~~~~~~~~~~~~~

'------------------------------------ SetReportFilter
Sub SetReportFilter( _
byVal pReportName As String, _
byVal pFilter As String)

'Save a filter to the specified report
'You can do this before you send a report
' in an email message
'You can use this to filter subreports
' instead of putting criteria in the recordset

' USEAGE:
' example: in code that processes reports
' for viewing, printing, or email
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
' "City='Denver' AND dt_appt=#9/18/05#"

' written by Crystal
' Strive4peace2006 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

On Error GoTo Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
'---------- and set the report object variable
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save the changed report
DoCmd.Save acReport, pReportName

Exit_proc:
on error resume next
'---------- save the changed report
DoCmd.Close acReport, pReportName
'---------- Release object variable
Set rpt = Nothing

Exit Sub

Proc_Err:
Resume Next

msgbox Err.Description, , _
"ERROR " & Err.Number & " SetReportFilter"
'press F8 to step thru code and fix problem
'comment next line after debugged
Stop : Resume
'next line will be the one with the error

resume Exit_proc
End Sub

'~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*