Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-14-2018, 11:18 PM   #16
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

Hi! Thanks for your swift reply very kind.

No, there was no / but now you mentioned it, it put one there.

Also added explicit in header.

With that line up there, I have no idea what its supposed to do, or how to declare it for that matter.

It ALMOST works.

bruceblack is offline   Reply With Quote
Old 05-15-2018, 03:35 AM   #17
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Send email with attachment from attachment field

Option Explicit forces declaration of variables. It will help with discovering misspelled variables in code.

ALMOST works means what - error message, wrong results, nothing happens?

Still don't know where strReviewWorkOrdeMROR is declared and set. The line with this variable is attempting to open a text file and set variable txtHTML with content of that file. Variable strReviewWorkOrdeMROR is supposed to hold the text file's name.

Error handler code can actually interfere with debugging. Learn how to debug code https://www.techonthenet.com/access/...10/debug01.php
June7 is online now   Reply With Quote
Old 05-15-2018, 05:38 AM   #18
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

Thanks June7!

It's driving me up an ABSOLUTE wall.
I NEEEED this to work and will NOT in ANY way possble.
I have been up with this problem for weeks and im at the end of my rope here.

I have no clue what im doing anymore.
All i wanted to do is send an email with the attachment save in my records.

Option Compare Database
Option Explicit

Private Sub send_Click()
Dim oFilesys, oTxtStream As Object
Dim txtHTML As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim OutlookAttach As Outlook.Attachment
Dim strFileName As String

Dim strt1_customs As String
Dim strReviewWorkOrdeMROR As String

Const PATH As String = "P:\WISSEL\Expeditie\21- Data Expeditie"
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

DoCmd.OpenReport "t1_customs", acViewPreview, , "checklistID=" & Me.checklistID, acHidden
'
' agp
'
' you are missing a backslash on the folder name
' that is why it wasn't deleted
' when this sub exited
'
' create the PATH folder here first
If Dir(PATH, vbDirectory) = "" Then
MkDir PATH
End If

'DoCmd.OutputTo acOutputReport, strt1_customs, acFormatHTML, PATH & strt1_customs & ".HTML", False
Set oFilesys = CreateObject("Scripting.FileSystemObject")
'Set oTxtStream = oFilesys.OpenTextFile(PATH & strReviewWorkOrdeMROR & ".HTML", 1)
'txtHTML = oTxtStream.ReadAll
'oTxtStream.Close
Set oTxtStream = Nothing

Set oFilesys = Nothing
Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = Me.Recordset
rsParent.OpenRecordset
Set rsChild = rsParent.Fields("shipattachment").Value

While Not rsChild.EOF
' we already check the folder above
' so i comment this out
'
' agp
'
''If Dir("H:\t1customs", vbDirectory) = "" Then
''MkDir ("H:\t1customs")
''Else
'''do nothing for the "C:\dbtemp" directory already exists
'''MsgBox "C:\dbtemp\ directory already exists"
''End If

''rsChild.OpenRecordset
'
' arnelgp
'
' save the original filename to variable
strFileName = rsChild.Fields("FileName")
'rsChild.Fields("FileData").SaveToFile (PATH & strFileName)
rsChild.MoveNext
Wend

' dont forget to close what we opened
rsChild.Close
Set rsChild = Nothing


With MailOutLook
.bodyFormat = olFormatRichText
.To = "
bruce.zwarts@cevalogistics.com"
'.CC = " "
.Subject = "Expedition - T1 Inbound"
.HTMLBody = txtHTML
Dim fso As Object, SourceFolder As Object, SourceFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = fso.GetFolder(PATH)
For Each SourceFile In SourceFolder.Files
.Attachments.Add SourceFile.PATH '& "" & SourceFile.Name (SourceFolder.Path contains the path+filename)
Next
' display it here after processing all attachments
.Display

' dont forget to close fso
Set SourceFile = Nothing
Set SourceFolder = Nothing
Set fso = Nothing

