Re: Attaching Embedded Objects to a Email in Access

On Wed, 20 Jun 2007 14:18:59 -0700, Tanya <Tanya@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi Scott,

Thanks for pointing me in the right direction, just one question though, can
this be used for word, pdf, excel documents that are stored as objects in my
sql database backend? or does this just apply to reports created withinin
access?

It only works for Access reports. The lebans method uses the Snapshot format of the Access report to create the PDF, if
I'm not mistaken. If you want to print to PDF, you'd need to purchase a utility to do that (or do a search on "ms access
PDF" and you may find something you can use <g>).



Thanks again
Tanya

"Scott McDaniel" wrote:

On Tue, 19 Jun 2007 20:59:00 -0700, Tanya <Tanya@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi,

I have been struggling with this for a while now and hope that someone might
have a idea of how to do this, I have set up a form which allows users to
attach external files and pull the email address from a table.

However I also have a reports table in my database that stores reports in
either word or pdf format as a embedded object.

I can't figure out how to allow users to select one of those reports from a
listbox and attach that report/object to the email. I am guessing i might
have to export the report to a temp location and then attach the file like
normal?

Yes ... you'd need to save the report somewhere, then attach it. Stepen Lebans has a great utility for exporting
items
to PDF:

http://www.lebans.com/reporttopdf.htm

You'd do this before your run the SendMail function, of course, and then pass in the full path with the strFilePath
argument.

Also - PDF generation can take some time, so make sure that you verify the existence of the new .pdf file before you
try
to attach it, using the Dir function ...



I am using the following email code to send emails with external attachments:

'*************Begin Code***********************
Option Compare Database
Option Explicit

Public Function SendMail(strRecipients As String, strSubject As String, _
Optional strBody As String, Optional strFilePath As String, _
Optional strFileExtension As String) As String

On Error GoTo ProcError

' Written by Tom Wickerath, May 7, 2006.
' Inputs:
' strRecipients: Required. Semicolon delimited string of recipients.
' strSubject: Required. Message subject.
' strBody: Optional. Body of the message.
' strFilePath: Optional. Valid path containing files to attach.
' strFileExtension: Optional. Allows one to send a particular file type.
'
' Returns a string to indicate success or failure.
'
' Notes:
' 1.) An invalid path will result in a message with no attached files.
' 2.) This is late bound code. It does not require a reference to the
' "Microsoft Outlook X.X Object Library". However, there is also no
' Intellisence to help you with editing the VBA code.
'
' Usage examples from Immediate Window:
' Message without attachments
' ?SendMail("m...@xxxxx;y...@xxxxx", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("m...@xxxxx;y...@xxxxx", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("m...@xxxxx;y...@xxxxx", "Reports","My message","C:\Temp")


Dim myObject As Object
Dim myItem As Object
Dim strFullPath As String
Dim strFileName As String
Dim strAttachPath As Variant
Dim intAttachments As Integer


Set myObject = CreateObject("Outlook.Application")
Set myItem = myObject.CreateItem(0)


With myItem
.Subject = strSubject
.To = strRecipients


If Len(Trim(strBody)) > 0 Then
.Body = strBody
End If


If Len(Trim(strFileExtension)) = 0 Then
strFileExtension = "*.*"
End If


If Len(strFilePath) > 0 Then
strFullPath = strFilePath & "\" & strFileExtension


If Len(Trim(strFullPath)) > 0 Then 'An optional path was
included
strFileName = Dir(strFullPath)
Do Until strFileName = ""
intAttachments = intAttachments + 1
strAttachPath = (strFilePath & "\" & strFileName)
.Attachments.Add (strAttachPath)
' Debug.Print strAttachPath
strFileName = Dir()
Loop
End If
End If


.Send
SendMail = "Message placed in outbox with " _
& intAttachments & " file attachment(s)."
End With


ExitProc:
Set myItem = Nothing
Set myObject = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMail Function..."
SendMail = "A problem was encountered attempting to automate Outlook."
Resume ExitProc
Resume
End Function


'************* End Code ***********************

hope this makes sense
Thanks in advance

Tanya

Scott McDaniel
scott@xxxxxxxxxxxxxxxxxxxxxxxxx
www.infotrakker.com


Scott McDaniel
scott@xxxxxxxxxxxxxxxxxxxxxxxxx
www.infotrakker.com
.

Relevant Pages

  • Re: Attaching Embedded Objects to a Email in Access
    ... or does this just apply to reports created withinin ... either word or pdf format as a embedded object. ... Public Function SendMail(strRecipients As String, strSubject As String, _ ... Dim strFileName As String ...
    (microsoft.public.access.formscoding)
  • Re: Attaching Embedded Objects to a Email in Access
    ... However I also have a reports table in my database that stores reports in ... Public Function SendMail(strRecipients As String, strSubject As String, _ ... Dim myObject As Object ... Dim strFileName As String ...
    (microsoft.public.access.formscoding)
  • Attaching Embedded Objects to a Email in Access
    ... However I also have a reports table in my database that stores reports in ... Public Function SendMail(strRecipients As String, strSubject As String, _ ... Dim myObject As Object ... Dim strFileName As String ...
    (microsoft.public.access.formscoding)
  • Re: Long print ranges in VBA
    ... I think that Jim explained the problem--it's the length of the string. ... And I don't know enough about whatever PDF writer you're using to know how it'll ... Dim myRng As Range ... to work for 23-24 reports, but it appears I dont have the VBA ...
    (microsoft.public.excel.programming)
  • Re: Snapshot to pdf code
    ... Dim strDocName As String ... ' Save the Report as a PDF document. ... ' The selected report is first exported to Snapshot format. ...
    (microsoft.public.access.reports)