Send email with attachment from attachment field (1 Viewer)

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
Holy S***

It works...

Thanks for narrowing it down there June! Youre a soul saver.
Yes Expeditie is a Dutch translation :)

I have no idea what happend but i will investigate your code and see what i did wrong. (lots of things).

At least i have some room to breath again. Thanks you a million times June7!
 

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
There's one thing i need to fix. Somewhere in the record set.

This event happens on a subform. So it takes the values from the source table. (table1)

This table is related to another table. (table2)
How can i fetch those values in my recordset?

I want some values in my email body and subject. Which works fine, but i can only use the values from the table the send button resides. :S?
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
What is the 'other' table - a lookup table? Is this table the source for combobox on form? Possible options:

1. refer to combobox column holding the required value

2. include other table in the form RecordSource

3. DLookup() in textbox or VBA
 

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
Hey guys. Thanks for all the help. I tried everything i could wrap my head around and it still doesn't work. My brain hurts...

Marked in red below, there's Me.company
"datamember not found" it says (logically because its in a different table)
This value comes from the table "drivers".
Currently it's using the table "shipments" (which i also need!)

Can someone help including the table "drivers"? :banghead:

Code:
Private Sub send_Click()
Dim appOutLook As Outlook.Application, MailOutLook As Outlook.MailItem
Dim rsParent As DAO.Recordset2, rsChild As DAO.Recordset2
Dim fso As Object, SourceFolder As Object, SourceFile As Object
Const strPath As String = "P:\WISSEL\Expeditie\21- Data Expeditie\Expeditool\temp"
'create the attachments folder
If Dir(strPath, vbDirectory) = "" Then
    MkDir strPath
End If
'extract files from table and save to folder
Set rsParent = Me.Recordset
rsParent.OpenRecordset
Set rsChild = rsParent.Fields("shipattachment").Value
While Not rsChild.EOF
    rsChild.Fields("FileData").SaveToFile (strPath & "\" & rsChild.Fields("FileName"))
    rsChild.MoveNext
Wend
rsChild.Close
Set rsChild = Nothing

'open email and attach files
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "[EMAIL="[email protected]"][email protected][/EMAIL]"
    '.CC = " "
    .Subject = "Expedition T1 -" & Me.contract & ""
    .HTMLBody = "Your text " & [B][COLOR=red]Me.company[/COLOR][/B] & " here."  'or restore code to export report to HTML and open and read file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder(strPath)
    For Each SourceFile In SourceFolder.Files
        .Attachments.Add SourceFile.PATH 'SourceFolder.Path contains the path+filename
    Next
    .Display 'display email after processing all attachments
    '.DeleteAfterSubmit = True 'this would let Outlook send email without saving to Sent bin
    '.Send
    'close fso objects
    Set SourceFile = Nothing
    Set SourceFolder = Nothing
    Set fso = Nothing
End With
Kill strPath & "\*.*" 'delete all files in the folder
RmDir strPath 'delete folder
End Sub
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
Does the code work if you don't have the Me.company concatenation?

Do Shipments and Drivers have a relationship? I already suggested 3 methods of pulling related data.
 

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
Hi June7

Yes! Code works perfectly without it. Thanks to you.
And yes, the 2 tables are related by checklistID.

I tried fiddling around with your suggestions which seem perfect, i just cant get it to work. When it DOES work, i can study it and see the logic.
But at this point, i tried so many things, i dont know whats front or back anymore.
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
And exactly what did you do?

This code is behind a form that has the checklistID field? If they are related by checklistID, which table has the primary key and which has the foreign key? How is checklistID the relating value? I would expect DriverID to be linking value. You need to give more info on data structure.
Do you have a combobox on the form for selecting driver?
Did you include the Drivers table in the form RecordSource?
Did you try DLookup()?

Post the expression(s) you attempted.
 

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
I have a button on a form where the record source is the table "drivers"
I want to unclude some of the values from "shipments" inside my email subject and email body.

The main table is "drivers". The unique field here is in relation with the child table "shipments" by "checklistID".

It's hard to describe what i tried, since i have no clue and i am just TRYING. For example i tried pulling up both tables in the recordset.
FROM tbldrivers and tblshipments.

I did what you suggested, making a query that has both tables with the fields that i need, but i dont know how to implement this into the code.
I know how to use Dlookup on fields, but not in this complicated code with recordsets.
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
If both tables are in the form RecordSource and Company is a field from one of the tables and a textbox is bound to that field, then Me.Company should work. Or try Me!Company instead. I still don't understand data structure. My head is starting to hurt. Might provide db for analysis.
 

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
Ok, so i forgot:

The send button is on a SUBFORM. The subform's source is the table "shipments". Which is a parent from "drivers".

They are bound by checklistID

The field company is in the "drivers" table.
 

bruceblack

Registered User
Joined
Jun 30, 2017
Messages
115
Solved it thanks to your pushing my brain lol

.HTMLBody = "Your text " & Forms!registration.company.Value & "Yes" & " here." 'or restore code to export report to HTML and open and read file

Thank you so much for sticking with me!
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom