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
> Vba For Emailing Reports Not Quite Working, Access 2013    
 
   
ebroomhead
post Mar 13 2019, 02:13 PM
Post#1



Posts: 14
Joined: 19-May 15



I have a database that returns an AR report for exceptions such as over credit limit, past dues, etc. I want each salesperson to get a copy of only their customers by Outlook email. I managed to find some code that did what I wanted after a bit of tweaking. When I run the code, emails for each salesperson are set up with only their report as an attachment, however, the very first salesperson ends up with an email and their proper report except the "To:" on the email is blank. As I mentioned earlier, all other salespersons work fine. The code I am using is below and any help I can get to solve this problem would be very much appreciated.

Private Sub Command42_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim temp As String
Dim strReportName As String
If Forms![main menu]![Text9] <> 0 Then

Set db = CurrentDb()
DoCmd.SetWarnings False
DoCmd.OpenQuery "sales reps for current ar_EXCEPTRPT", , acEdit
DoCmd.SetWarnings True
Set rs = db.OpenRecordset("SELECT [saname] FROM [tbl sales reps for current ar_exceptrpt]", dbOpenSnapshot)

Do While Not rs.EOF
strReportName = "Outstanding AR by Rep_exceptrpt"
temp = rs("saname")
DoCmd.OpenReport "Outstanding AR by Rep_exceptrpt", acViewReport, , "[saname]='" & temp & "'"

DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, CurrentProject.Path & "\" & strReportName & ".pdf", False

Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)

strto1 = Reports![outstanding ar by rep_exceptrpt]![text88] & ";" 'removed a ' before the &

'Remove Trailing ';'
strto = Left$(strto1, Len(strto1) - 1)


'******************************* USER DEFINED SECTION ********************************
'StrMessageBody = "This is the report for - "
strSubject = Reports![outstanding ar by rep_exceptrpt]![text53] & " for " & Reports![outstanding ar by rep_exceptrpt]![SANAME] & " as of " & Date
'****************************************************************************
*********
With oMail
On Error Resume Next
.to = strto

.Body = strMessageBody
.Subject = strSubject
.Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"

.Display 'VIEW email before sending
' .Send 'Immediately Sends the E-Mail without displaying Outlook
End With

Set oMail = Nothing
Set oLook = Nothing

'rst.Close
'Set rst = Nothing
'DoCmd.Close acReport, "outstanding ar by rep"

'DoEvents

rs.MoveNext
Loop
DoCmd.Close acReport, "outstanding ar by rep_exceptrpt"
rs.Close
Set rs = Nothing
Set db = Nothing
Else: MsgBox ("Days past due cannot be zero")
End If
End Sub








Go to the top of the page
 
DanielPineault
post Mar 13 2019, 04:17 PM
Post#2


UtterAccess VIP
Posts: 6,455
Joined: 30-June 11



Considering the To is taken from

CODE
strto1 = Reports![outstanding ar by rep_exceptrpt]![text88] & ";"


Does that control have the proper e-mail address when you generate it?

Also, why remove the trailing ; that you just inserted in the line above? If you don't want it, then don't concatenate it .

Furthermore, blindly truncating the string is very dangerous.

CODE
strto = Left$(strto1, Len(strto1) - 1)


If you absolutely want to then you'd be better off doing

CODE
strto = Replace(strto1, ";", "")


Or perform a check for the ; by using InStr(strto, ";") <> 0








--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ebroomhead
post Mar 14 2019, 11:35 AM
Post#3



Posts: 14
Joined: 19-May 15



Thanks Daniel, I'll check your comments and suggestions when I can get back on my internet connection. I don't think that I do need the ; at the end of strto1 variable. As I mentioned, I got this as a snippet.


Regards
Ed

Go to the top of the page
 
DanielPineault
post Mar 14 2019, 12:26 PM
Post#4


UtterAccess VIP
Posts: 6,455
Joined: 30-June 11



Here's a version more along the lines of what I'd be trying

