Access module to attach file to email not working

I have been using a module in Access 2010 to generate a PDF from a report and then attach and email using Outlook 2007 to the specified people. I recently setup a new computer. I am using Access 2013 and Outlook 2013. Both computers are Windows 7.

When I run the module the PDF gets created and put into the temp folder as it should. The module completes but the email never happens. See below. I believe it references a module called safesendemail. I've in included that code as well.

Function Send_all_sales_yesterday()
   On Error GoTo Err_Send_Click
 
    Dim mydb As Database, RS As Recordset
    Set mydb = DBEngine.Workspaces(0).Databases(0)
    Dim attachPDF As String
    Dim docname As String, ctl As Control, strTo As String
    Dim path As String, subject As String, body As String
    Dim attach As String, blnSuccessful As Boolean
         
     Application.Echo False
    
    Set RS = mydb.OpenRecordset("qry_email_none")
        
    path = "c:\temp\"
    docname = "all_sales_yesterday"
    attachPDF = path + docname & ".pdf"
    subject = "Daily Stats Report"
    body = "Daily Report is Attached"
    DoCmd.OutputTo acOutputReport, docname, acFormatPDF, attachPDF
 
 Do Until RS.EOF
      strTo = RS!Email
       blnSuccessful = FnSafeSendEmail(strTo, subject, body, attachPDF, "", "")
       RS.MoveNext
    Loop
    RS.Close
             
    Set RS = Nothing
    Set mydb = Nothing
 
exit_send_click:
     
    Application.Echo True
    Exit Function
 
Err_Send_Click:
    Resume exit_send_click
     
End Function

Open in new window


' ACCESS VBA MODULE: Send E-mail without Security Warning
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Last updated v1.3 - 11/11/2005
'
' Please read the full tutorial & code here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning
'
' Please leave the copyright notices in place - Thank you.

'This is a test function - replace the e-mail addresses with your own before executing!!
'(CC/BCC can be blank strings, attachments string is optional)

Function FnTestSafeSendEmail()
    Dim blnSuccessful As Boolean
    Dim strHTML As String
        
    strHTML = "<html>" & _
               "<body>" & _
               "My <b><i>HTML</i></b> message text!" & _
               "</body>" & _
               "</html>"
    blnSuccessful = FnSafeSendEmail("bbistats@jiffylubeca.com", _
                                    "My Message Subject", _
                                    strHTML)
    
    'A more complex example...
    'blnSuccessful = FnSafeSendEmail("myemailaddress@domain.com; secondrecipient@domain.com", _
                                         "My Message Subject", _
                                         strHTML, _
                                         "C:\MyAttachmentFile1.txt; C:\MyAttachmentFile2.txt", _
                                         "cc_recipient@domain.com", _
                                         "bcc_recipient@domain.com")
    If blnSuccessful Then
    
        MsgBox "E-mail message sent successfully!"
        
    Else
    
        MsgBox "Failed to send e-mail!"
    
    End If

End Function


'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
                                strSubject As String, _
                                strMessageBody As String, _
                                Optional strAttachmentPaths As String, _
                                Optional strCC As String, _
                                Optional strBCC As String) As Boolean

    Dim objOutlook As Object ' Note: Must be late-binding.
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
    
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    
    If objOutlook Is Nothing Then
    
        'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True
        'We need to instantiate the Visual Basic environment... (messy)
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
        objExplorer.CommandBars.FindControl(, 1695).Execute
                
        objExplorer.Close
                
        Set objNameSpace = Nothing
        Set objExplorer = Nothing
        
    End If

    blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
                                                strSubject, strMessageBody, _
                                                strAttachmentPaths)
                                
    If blnNewInstance = True Then objOutlook.Quit
    Set objOutlook = Nothing
    
    FnSafeSendEmail = blnSuccessful
    
End Function

Open in new window

Cole100IT Systems ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
>>The module completes but the email never happens.
did you setup a valid Outlook profile in your new computer? can the Outlook in your new computer send out a "test email" successfully ?
0
Cole100IT Systems ManagerAuthor Commented:
Yes, Outlook functions normal and I have sent and received email from it.
0
Ryan ChongCommented:
are you able to track and debug within function FnSafeSendEmail to see if you encountered any errors?
0
Jeffrey CoachmanMIS LiasonCommented:
Why not use the Access Automation to do this?

The basic code is here (as a function)
...but I am sure you can trim it down to work as a simple procedure

Not sure, ...but perhaps your email module does not support Office 2013, or the newer (perhaps 64 bit ) operating systems.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.