Help with VBA script to Email Attachment data type

[Nancythomas]

I have searched all over and am in need of some assistance. I have a database that has the table [tblAttachments] to store various attachments. In this table I have a primary key [ItemNumber] and an attachment data type field. This table holds all attachments for a Customer/Record.
What i am trying to accomplish is being able to code a command button to send the Attachment file (eg. various type of files .doc; .exl; .jpg etc) as an attachment in an email.
I dont know if sendobject can do what i am asking.
I have found some info on saving them to the local harddrive but this is not going to work for my specific needs.
Any and all help or push in the right direction is greatly appreciated.

 

[GaryPanic]

See if this helps anything in a [] is a field on the form Quoteform
some of the [] i should of named properly
anyway the bottom part

EmailSend.Attachments.Add (Me.[Attach2a])

Attach2a = a field on my form that is a drop down list from a table that table has a location field on it
Drive\ folder \ filename.doc or pdf or whatever
I select from the drop down and this caputers the location of the file push the email button and the files is attached

(in my case i have up to 4 files that can be selected independent of each other ) so the there is a filter ont e end Attach2a only shows files with a K filter on them
Attach3 has a code of P - the code are not inportant - but allow me to load up x number of files and then be able to filter them in to the right slot


SELECT Attachmenttbl.Location, Attachmenttbl.Filename, Attachmenttbl.Attachmenttblatuo, Attachmenttbl.Filter FROM Attachmenttbl WHERE (((Attachmenttbl.Filter)="K")) ORDER BY Attachmenttbl.Filename;




frm As Form

Const bq As String = "<blockquote>"
Const bqe As String = "</blockquote>"

Dim EmailApp As Object, NameSpace As Object, EmailSend As Object


Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

Set frm = Forms!Quotefrm


With frm

EmailSend.To = !
' EmailSend.bcc = "test'test.co.uk"


EmailSend.Subject = " Our Reference :-" & " " & ![xxxx] & " - " & "(" & ![xx] & ")"



EmailSend.HTMLBody = "whatevers "
>"


End With



If Len(Me.[attachmentfld] & vbNullString) <> 0 Then
EmailSend.Attachments.Add (Me.[attachmentfld])
End If
If Len(Me.[Attach2a] & vbNullString) <> 0 Then
EmailSend.Attachments.Add (Me.[Attach2a])
End If
If Len(Me.[attach3] & vbNullString) <> 0 Then
EmailSend.Attachments.Add (Me.[attach3])
End If
If Len(Me.[Attach4] & vbNullString) <> 0 Then
EmailSend.Attachments.Add (Me.[Attach4])

'emailBcc

'Attach4
End If

'carbon copy


EmailSend.Display

 

[GaryPanic]

my attachment table

Location =S:\folder\sub folder\another subfolder1\filename.pdf
Filename = name of the file
Attachmenttblatuo= id number
Filter = K

 

[Nancythomas]

Thanks for the help, but this is not what I am looking for.

I want help on how to email attachments via ms access 2010. The attachment field is 'attachment data type' and the attachment is stored inside the database. How can access automatically select a record and email all attachments that is stored in the access database by a click of a button command.
In short I want to send email attachment that is stored in a "field attachment data type". This is all MS access 2010.

See sample screen display

 

[vbaInet]

Upload a sample database with a table and some attachments and I'll write code to get you started (if I have a minute).

By the way, in the future, save your documents to a drive instead of in the attachment field. You can save the path to the individual files.

And welcome to AWF!

 

[Nancythomas]

Thank you all for helping me. The below script works, but I have two problems
1) If you have more than one attachment, it only move the first attachment to my C:\projects folder. I would like it to move all the attachments for that record in c:\projects folder.
2) I am also getting an error message where I am unable to find a way to clear this. Run-Time error '-2147024773(8008007b)': File name or directory name is not valid. The error on this line .Attachments.Add (strAttachementPath)

Here is the code:
Private Sub cmdEmail_Click()
Dim outlookApp As Outlook.Application
Dim outlookNamespace As NameSpace
Dim objMailItem As MailItem
Dim objFolder As MAPIFolder
Dim strAttachementPath As String
Dim rst As DAO.Recordset2
Dim rstAttachment As DAO.Recordset2
Dim db As DAO.Database
Dim strHTML