CODE
Private Sub Command42_Click()
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim oLook                 As Object    'Outlook.Application
    Dim olns                  As Object    'Outlook.NameSpace
    Dim oMail                 As Object    'Outlook.MailItem
    Dim temp                  As String
    Dim strReportName         As String
    Dim strto                 As String
    Dim StrMessageBody        As String
    Dim strSubject            As String

    On Error GoTo Error_Handler

    If Forms![main menu]![Text9] <> 0 Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "sales reps for current ar_EXCEPTRPT", , acEdit
        DoCmd.SetWarnings True

        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT [saname] FROM [tbl sales reps for current ar_exceptrpt]", dbOpenSnapshot)
        If rs.RecordCount <> 0 Then
            Set oLook = CreateObject("Outlook.Application")
            Set olns = oLook.GetNamespace("MAPI")
            strReportName = "Outstanding AR by Rep_exceptrpt"
            Do While Not rs.EOF
                temp = rs("saname")
                DoCmd.OpenReport strReportName, acViewReport, , "[saname]='" & temp & "'"
                DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, _
                               CurrentProject.Path & "\" & strReportName & ".pdf", False

                '******************************* USER DEFINED SECTION ********************************
                strto = Reports![outstanding ar by rep_exceptrpt]![text88] & ";"    'removed a ' before the &
                If Right(strto, 1) <> ";" Then strto = strto & ";"
                'StrMessageBody = "This is the report for - "
                strSubject = Reports![outstanding ar by rep_exceptrpt]![text53] & " for " & _
                             Reports![outstanding ar by rep_exceptrpt]![SANAME] & " as of " & Date
                '****************************************************************************
*********

                Set oMail = oLook.CreateItem(0)
                With oMail
                    On Error Resume Next
                    .To = strto
                    .Body = StrMessageBody
                    .Subject = strSubject
                    .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
                    .Display    'VIEW email before sending
                    ' .Send 'Immediately Sends the E-Mail without displaying Outlook
                End With
                Set oMail = Nothing

                DoCmd.Close acReport, strReportName
                rs.MoveNext
            Loop
        End If
    Else
        MsgBox ("Days past due cannot be zero")
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not oMail Is Nothing Then Set oMail = Nothing
    If Not olns Is Nothing Then Set olns = Nothing
    If Not oLook Is Nothing Then Set oLook = Nothing
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub

Error_Handler:
    'LogError Err.Number, Err.Description, sModName & "\Command42_Click", , True, Erl
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Command42_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub


Try to give meaningful names to your controls so you know what you are looking at when you are reviewing your code. Command42 doesn't tell you much, but cmd_SendEmailToSaleman let's you know exactly what the control is/does. Always think that today it is fresh in your memory, but in 6 months, will you remember Command42!



--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
datAdrenaline
post Mar 14 2019, 02:50 PM
Post#5


UtterAccess Editor
Posts: 17,968
Joined: 4-December 03
From: Northern Virginia, USA


I think I would simplify things and create a Query object dedicated to the Report and call it something like "xsys_sel_rptSomeReportName". Then use code to mod the SQL property of the Query object in order to have the report get the data you want, then call DoCmd.SendObject.

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
ebroomhead
post Mar 15 2019, 10:50 AM
Post#6



Posts: 14
Joined: 19-May 15



Thanks for the code Daniel, however, now I am not getting the salesmen's address on any emails nor am I getting their name as part of the file name.
Go to the top of the page
 
ebroomhead
post Mar 15 2019, 11:50 AM
Post#7



Posts: 14
Joined: 19-May 15



HELP
I reloaded my original vba that printed all names other than the first and that is not showing any of the names now either. Has an add-in been inadvertently turned off???
Go to the top of the page
 
ebroomhead
post Mar 15 2019, 02:54 PM
Post#8



Posts: 14
Joined: 19-May 15



Not sure what you mean. I run a report based on sales reps in a table to get their specific customers, conditionally format it for certain things and then want to email it to each one separately.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th March 2019 - 12:17 AM