Send email with attachments

[rbiggs]

I am trying to adapt the following code and it errors out in the highlighted row. I am not familiar with the syntax to set a DAO recordset could someone point me in the right direction or correct what I have.

Thanks

Private Sub btnEmail_Click()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim strEMailMsg As String


Dim strAttachmentPath As String
Dim rsATT As DAO.Recordset
Dim db As Database

Set db = CurrentDb()
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
strEMailMsg = "Attached..........."
.Subject = "Invoice Number" & [Invoice_Number]
.body = strEMailMsg
Set rsATT = CurrentDb.OpenRecordset("Invoice Query").Fields("Invoice_Attachments")
If rsATT.BOF And rsATT.EOF Then
GoTo noAttach
Else
Do Until rsATT.EOF
strAttachmentPath = rsATT.Fields("FilePath")
.Attachments.Add (strAttachmentPath)
rsATT.MoveNext
Loop
End If
noAttach:
.Display
End With
Set rsATT = Nothing
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

Invoice Query is the dataset for the Form and the field name is Invoice_attachments. I get the error "Type Mismatch". The goal is to email all of the attachments related to an invoice.

[pbaldy]

Try

Set rsATT = CurrentDb.OpenRecordset("Invoice Query")

[rbiggs]

If I take out the reference to the .fields like you suggested it breaks in the Else statement where it is setting the strAttachmentPath

[pbaldy]

What's the error? FilePath is a field in the query? If it could be Null, you'll get an error trying to assign its value to a String variable. Only a Variant can take a Null.

[rbiggs]

Yes it is. It is not required since I may not have an attachment for every record how could I get around this. If I change strAttachmentPath to variant I get a Data Type Conversion error at .Attachments.Add (strAttachmentPath)

[pbaldy]

Then you need to test for it. One way:

If Len(rsATT!FilePath & vbNullString) > 0 Then
  strAttachmentPath = rsATT.Fields("FilePath")
     .Attachments.Add (strAttachmentPath)
End If

Though looking at the code, it might make more sense to eliminate them from the query results.

[rbiggs]

Its still not working. I don't think I truly understand what the code is doing. Adding the If statement to account for 0 length filepaths works however the code is never seeing the attachments in the recordset.

[pbaldy]

Can you post the db, or a representative sample?

[rbiggs]

Here is what I am working with. The code is on the DispatchMain form. Thanks for your help

[pbaldy]

For starters, in this file there is no code associated with the button. The button is btnEmail and the code is:

Private Sub btnEmail2_Click()

I have to confess to assuming your field held the path to an attachment, not an embedded attachment. I don't like those because they bloat the database, and are unavailable anywhere else. I store a path, so users can open the file via other means but I can get at it from Access. It appears the code is intended for that, given the reference to path. I've never used the attachment data type, but I'll poke around and see if I can find out how to access them for an email.

[pbaldy]

Okay, a quick search would appear to suggest you can't email them directly. See the post here:

http://answers.microsoft.com/en-us/o...f-fb31fb491ed9

by Daniel Pineault, a few down from the top. Basically it sounds like you need to save the file to disk, use that path for the email attachment, and delete the file when you're done.

[rbiggs]

I have attached an updated copy. I think that it would be better not to use the embeded attachments and instead pass a string. I have changed the Invoice Query to create to Paths (Path and Path2) to a location on my computer. The code still seems to skip the .Attachments.Add thank you for your help

[pbaldy]

You changed the logic flow. The attachment code is inside an If/Then that tests for EOF. In other words, it would only run when the recordset is empty, the opposite of what you want. Plus you have the GoTo right before it, so it would never run anyway. You had an "Else" in there before.

As an aside, you test the first path field before attaching, but not the second. You'll likely get an error if the first field is populated and the second isn't.