Email all attachments in a record using vba (1 Viewer)

virencm

Registered User.
Local time
Today, 11:28
Joined
Nov 13, 2009
Messages
61
Hello,

I am using ms access 2016 and have a table that has attachments as one of my data field s. Each record in the table has approx 6-7 attachments.
What I am trying to do is add All attachments to an email in outlook when I open the form to a particular record and click a button.

Can someone please help with with the code for this?

Thanks in advance.

V
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:28
Joined
Oct 29, 2018
Messages
21,358
Hi. What part do you need help with? You will have to save the attachments to your hard drive first before you can attach them to your email.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:28
Joined
May 7, 2009
Messages
19,169
copy this code in a Module.
Code:
' you need to add Reference to:
' Microsoft Outlook XX.X Object Library (Tools->Reference)
'
' arnelgp
'
'
' Parameters:
'
'   sTable              (string) the name of table where the attachment field can be found
'   pkName              (string) PK or fieldname that uniquely identifies a record
'   pkValue             (variant) the value of the PK to search
'   sAttachmentFiedName (string) the name of attachment field in sTable.
'
Public Function olEmail(sTable As String, pkName As String, pkValue As Variant, sAttachFieldName As String)
    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2
    Dim coll As New Collection
    Dim sPath As String
    Dim sWhere As String
    Dim i As Integer
    
    sPath = Environ("temp") & "\"
    
    Select Case TypeName(pkValue)
    Case "string"
        sWhere = "[" & pkName & "]=" & Chr(34) & pkValue & Chr(34)
    Case "integer", "double", "single", "double"
        sWhere = "[" & pkName & "]=" & pkValue
    Case "date"
        sWhere = "[" & pkName & "]=#" & Format(pkValue, "mm/dd/yyyy") & "#"
    End Select
    
    Dim outlookApp As Outlook.Application
    Dim outlookMail As Outlook.MailItem
    
    Set rsParent = CurrentDb.OpenRecordset( _
                    "select [" & sAttachFieldName & "] from [" & sTable & "] " & _
                    "where " & sWhere, dbOpenSnapshot)
    Set rsChild = rsParent.Fields(sAttachFieldName).Value
    
    With rsChild
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            rsChild.Fields("FileData").SaveToFile sPath & rsChild.Fields("FileName")
            coll.Add sPath & rsChild.Fields("FileName")
            .MoveNext
        Wend
        .Close
    End With
    rsParent.Close
    Set rsChild = Nothing
    Set rsParent = Nothing
    
    Set outlookApp = New Outlook.Application
    Set outlookMail = outlookApp.CreateItem(olMailItem)
    
    With outlookMail
        '.To=
        '.Subject=
        '.BodyFormat=
        '.Body=
        For i = 1 To coll.Count
            .Attachments.Add coll.Item(i)
        Next
        '.Save
        '.Send
        .Display
    End With
End Function
on the click event of your button:
Code:
=olEmal("yourTableName","thePrimaryKeyOntheForm",[textBoxName],"theAttachmentName")
 

virencm

Registered User.
Local time
Today, 11:28
Joined
Nov 13, 2009
Messages
61
Thank you for your prompt reply arnelgp.


Let me try incorporate this in my database and i;ll get back to you soon.


Cheers
Viren
 

virencm

Registered User.
Local time
Today, 11:28
Joined
Nov 13, 2009
Messages
61
Hi Arnelgp,


I am having trouble with the parameters in your code.


for the below parameters


Code:
sTable              (string) the name of table where the attachment field can be found
 '   pkName              (string) PK or fieldname that uniquely identifies a record

 ' pkValue             (variant) the value of the PK to search
  '   sAttachmentFiedName (string) the name of attachment field in sTable.




for stable: my table name that holds the attachments is "Customer_Sem_Table"
for PKname: the field name that uniquely identifies a record is " Sem_No". this is also the primary Key.
for sattachmentfieldname: my field name is called "attachments"
for PKvalue: i dont understand what is required here?


similarly on the click button event:
Code:
=olEmal("Customer_Sem_Table","Sem_No",[SEM_No],"Attachments")
The [text box name] where the primary key is sourced is also called SEM_No.




Can you please help with the PK Value and anything else that may be wrong?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:28
Joined
May 7, 2009
Messages
19,169
do you have the pkField in you form in a textbox?
then substitute the textbox name there.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:28
Joined
May 7, 2009
Messages
19,169
I made some changes to the function.
on the form, the pkName is ID, which is on the textbox, ID also.
my attachment name is "att"

view the form in design view and see the Click event of the button.
 

Attachments

  • attachment.zip
    90.1 KB · Views: 69

virencm

Registered User.
Local time
Today, 11:28
Joined
Nov 13, 2009
Messages
61
Thanks you Arnelgp.This is exactly what i needed.


Really appreciate it!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:28
Joined
May 7, 2009
Messages
19,169
for the Signature part...
before anything else, read the comment on Module1, this will help you a lot.
 

Attachments

  • attachment.zip
    95 KB · Views: 72

Users who are viewing this thread

Top Bottom