UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> CDO Send Mail With Attachment Error, Access 2010    
 
   
LilAnnCC1
post Dec 26 2017, 10:57 AM
Post#1



Posts: 739
Joined: 31-May 04
From: Wisconsin, USA


Hello all!

I'm in the process of rewriting all my email procedures from using outlook to using CDO in Access 2010 VBA. I've successfully managed to get a few of those procedures to run--as long as they don't have attachments (access reports converted to PDF).

I have a public module:

CODE
'Set up constants for CDO email
Public Sub SendEmail(strTo As String, ByVal strCC As String, strSubject As String, strBody As String, Optional ByVal strAttach As String, Optional ByVal strMBox As String)
'Set Constants
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
Const cdoBasic = 1
Const cdoSendUsingMethod = "http://schemas.microsoft.com/CDO/configuration/sendusing"
Const cdoSMTPServer = "http://schemas.microsoft.com/CDO/configuration/smtpserver"
Const cdoSMTPServerPort = "http://schemas.microsoft.com/CDO/configuration/smtpserverport"
Const cdoSMTPUseSSL = "http://schemas.microsoft.com/CDO/configuration/smtpusessl"
Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/CDO/configuration/smtpauthenticate"
Const cdoSendUserName = "http://schemas.microsoft.com/CDO/configuration/sendusername"
Const cdoSendPassword = "http://schemas.microsoft.com/CDO/configuration/sendpassword"
Const cdoServer = "mail.XXXXXXX.com"
Const cdoUser = "XXXXXXX"
Const cdoPassword = "XXXXXXX"
Const cdoFrom = "XXXXXXX.com"


Dim cdoC As Object          'Configuration
Dim cdoM As Object          'Message
On Error Resume Next

Set cdoC = CreateObject("CDO.Configuration")
Set cdoM = CreateObject("CDO.Message")

cdoM.To = strTo
cdoM.CC = strCC
cdoM.From = cdoFrom
cdoM.Subject = strSubject
cdoM.TextBody = strBody
If strAttach <> "" Then
    cdoM.AddAttachment = strAttach
End If

cdoM.Configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/sendusing") = cdoSendUsingPort
cdoM.Configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpserver") = cdoServer
cdoM.Configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 25
cdoM.Configuration.Fields(cdoSendUserName).Value = cdoUser
cdoM.Configuration.Fields(cdoSendPassword).Value = cdoPassword
cdoM.Configuration.Fields.Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True
cdoM.Configuration.Fields("http://schemas.microsoft.com/CDO/configuration/smtpconnectiontimeout") = 60
cdoM.Configuration.Fields.Update
cdoM.Send

If Err.Number <> 0 Then
    MsgBox Err.Number & " Error in sending mail. " & Err.Description
        Else
    MsgBox strMBox
End If

Set cdoM = Nothing
Set cdoC = Nothing

End Sub


My code that works with this is:
CODE
Private Function EmailNotes()
'Email users of their notes that they have marked for followup.
'Send it once per day until they manually close the note!

Dim rst As DAO.Recordset
Dim strMessage As String
Dim strTitle As String
Dim strAcct As String
Dim strWho As String

Set rst = CurrentDb.OpenRecordset("q3_Note_Followup")

strMessage = "Reminder for Note Followup! " & rst![NoteEntityName] & _
                          " has a follow-up date of " & rst![noteFollowUpDate] & ". Note: " & rst![nNote]
strAcct = "xxxxxx.com; xxxxx.com; xxxx.com"

With rst
    If rst.EOF And rst.BOF Then
        'Do nothing as there are no records
    Else
    
    strWho = rst![CreatedBy] & "@xxx.com"
    
        Do Until rst.EOF
            If rst![NoteTitle] = "Order needs Accounting Approval!" Then
                Call SendEmail(strWho, strAcct, rst![NoteTitle], strMessage)
            Else
                Call SendEmail(strWho, "", rst![NoteTitle], strMessage, , "Note Followup Sent!")
                
            End If
                
         rst.Edit
         rst![Email_Sent] = Date
         .Update
         rst.MoveNext
         Loop
    End If
End With

rst.Close
Set rst = Nothing

End Function


This code, however, because there is an attachment, gives me a 438 error or a -2147220974 The transport lost its connection to the server error.

CODE
Public Function LargePO()
'Send emails to accounting if there are Large Purchase orders
Dim rst As DAO.Recordset
Dim strPath As String               'Path to reports
Dim strReport As String             'Access report name including prefix
Dim strName As String               'Full report name
Dim strTemp As String               'Add records to temp table
Dim strSQL As String                'Update Notified field to true in PO table
Dim strTitle As String              'Email Subject
Dim strWho As String                'Email to
Dim strAtt As String                'Attachment
Dim strMessage As String            'Body of email


strTemp = "INSERT INTO tmp_POLargeOrders ( POID, poLargeOrder ) SELECT tblPO.POID, tblPO.poLargeOrder FROM tblPO INNER JOIN q002_PO_LargeOrderTotal ON tblPO.POID = q002_PO_LargeOrderTotal.POID;"
strSQL = "UPDATE tmp_POLargeOrders INNER JOIN tblPO ON tmp_POLargeOrders.POID = tblPO.POID SET tblPO.poLargeOrder = -1;"
strTitle = "Large Purchase Order Notice"
strWho = "xxxx.com"
strMessage = "Attached report is showing one or more large Purchase Orders."

Set rst = CurrentDb.OpenRecordset("q002_PO_LargeOrderTotal")

With rst
    If rst.EOF And rst.BOF Then
        'do nothing as there are no records
    Else
        'Create the report
            strPath = "\\FS\General Office\BFM_Database\LPO\"
            strReport = "pvtA_Large Purchase Orders"
            strName = Mid$(strReport, 6) & "_" & Format$(Date, "yyyymmdd") & ".pdf"
          
            DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strName, False
            
            strAtt = strPath & strName
            
        'Send email
            Call SendEmail(strWho, "", strTitle, strMessage, strAtt, "Send Large PO!")
        Do Until rst.EOF
            rst.MoveNext
            Loop
        
        fEQ strTemp, dbFailOnError
        fEQ strSQL, dbFailOnError
        fEQ "DELETE * FROM tmp_POLargeOrders", dbFailOnError
        
    End If
End With

rst.Close
Set rst = Nothing

End Function



Please note that I haven't written any error procedures and I've changed some code to xxxxxx to hide certain information.

Can anyone help with getting attachment to go? The email actually comes in, but there is no attachment, even though I can clearly see the file in the windows explorer.

I have several more procedures to rewrite, some with more than 1 attachment.

Thank you for your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Dec 26 2017, 11:08 AM
Post#2


UtterAccess VIP
Posts: 10,140
Joined: 6-December 03
From: Telegraph Hill


You have spaces in your attachment path

Try adding extra quotes around the passed path:
CODE
' ...
  cdoM.AddAttachment = Chr(34) & strAttach & Chr(34)
' ...


Just a guess ...

d

--------------------


Regards,

David Marten
Go to the top of the page
 
PhilS
post Dec 26 2017, 01:37 PM
Post#3



Posts: 484
Joined: 26-May 15
From: The middle of Germany


QUOTE
CODE
CODE
' ...
  cdoM.AddAttachment = Chr(34) & strAttach & Chr(34)
' ...



Just a guess ...

While we're at guessing...
AddAttachment does not sound like a property to me. I would rather assume a method and would remove the equal sign.
CODE
cdoM.AddAttachment strAttach

If that doesn't work, try adding the quotes, as cheekybuddha suggested.
This post has been edited by PhilS: Dec 26 2017, 01:37 PM

--------------------
Go to the top of the page
 
cheekybuddha
post Dec 26 2017, 06:37 PM
Post#4


UtterAccess VIP
Posts: 10,140
Joined: 6-December 03
From: Telegraph Hill


Spot on Phil!

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Dec 27 2017, 08:07 AM
Post#5



Posts: 739
Joined: 31-May 04
From: Wisconsin, USA


Thank you all for your help, but still not working for me.

In the "attachment" procedure, I am clearly making a PDF file. I can see it in the windows explorer window in the folder I am directing the procedure to output the file to.

I am getting error -2147024894 The system cannot find the file specified.

When I step through the procedure, I am seeing the attachment as:

"\\FS\General Office\BFM_Database\LPO\Large Purchase Orders_20171227.pdf"

BTW, I removed the = from the statement: cdoM.AddAttachment strAttach

Where am I going wrong?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
LilAnnCC1
post Dec 27 2017, 08:12 AM
Post#6



Posts: 739
Joined: 31-May 04
From: Wisconsin, USA


Nevermind! I had rem'd out my timeout connection. Once I put that back in the email had the attachment!

Can you all see me doing my happy dance! Rolling on the floor and jumping for joy!

Thank you all for your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 05:01 AM