Simplifying Code

[Thompyt]

I have this code for an email setup:

Public Sub Send_Open_CCB_Click()
 On Error GoTo ErrorMsgs
 Dim rs As DAO.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg1 As Outlook.MailItem
 Dim objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject, strMsg As String
 Dim CRNUM, NextWed, FINAL As Variant
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg2 = objOutlook.CreateItem(olMailItem)
CRNUM = DLookup("[CR_Numbers]", "[Open_CCB_CRs]", "CR_Numbers")
NextWed = DLookup("[CCB]", "[Settings_Qry]", "CCB")
If IsNull(CRNUM) Then
With objOutlookMsg1
   .Subject = "There are no Open CCB CR's for " & NextWed
   .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
              "There are no CCB Change Request actions for the " & NextWed & " CMB." & vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Persons name" & vbCrLf & vbCrLf & "Brigade Modernization Command (BMC)" & _
            vbCrLf & "Network Integration Division (NID)" & vbCrLf & "BLDG 2, Sheridan Road" & vbCrLf & "Ft Bliss, TX 79916" & vbCrLf & "(555)555-5555" & vbCrLf & "Email address"
    .To = "CCB Results"
   .Display
    DoCmd.Close acReport, "Open_CCB_Changes"
Exit Sub
End With
Else
End If
With objOutlookMsg2
  .Subject = "Current Open CCB CR's - " & NextWed
  .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
          "Dial in - 555-555-55555" & vbCrLf & vbCrLf & _
          "The attached CRs are available for the " & NextWed & " CCB." & vbCrLf & vbCrLf & strMsg & vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Persons Name" & vbCrLf & vbCrLf & _
          "Brigade Modernization Command (BMC)" & vbCrLf & "Network Integration Division (NID)" & vbCrLf & "BLDG 2, Sheridan Road" & vbCrLf & "Ft Bliss, TX 79916" & vbCrLf & _
          "(555)555-55555" & vbCrLf & "Email address"
   DoCmd.OutputTo 3, "Open_CCB_Changes", acFormatPDF, "C:\Temp\CCB Open Changes - " & NextWed & ".pdf", , 0
  .Attachments.Add ("C:\Temp\CCB Open Changes - " & NextWed & ".pdf")
  .To = "CCB Results"
  .Display
  Kill "C:\Temp\CCB Open Changes - " & NextWed & ".pdf"
  DoCmd.Close acReport, "Open_CCB_Changes"
  End With
 Set objOutlookMsg1 = Nothing
 Set objOutlookMsg2 = 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

I would like to take these:

Dim rs As DAO.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg1 As Outlook.MailItem
 Dim objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject, strMsg As String
 Dim CRNUM, NextWed, FINAL As Variant
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg2 = objOutlook.CreateItem(olMailItem)
NextWed = DLookup("[CCB]", "[Settings_Qry]", "CCB")

plus other Variants as needed into one place where the email code can reference it. Call Form_Start.????????? or do I need to have another form/report....



Thanks

[John_G]

If the code is only going to be reference from within that form, but in different procedures, move it to the top of the form's code module, above all the procedures.

This line:
NextWed = DLookup("[CCB]", "[Settings_Qry]", "CCB")

won't work like that, though - you'll have to either leave it in all the procedures or put it in the On Open event of the form.

[Thompyt]

What would I call that section at the top?

Public Sub Form_Load?

[CJ_London]

the where parameters in your dlookups are incomplete so I'm surprised the code runs at all

[Thompyt]

AJAX,
It works fine for me. What am I missing?

[CJ_London]

CRNUM = DLookup("[CR_Numbers]", "[Open_CCB_CRs]", "CR_Numbers")

NextWed = DLookup("[CCB]", "[Settings_Qry]", "CCB")

CR_Numbers and CCB needs to equal something, you might as well not have anything for the where parameter - it will just return the first random value for CR_Numbers/CCB it finds - unless there is only one record in the table/query in which case I guess it doesn't matter

[Thompyt]

CR_Numbers and CCB are variables that are filtered by the query.