Attachments in Access 2007

[unit213]

I'm looking for a VBA solution that will allow me to email an attachment (not a report).
As a simple example, I have a table setup as follows:

The 'Quote' field has an 'Attachment' data type. That's what I'm trying to email.

I'm already using the following code to email a report. I'd like to find a way to include attach the attachment (Quote field from the table) to the email also.

Function Email()

Dim db As Database
Dim rs As DAO.Recordset
Dim strTO As String




'Dont't forget to reference the Outlook Object Library

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

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

'DoCmd.SetWarnings 0


'Only select records with an email address

Set rs = db.OpenRecordset("SELECT Email FROM Email WHERE Trim(Email & '')<>''")

Do Until rs.EOF
If strTO = "" Then
strTO = rs!Email
Else
strTO = strTO & "; " & rs!Email
End If
rs.MoveNext
Loop
With objEmail
.BCC = strTO

.Subject = "**New RFQ**"

.HTMLBody = "Message"

.Attachments.Add ("\\****\****\****\RFQ.pdf")
'.Attachments.Add ("\\****\****\****\Specs.zip")

.Display

End With

Beep

MsgBox "Email has been sent to recipients.", vbOKOnly, "Done."


'DoCmd.SetWarnings -1

End Function

Any thoughts or links that you can point me to that might assist? As a point of clarity, I have a form with an attachments textbox on it that
references the record ID, which should allow me to identify the file to attach to the email (hopefully).


Thanks in advance!

Dan

 

[HiTechCoach]

As far as I know, you will have to export the attachment to a file before you can add it as an attachment to an email.

This is just another reason I have avoided the attachment data type and any method of storing a file in a table except as a BLOB.

 

[HiTechCoach]

And here is some code to help you:

Microsoft® Access 2007 Working with the Attachment DataType

 

[unit213]

As far as I know, you will have to export the attachment to a file before you can add it as an attachment to an email.

This is just another reason I have avoided the attachment data type and any method of storing a file in a table except as a BLOB.

And here is some code to help you:

Microsoft® Access 2007 Working with the Attachment DataType

Thank you for the replies! I'll sift through the link you provided and I'll see if I can use any of the info.

I did consider outputting the file and then attaching the file to the email. Do you happen to have any sample code that I could review for exporting a stored Access attachment?
For my purposes, I'd need to export the file and rename it. I shouldn't have any trouble attaching it to an email...it's just the exporting & renaming part that I need some assistance with.

Thanks for the help!

Dan

 

[HiTechCoach]

Do you happen to have any sample code that I could review for exporting a stored Access attachment?

Dan,

I was thinking ahead and new you were going to ask this. I already provided the answer to this question for you with the link I posted. In the link, check out: Code Example 7.2: Saving an Internal Attachment Back to a File