G
Guest
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
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