Attaching Embedded Objects to a Email in Access

T

Tanya

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?
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("(e-mail address removed);[email protected]", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "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
 
S

Scott McDaniel

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("(e-mail address removed);[email protected]", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "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@takemeout_infotrakker.com
www.infotrakker.com
 
T

Tanya

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?

Thanks again
Tanya
 
S

Scott McDaniel

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
Thanks again
Tanya

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top