'Call SaveAttachment
Set outlookApp = CreateObject("Outlook.Application")
Set outlookNamespace = outlookApp.GetNamespace("mapi")
Set objFolder = outlookNamespace.GetDefaultFolder(olFolderInbox)
Set objMailItem = objFolder.Items.Add(olMailItem)
Set db = CurrentDb
Set rst = db.OpenRecordset("Attachment_Tbl", dbOpenDynaset)
rst.FindFirst "AVLOGID = " & Me!AVLOGID
Set rstAttachment = rst.Fields("Attachment").Value
'strAttachementPath = CurrentProject.Path & "\Attach\" _
' & rstAttachment.Fields("Filename")

' Build the Email to be sent
With objMailItem
.BodyFormat = olFormatHTML
.To = "Email address"
.Subject = "Site Inspection for " & [AVLOGID] & " At " & [Date]
' .Body = "Some text here"
.HTMLBody = strHTML
' Grab Attachments for Email if there are any
If rstAttachment.RecordCount > 0 Then
Call SaveAttachment
strAttachementPath = CurrentProject.Path & "C:\Projects" _
& rstAttachment.Fields("FileName")
.Attachments.Add (strAttachementPath)

End If
.Display
End With

outlookApp.ActiveWindow
'SendKeys ("%s")
MsgBox "Mail Sent!", vbOKOnly, "Mail Sent"
End Sub

 

[GaryPanic]

I've bailed out -
vbaInet - can run rings round me - I can hack things together picked from various people - but anything creative ....

regards

G

 

[vbaInet]

Going back to my request...

Upload a sample database with a table and some attachments and I'll write code to get you started (if I have a minute).

@Gasman: I'll run out of steam before I complete the loop

 

[Nancythomas]

Thanks for help me
I have attached a test database.
I would like a button command to pick the current form (AVLOG_Frm) export all attachment for a current record to a folder and email the attachment. Delete the file in the folder after the email is sent
I tried to make this work but failed.

Attached is the sample database .

 

[vbaInet]

See attached. I've not tested it much, so test it extensively.

 

[Nancythomas]

Well you are GOD sent. Thank you very very very much. I was trying to do this for the last 15 days and was unable to get any help. I am so happy with your codes.
I appreciate all your help. Thank you again.

 

[Nancythomas]

Thanks for helping me. I tried to add a record and run the code, but when I click on the email button I get an error message. I have attached the error. Please help me again. Error is Run-Time error '-2147024893(80070003)': Path does not exist. Verify the path is correct. How can I fix this ????

 

[vbaInet]

It would appear that such a small recordset isn't populating fully, so add these two lines:

        With rstAttach
[COLOR="Blue"]            .MoveLast
            .MoveFirst[/COLOR]
            
            If .RecordCount > 0 Then

 

[Nancythomas]

Thanks. As I am very new to this programming can you still help me.
I added the script as you suggested, but I am still getting an error. the error now is "compile error - End With Without With
Please help me again.
Thanks again

 

[vbaInet]

Thanks. As I am very new to this programming can you still help me.

You need to start learning!

See attached. And because you're a beginner, I've also included some very basic error handling, so after you've tested it and you're satisfied, enable the error handling by removing the apostrophe on this line in both subs:

[COLOR="Red"]'[/COLOR]On Error GoTo Err_Catch

 

[Nancythomas]

Thank you very much again. I hope this is the last question.
The scripts you provided work well. I would also like to add the below in the existing script and I do not know where to add it. Can you please assist me with this one please.
Along with all the attachments I am emailing, I would also like to attach a report from the database as a pdf..
I am not sure where to add the below script in the above lot.

DoCmd.SendObject acSendReport, "FullReport", acFormatPDF, "", , , "", , True

 

[vbaInet]

First look into DoCmd.OutputTo

 

[Nancythomas]

thanks for helping me.
i akso would like to attach a report in this scipt, but i dont know where to insert this script.
DoCnd.sendobject as sendreport, ("Reportname"), aspdf
pl help me with this.

 

[vbaInet]

Repeat :

First look into DoCmd.OutputTo

 

[Nancythomas]

You have helped me a lot. One more last help please.
I am trying to also attach a report along with the other attachments as well.
I am attaching my a test database. Can you please fix this for me as I do not know here to add the command DoCmd.output in the existing script.