SendObject Attachment Problem

[Guest]

Hi All,

I have a database (using A2000) which handles orders, invoices, etc for a
client. As well as being able to print an invoice the client also has the
option to e-mail the invoice to his customer as an attached Snapshot file.
This is done with the DoCmd.SendObject command in VBA and works fine.

However, the client now wants to be able to attach .pdf file/s to the same
e-mail, one for each product in the order which gives further information
about the product being delivered.

Since it appears it is not possible to add more files to the e-mail using
the SendObject command, I need to find some other alternative, if possible.

I did think about creating the e-mail (in Outlook 2003) using the SendObject
command and then attaching the files using Automation but that does not seem
to work and in any case the attaching code would have to come after the
e-mail has been created so that the user would not then see the extra
attached files in the Outlook window (which is what they want).

I have also considered using the MAPI active X controls to create the e-mail
within Access but I don't know of any way that the Snapshot file for the
Invoice report could be generated and then attached to the e-mail.

I could also use Automation to create the e-mail from Access but I still
have the problem of making the Snapshot file to attach to the e-mail as well
as the .pdf files.

Does anyone have any more thoughts on this. Thanks very much.

 

[Guest]

Hi Peter,

Since it appears it is not possible to add more files to the e-mail using
the SendObject command, ....

This is a correct statement. If your client is using Outlook, you can use
the method shown in this MSDN article to attach multiple files. Although the
article indicates Outlook 2003, I think that the section titled Send an
Outlook Message Using Access will work with Outlook 2000 or 2002 as well:

Using Automation in Microsoft Office Access 2003 to Work with Microsoft
Office Outlook 2003

http://msdn.microsoft.com/office/do...library/en-us/dno2k3ta/html/odc_ac_olauto.asp

I could also use Automation to create the e-mail from Access but I still
have the problem of making the Snapshot file to attach to the e-mail as well
as the .pdf files.

You can use code like this to output a report to a snapshot file. The report
could be opened using VBA code that supplied to criteria to filter the report
to one or more products:

DoCmd.OutputTo acOutputReport, "YourReportNameHere", _
acFormatSNP, "c:\temp\" & rs1("EquipID") & ".snp"

In the Report's activate procedure, you can use code to programmatically set
the caption, which will be used as the filename upon export. I believe the
same idea will work for programmatically exporting PDF files, although I have
less experience doing that, since snapshot files tend to serve my needs just
fine.

Private Sub Report_Activate()
On Error GoTo ProcError

' The caption is used for the Snapshot filename (caption.snp)
Me.Caption = strServiceRequested & " Request for " & EquipID
DoCmd.Maximize

ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"Error in Report_Activate event procedure..."
Resume ExitProc
End Sub


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom

Thanks for that info, it works fine now. I just need to figure out how to
attach multiple files to an e-mail but I guess the Outlook guys will help if
I can't.

 

[Guest]

Hi Peter,

Here is an example with hard-coded values for the recipient and the folder.
Perhaps you can adapt this code to your situation.

'*************Begin Code***********************

Option Compare Database
Option Explicit

' Demonstrates sending multiple file attachments using Outlook.

Public Function SendMail()
On Error GoTo ProcError

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim Filename As String
Dim FilePath As String
Dim intAttachments As Integer

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = olBCC

.Subject = "Confirmation of files received"
.Body = "Whatever you want to say here."

Filename = Dir("C:\temp\*.snp")

Do Until Filename = ""
intAttachments = intAttachments + 1
FilePath = ("C:\temp\" & Filename)
Filename = Dir()

Debug.Print Len(Trim(FilePath))
If Len(Trim(FilePath)) > 0 Then
Debug.Print FilePath
.Attachments.Add FilePath
End If

Loop

If intAttachments > 0 Then
.Send
Else
MsgBox "There are no attachments to send.", vbCritical, _
"We have no banannas..."
End If
End With

ExitProc:
Set objOutlook = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMail Function..."
Resume ExitProc
Resume
End Function

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


I can work up a different example that allows optional parameters, if this
is needed.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

__________________________________________

 

[Guest]

Here is an example that allows optional parameters, returning a string to
indicate success or failure:

'*************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 ***********************


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom

Thanks for the extra code to create the e-mail from Access. I have pasted
the code into my test database and I will look at it tommorow (it's 10
o'clock at night here now). I am sure I can adapt it to do exactly what I
want.

Thanks again for your help.