Attaching Embedded Objects to a Email in Access

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("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
.

Relevant Pages

  • Re: Filename variable
    ... strFileName is a string--it should be used to hold the name of the ... 'Dim FSO As Scripting.FileSystemObject ... Dim strFileName As String ... sFileName + 1), 1) ...
    (microsoft.public.excel.programming)
  • 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)
  • Re: Access97 - str vars not recognized but hardcoded string is... HELP
    ... > Function ng_Step2(pstrFileName As String) As Integer ... > Dim strTempFileName As String ... > Dim strFileName As String ... > Dim rxw As Integer ...
    (microsoft.public.access.macros)
  • Re: Connecting to a MySQL database through VB .NET
    ... first thing I learned from the error reports was the connection string ... Dim conn As New OleDb.OleDbConnection ... startRecord, Int32 maxRecords, String srcTable, IDbCommand command, ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Importing Multiple Excell Files
    ... Dim strDirectory As String ... Dim strFileName As String ... button the directory table for choosing the folder comes up? ...
    (microsoft.public.access.externaldata)
Loading