Save, rename and email report (1 Viewer)

[dsmith111]

Thank you for your help.

I seem to be having a problem with this part:

Send_Email (strDPath & strFName), "name@mydomain.com"

Here's the full Name file and send Email code.

Public Function Command21_Click()
Dim strRep As String
Dim strDPath As String
Dim strFName As String
' What report to send
strRep = "Run_Notifications"
' Initial Path
strDPath = "S:\Name\Will Call 2012\Sent\"
' Filename
strFName = (DLookup("[Dlr_Code]", "99 - Dealer Email")) & "_Order_" & (DLookup("[MinOfOrder_Number]", "99 - Dealer Email")) & "_thru_" & (DLookup("[MaxOfOrder_Number]", "99 - Dealer Email")) & "_" & Format(Date, "mm-dd-yyyy")
'add file extension
strFName = strFName & ".pdf"
' Output report as pdf
DoCmd.OutputTo acOutputReport, strRep, "PDFFormat(*.pdf)", strDPath & strFName, False, "", 0
' Send the report to whoever
Send_Email (strDPath & strFName), "Name@mydomain.com"
End Function

 

[mtn]

If you have checked that your file path/address is valid then call your function this way:

Send_Email strDPath & strFName, "name@mydomain.com"

and not this way:

Send_Email (strDPath & strFName), "name@mydomain.com"

You don't need the brackets since the file path is passed using a variable name.

 

[dsmith111]

When I take away the brackets i'm getting an error on that line that says I have the wrong number of arguements. Any other ideas?

 

[mtn]

That was why you should check that your file path is valid.
Try sending a file from your desktop first using absoluet path and if it works then your problem is from strFName.

I have tried your code using this

Send_Email "C:\Documents\Doc.pdf", "username@yourdomain.com"

and it worked for me. If your error message says that you have a wrong number of arguments then check that you only supplied a valid file path and and an email account separated by only one comma.

 

[mtn]

Once again I have taken a second look at your code and after another test, it still works for me here. I have changed your code from a Private Sub to a Public Function and commented out the messages that requires information from your "99 - Dealer Email" table so that I can try it here and it wiorked too.

Public Function Send_Email(strDoc As String, strEmailAccount As String)
Dim sTo As String
Dim sCC As String
Dim sBCC As String
Dim sSub As String
Dim sBody As String
Dim strCC As String
Dim OutApp As Object
Dim OutMail As Object
Dim OutAccount As Outlook.Account
Dim varPress As Variant
Dim varStatus As Boolean
' Original code copied from [URL]http://www.ozgrid.com/forum/showthread.php?t=51384[/URL]
'strMess = "You are about to send an email message to ." & (DLookup("[Dlr_Fax]", "99 - Dealer Email")) & vbCrLf & vbCrLf
strMess = "You are about to send an email message to James Baker" & vbCrLf & vbCrLf
strMess = strMess & "Do you wish to continue?"
strStyle = vbYesNo
strTitle = "Send Notificaiton"
varPress = MsgBox(strMess, strStyle, strTitle)
 
If varPress = vbYes Then
    ' Get the email address from the current form control
    sTo = "[EMAIL="jamesbaker@yahoo.com"]jamesbaker@yahoo.com[/EMAIL]"     'DLookup("[Dlr_Fax]", "99 - Dealer Email")
 
    ' Set the subject
    sSub = "Dealer Account Summary"     '(DLookup("[Dlr_Code]", "99 - Dealer Email")) & "_Order_" & (DLookup("[MinOfOrder_Number]", "99 - Dealer Email")) & "_thru_" & (DLookup("[MaxOfOrder_Number]", "99 - Dealer Email"))
    ' Build the body of the email
    ' sBody = "First name from some field" & vbCrLf & vbCrLf
    ' sBody = sBody & "Continue with message"
 
    ' Create the email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sCC = ""
    sBCC = ""
 
    'Determine the correct account
    For Each OutAccount In OutApp.Session.Accounts
 
        If OutAccount = strEmailAccount Then
            varStatus = True
            With OutMail
 
                .To = sTo
                .CC = sCC
                .BCC = sBCC
                .Subject = sSub
                .Body = sBody
                .Attachments.Add (strDoc)
                .Display ' THis will display the email, but not send it
                '.Send ' THis will send the email
 
            End With
        Else
            varStatus = False
        End If
    Next
 
    Set OutMail = Nothing
    Set OutApp = Nothing
 
    If varStatus = False Then
        MsgBox "Email request was cancelled because the account: " & strEmailAccount & " was not found."
    End If
 
End If
End Function

From my immediate window I called the above this way and it worked (remember i have removed my real email address):

? Send_Email ("C:\Users\mtn\Desktop\sampledoc.pdf", [COLOR=#000000]"[/COLOR][EMAIL="username@yourdomain.com"][COLOR=#0066cc]username@yourdomain.com[/COLOR][/EMAIL][COLOR=#000000]"[/COLOR])

I also called this from a command button on a form this way and it worked too:

Private Sub btnSendEmail_Click()
 
Send_Email "C:\Users\mtn\Desktop\sampledoc.pdf", [COLOR=#000000]"[/COLOR][EMAIL="username@yourdomain.com"][COLOR=#0066cc]username@yourdomain.com[/COLOR][/EMAIL][COLOR=#000000]"[/COLOR]
 
End Sub

So can you check your file path again and let us know how best to help you fix the problem? Cheers.

 

[dsmith111]

Thanks for your detailed response. I will make these changes and let you know if it works.

The path should be correct. The code that I started with, using that path worked perfectly. I didn't get the error until I started trying to make these changes to identify the "from" email.

thanks!!!