Results 1 to 3 of 3
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Downsizing

    I have noticed that the following is repeated in my coding often. Is there a way to make it into 1 instance that when needed the code could reference it and run?

    Such as this with the PDF output:

    Code:
    With objOutlookMsg
        .Subject = NIE & " Closed Changes - " & Tod
        .Body = SigBlock
          DoCmd.OutputTo 3, "rptClosedCRStats", acFormatPDF, "C:\Temp\" & NIE  & " Closed Changes - " & Tod & ".pdf", , 0
        .Attachments.Add ("C:\Temp\" & NIE & " Closed Changes - " & Tod & ".pdf")
        .Display
      Kill "C:\Temp\" & NIE & " Closed Changes - " & Tod & ".pdf"
    Example:
    .Subject = NIE & " Closed Changes - " & Tod

    NIE = Exersize
    TOD - Todays date formatted
    Closed changes references the form

    Could this code be put into 1 "module" for all others to reference? Like Call form_CodeEmail_Click? Then also where "Closed changes" be a Variable/String?

    make a table listing all the report & query names and in the next column put what the variable/string would be named? Then reference Column 2 for the report name and Column 3 for the "Closed change" part of the subject line?

    IE:
    Code:
    With objOutlookMsg
        .Subject = NIE & OutputName & Tod
        .Body = SigBlock
          DoCmd.OutputTo 3, "RPTName", acFormatPDF, "C:\Temp\"  & NIE  & OutputName & Tod & ".pdf", , 0
        .Attachments.Add ("C:\Temp\" & NIE & OutputName & Tod & ".pdf")
        .Display
      Kill "C:\Temp\" & NIE & OutputName & Tod & ".pdf"

    OR

    Code:
    ErrorMsgs:
     If Err.Number = "287" Then
     MsgBox "You clicked No to the Outlook security warning. " & _
     "Rerun the procedure and click Yes to access e-mail " & _
     "addresses to send your message."
     Else
     MsgBox Err.Number & " " & Err.Description
    End If
    Where I can call this Error code as a module like Tod?



    Would you use something like:
    Public Function ErrorSub
    ...........

    Unsure of how this goes


    Probably it would be the same for the DIMs and Sets

    Thanks




    Code:
    Public Sub CMD_EMail_Min_Click()
     
     On Error GoTo ErrorMsgs
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    With objOutlookMsg
        .Subject = NIE & " Closed Changes - " & Tod
        .Body = SigBlock
         DoCmd.OutputTo 3, "rptClosedCRStats", acFormatPDF, "C:\Temp\" & NIE & " Closed Changes - " & Tod & ".pdf", , 0
        .Attachments.Add ("C:\Temp\" & NIE & " Closed Changes - " & Tod & ".pdf")
        .Display
      Kill "C:\Temp\" & NIE & " Closed Changes - " & Tod & ".pdf"
    
      End With
     
      DoCmd.Close acReport, "rptClosedCRStats"
      DoCmd.OpenForm "frmComplete"
     
     Set objOutlookMsg = Nothing
     Set objOutlook = Nothing
     Set objOutlookAttach = Nothing
      Exit Sub
    
    ErrorMsgs:
     If Err.Number = "287" Then
     MsgBox "You clicked No to the Outlook security warning. " & _
     "Rerun the procedure and click Yes to access e-mail " & _
     "addresses to send your message."
     Else
     MsgBox Err.Number & " " & Err.Description
    End If
    End Sub
    Public Sub EMailClosedSoftware_Click()
     On Error GoTo ErrorMsgs
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
      With objOutlookMsg
        .Subject = NIE & " Closed Software - " & Tod
        .Body = SigBlock
         DoCmd.OutputTo 3, "rptClosedSW", acFormatPDF, "C:\Temp\" & NIE & " Closed Software - " & Tod & ".pdf", , 0
        .Attachments.Add ("C:\Temp\" & NIE & " Closed Software - " & Tod & ".pdf")
        .Display
      Kill "C:\Temp\" & NIE & " Closed Software - " & Tod & ".pdf"
    
      End With
      
        DoCmd.Close acReport, "rptClosedSW"
        DoCmd.OpenForm "frmComplete"
     
     Set objOutlookMsg = Nothing
     Set objOutlook = Nothing
     Set objOutlookAttach = Nothing
    
      Exit Sub
    
    ErrorMsgs:
     If Err.Number = "287" Then
     MsgBox "You clicked No to the Outlook security warning. " & _
     "Rerun the procedure and click Yes to access e-mail " & _
     "addresses to send your message."
     Else
     MsgBox Err.Number & " " & Err.Description
    End If
    End Sub

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Try
    Public Sub CMD_Email_Min_Click(byval NIE as string, byval Tod as string)
    with your code in the middle
    End Sub

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Create a record set from the table, (if it has only 2 col's rpt/qry name and string value) loop through it and pass rs.fields(0) and rs.fields(1) to the sub that creates the email message, along with your 3rd parameter (date value?). Not sure how you arrived at a 3 column count when you only mentioned two. I'd let the sub handle the error - no need to create a separate error handling procedure as far as I can see. Maybe something like this:
    Code:
    Dim rs as DAO.Recordset
    Dim db as DAO.Database
    Dim dteDate as Date
    Dim strTitle as String, strRptName as String
    
    dteDate = your formatted date here, unless it changes for each record in recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblStrings")
    rs.MoveFirst 'could check for no recs; unlikely
    Do While Not rs.EOF
      strRptName=rs.Fields(0)
      strTitle=rs.Fields(1)
      If MakeMessage strRptName, strTitle, dteDate = True Then
        rs.MoveNext
      Else
        Exit Sub
      End If
    Loop
    Set db = Nothing
    Set rs = Nothing
    
    End Sub
    Code:
    Function MakeMessage(strRptName, strTitle, dteDate) as Boolean
    MakeMessage = True
      do all email stuff here
      replace your string literals with the variables being passed
      have error routine here also. If error, set MakeMessage = False
    End Function
    Edit: it's air code - untested.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Downsizing
    By Thompyt in forum Programming
    Replies: 6
    Last Post: 07-24-2015, 09:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums