Issues sending email with attachment

[lespritdelescalier]

Hi everyone,

I am working with an Access 2003 application, and one of the functions of the program is to create a csv file, and then send it to a list of email addresses which are stored in a database table.

The procedure that creates the csv file works every time. The csv is created, and it has the correct data in it.

The procedure that creates and sends the email works sometimes, and fails sometimes. Here are the tests I've done, and their results:

1. Send the email to 3 internal addresses - WORKS
2. Send the email to 1 internal address, 1 hotmail address, 1 gmail address - WORKS
3. Send the email to 2 internal addresses, 2 external addresses for the client - FAILS
4. Send the email to 2 internal addresses, 2 external addresses for the client while manually executing the code in debug mode - WORKS

I've confirmed that sending emails to both internal and external addresses works just fine. However, sending to the client's email addresses does not work unless I step through the code line by line.

Any ideas? Thanks for any help you can provide!

 

[Cronk]

Post your code.

 

[lespritdelescalier]

Thanks. Here is the main procedure:

Public Sub Prep_Daily_Summary(strDate As String)

    strSender = "email@email.com"
    strRecipient = getEmailsForDailySummary(TargetCompanyID) 
    strSubject = "Email title " & strDate
    strAttachment = "\\CSVPath\" & strDate & ".csv"
    strMessage = ""
    
    Call Send_Email_With_Attachment(strSender, strRecipient, strSubject, strMessage, strAttachment)

End Sub

Function to return email addresses:

Private Function getEmailsForDailySummary(strCompanyID As String) As String
    
    Dim db As Database
    Set db = CurrentDb
    
    Dim rs As Recordset
    Set rs = db.OpenRecordset("select EMAIL_ADDRESS from tbEMAIL_LIST where COMPANY_ID = '" & strCompanyID & "'")
    
    If rs.RecordCount > 0 Then
    
        rs.MoveFirst
        Do While Not rs.EOF
        
            getEmailsForDailySummary = getEmailsForDailySummary & rs!EMAIL_ADDRESS & ";"
            rs.MoveNext
            
        Loop
        
    End If
    
    rs.Close
    Set rs = Nothing
    
End Function

Procedure which sends the email:

Public Sub Send_Email_With_Attachment(sSender As String, sRecipient As String, sSubject As String, sMessage As String, sAttach As String, Optional sCopyTo As String)

    Dim objEmail
    Set objEmail = CreateObject("CDO.Message")
    objEmail.FROM = sSender
    objEmail.To = sRecipient
    objEmail.Subject = sSubject
    objEmail.Textbody = sMessage
    
    objEmail.AddAttachment sAttach
    
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "emailServerName"
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "path\info"
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    objEmail.Send

End Sub

 

[Minty]

This sounds like a possible permissions problem.

Can you send to a single External email address through the code, without debug / single stepping? I have a feeling the answer will be no.

 

[lespritdelescalier]

You're correct, it did fail. I understand that it could be a permissions problem, but I'm going to have difficulty explaining that to my network admins when I've successfully sent to external email addresses (as noted in my examples above). Can you help me understand why this might be happening in some instances but not others?

Thank you!

 

[Minty]

When you say it fails what exactly happens?
I don't use CDO, but am vaguely familiar with mail server protocols. My uneducated guess is that the CDO mail server is not configured to allow an application to send mail from the sender being specified. When you single step through it what sender address are you using compared to when the code is run automatically?

 

[lespritdelescalier]

Okay, so I figured out what the issue is.. I think.

First, I checked the relay server's email logs, and noted that when I was receiving a fail, nothing was even getting to the server. I was using the same email address when stepping through the code, and when executing the whole thing. Both were gmail addresses.

I put a sleep between the call to create the file and the call to create the email/send the file. At 5 seconds, the send failed, but at 10 seconds, the send completed. I'm thinking that the code is executing too quickly, and the file is not finished writing before the call to send the email.

I'll see if I can figure out how to determine if the file is attached properly before sending the message and see if that helps.

If you have any additional thoughts, I'd love to hear them.

Thanks again for your help!

 

[Minty]

You could use a loop to check to see if you can open the file (Search on here for some VBA) as I'd assume you can't open it until it's created.

 

[lespritdelescalier]

You could use a loop to check to see if you can open the file (Search on here for some VBA) as I'd assume you can't open it until it's created.

Thanks! I ended up doing exactly that.