Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2019, 01:58 PM   #1
jmsjazz
Newly Registered User
 
Join Date: Mar 2013
Location: Cayman Islands
Posts: 43
Thanks: 12
Thanked 0 Times in 0 Posts
jmsjazz is on a distinguished road
Attaching files to email Access 2016

The attached code was written to enable a database form with multiple attached pdfs to be emailed from the command-click button on the form.
Since some machines have been upgraded to the latest versions of Windows 10 and Office 16, I get different errors, such as "User-defined Type not defined" (mostly to do with recordset and savetofile commands). It still works on machines that were not upgraded. I read it may be due to the DAO 3.6 reference library, but I have installed that.
Can anyone please help? I have been going round in circles for hours!
I changed a couple of references to file directories in the below code.
Many thanks in advance..

Code:
Private Sub cmdMAILTEAM_Click()
Dim oAPP As Object
Dim oEMail As Object
' Reference library set to DAO 3.6
Dim rstAttachments As DAO.Recordset
Dim db As DAO.Database
Dim rsParent As DAO.Recordset
Dim rsChild As DAO.Recordset

Set oAPP = CreateObject("Outlook.application")
Set oEMail = oAPP.CreateItem(0)

' Get atttachments ************************************
Set db = CurrentDb
Set rsParent = Me.Recordset
On Error Resume Next
Set rsChild = rsParent.Fields("Attachment").Value

folderspec = "C:\dbtemp"

    With rsChild
    .MoveFirst
    Do While Not .EOF

    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile (folderspec) ' this line fails in Win10/Office 19
    
    .MoveNext
    Loop
    .Close
End With
*********************************************************
Set rsChild = Nothing
Set rsParent = Nothing

Dim OlApp As Object
Dim objMail As Object
Dim stWhere As String   '-- Criteria for DLookup
Dim varTo As Variant    '-- Address for SendObject
Dim stText As String    '-- E-mail text
Dim stWho As String     '-- email to this person
Dim stSubject As String '-- Subject line of e-mail
Dim stDESC As String
Dim stNoreply As String
Dim recid As String
Dim stdatum As Date
Dim stidate As String
Dim yrrecs As Integer
Dim eTotal As Long
Dim i As Long
Dim distro As String    '-- distribution group selected in FORM 

On Error Resume Next 'Keep going if there is an error

'*********************************************************
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String
'*************************************************************
'*************************************************************
'Send email **************************************************
    Set db = CurrentDb
    sSQL1 = "SELECT tblPOI.email, tblPOI.chosen FROM tblPOI WHERE tblPOI.chosen = TRUE"

    Set rs = db.OpenRecordset(sSQL1, dbOpenDynaset)

    Do While Not rs.EOF
        sSQL3 = rs.Fields("EMAIL")
        distro = distro & sSQL3 & "; "
        rs.MoveNext
    Loop
    varTo = Left(distro, Len(distro) - 2)
    If Len(varTo) > 0 Then
        MsgBox "sending to " & varTo
    Set rs = Nothing

    '*********************************************************

    Set OlApp = GetObject(, "Outlook.Application") 'See if Outlook is open *************************

    If Err Then 'Outlook is not open
        Set OlApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook **********
    End If

    'Create e-mail item for distro group ***********************
    Set objMail = OlApp.CreateItem(olMailItem)
    stSubject = ""
    stDESC = Me.DESCRIPTION
    recid = Me.ID
    irec = Form.CurrentRecord
    strecyear = Me.txtrecordno & " of " & Me.txtYear & " (" & "ID " & recid & ")"
    stSubject = InputBox("Type Message Subject or use default: ")
    If Len(stSubject) = 0 Then
        stSubject = ":: New Incident ::"
    End If
    stNoreply = "This is an automated message.  Please do not reply."
    With objMail

    'For each file in "C:\dbtemp" **********************************
        strPath = "C:\dbtemp\"
        strFile = Dir(strPath & "*.*")

        Do While Len(strFile) > 0
            .Attachments.Add strPath & strFile
            strFile = Dir
        Loop
    '******************************************************

jmsjazz is offline   Reply With Quote
Old 11-10-2019, 02:30 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,951
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Attaching files to email Access 2016

In A2016 you should be using the newer reference Microsoft Office 16.0 Access database engine library instead of the DAO 3.6 library
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-10-2019, 05:50 PM   #3
jmsjazz
Newly Registered User
 
Join Date: Mar 2013
Location: Cayman Islands
Posts: 43
Thanks: 12
Thanked 0 Times in 0 Posts
jmsjazz is on a distinguished road
Re: Attaching files to email Access 2016

Thanks for this - but it won't let me remove or switch the Access 15 engine as it is in use - how can I do this?


Last edited by jmsjazz; 11-11-2019 at 04:31 AM. Reason: Thank responder!
jmsjazz is offline   Reply With Quote
Old 11-11-2019, 05:07 AM   #4
jmsjazz
Newly Registered User
 
Join Date: Mar 2013
Location: Cayman Islands
Posts: 43
Thanks: 12
Thanked 0 Times in 0 Posts
jmsjazz is on a distinguished road
Re: Attaching files to email Access 2016

Thanks again for your advice - just found the solution by searching for the 16.olb file in the forum - this was posted here by Ganymede:

Quote:
Thank you all for the help. I believe I have figured it out. The problem was that I had "Microsoft DAO 3.6 Object Library" enabled when I needed "Microsoft Office 12.0 Access database engine Object library" enabled in order to use Recordset2, field2 and Savetofile. Essentially, I had to disable DA0 3.6 and browse for ACEDAO.DLL in C:\Program Files\Common Files\microsoft shared\Office12\. Everything worked after I did that.
I removed DAO3.6 and added the file from the shared\Office16 folder and it worked.

Many thanks -
jmsjazz is offline   Reply With Quote
Old 11-11-2019, 05:46 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,951
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Attaching files to email Access 2016

Hi
Yes - that's exactly what I meant you to do. Sorry if I wasn't clear earlier

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-11-2019 at 05:57 AM.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Attaching Files to Access DB Altin Forms 0 05-24-2012 01:59 AM
Attaching WP, Excel, Adobe files to an Access database Form using a control key mcraven Forms 6 10-23-2006 06:51 AM
Attaching Files in Access 2000 eurodjuro General 1 09-26-2006 11:37 PM
Attaching files to email Ethereal Modules & VBA 1 02-03-2006 09:10 PM
attaching files to email presuming_ed Modules & VBA 10 10-13-2003 07:26 AM




All times are GMT -8. The time now is 08:31 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