'Send email
'.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
'.Send

Kill PATH & "*.*" ' delete all files in the folder
RmDir PATH '"H:\t1customs" ' delete folder
End With
'MsgBox MailOutLook.Body
'Kill " H:\t1customs" & strReviewWorkOrdeMROR & ".HTML"
'email_error:
'MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
'Resume Error_out
''Error_out:


End Sub

bruceblack is offline   Reply With Quote
Old 05-15-2018, 05:40 AM   #19
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

To clarify:

I do NOT have the intention to "make folders" or make them delete or have html docs attachted and what not.

All i want is to send the attachment field inside of a email.
How difficult can this actually be?!?!?!!
bruceblack is offline   Reply With Quote
Old 05-15-2018, 08:34 AM   #20
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Send email with attachment from attachment field

First of all, as already pointed out, need to post code between CODE tags to retain indentation and readability and the \ character will not drop. USE CODE TAGS.

It is necessary to first extract embedded file to a folder before it can be attached to email. You have commented the line that saves file to folder.

I see you now have strReviewWorkOrdeMROR declared but where is it set? This variable is supposed to hold name of text file to be opened. This text file is used to populate variable txtHTML which is used as the message body. If you don't open the text file to populate txtHTML variable, what do you intend to use as the body of the message?

The procedure opens a report but the code to save it as HTML file is commented. This file is supposed to become the content of variable txtHTML. If you don't want the report to be the message body, what do you want? Can construct message in the procedure, so what do you want the message content to be?

Suggest not using PATH as variable or constant name, change to strPath, so as not to confuse with intrinsic property Path.

Step Debug. Read code - does each line make logical sense?

See if this stripped down version helps:
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\DataExpeditie"

'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 = "bruce.zwarts@cevalogistics.com"
    '.CC = " "
    .Subject = "Expedition - T1 Inbound"
    .HTMLBody = "Your text 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
Is 'Expeditie' intential spelling? Is this really correct name of an existing folder?

Last edited by June7; 05-15-2018 at 01:09 PM.
June7 is online now   Reply With Quote
Old 05-16-2018, 12:30 AM   #21
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Thumbs up Re: Send email with attachment from attachment field

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 is offline   Reply With Quote
Old 05-16-2018, 01:09 AM   #22
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

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?

bruceblack is offline   Reply With Quote
Old 05-16-2018, 08:26 AM   #23
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Send email with attachment from attachment field

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
June7 is online now   Reply With Quote
Old 05-24-2018, 12:35 AM   #24
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

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"?

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 = "bruce@logistics.com"
    '.CC = " "
    .Subject = "Expedition T1 -" & Me.contract & ""
    .HTMLBody = "Your text " & Me.company & " 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
bruceblack is offline   Reply With Quote
Old 05-24-2018, 12:47 AM   #25
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Send email with attachment from attachment field

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.
June7 is online now   Reply With Quote
Old 05-24-2018, 01:10 AM   #26
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

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.
bruceblack is offline   Reply With Quote
Old 05-24-2018, 01:46 AM   #27
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Send email with attachment from attachment field

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.
June7 is online now   Reply With Quote
Old 05-24-2018, 01:58 AM   #28
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

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.
bruceblack is offline   Reply With Quote
Old 05-24-2018, 02:30 AM   #29
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Send email with attachment from attachment field

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.
June7 is online now   Reply With Quote
Old 05-24-2018, 02:51 AM   #30
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Send email with attachment from attachment field

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 is offline   Reply With Quote
Reply

Tags
attachment , email , field , form , send

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Send Email with attachment jamesormi Modules & VBA 3 08-14-2010 05:26 PM
email send (attachment ) but only when GaryPanic General 10 03-29-2010 11:03 AM
Send a PDF attachment in email AnnPhil Reports 1 10-01-2007 04:30 PM
Send email attachment mtagliaferri Forms 5 12-08-2006 07:33 AM




All times are GMT -8. The time now is 01:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World